前言：
postgresql做为一个比较复杂的关系型的重型数据库，不管是安装部署，还是后期的运行维护，都还是有比较多的细节问题需要引起关注。
例如，用户权限的合理分配，那么，什么是权限的合理分配呢？ 自然是权限的最小化原则，也就是说每个用户能够完成其权限范围内的工作，而不会由于黑客攻击，漏洞等原因造成安全方面的危险。
在写这篇文章之前，仅仅是对于一些普通用户简单的随便赋权就完事了，而这样的管理工作显然是不够的，因此，本文将对postgresql数据库内的用户赋权和去权做一个相对完整的总结，并通过示例说明 usage权限和select权限的不同。
一，
用户的权限有哪些？
SELECT：该权限用来查询表或是表上的某些列，或是视图，序列。
INSERT：该权限允许对表或是视图进行插入数据操作，也可以使用COPY FROM进行数据的插入。
UPDATE：该权限允许对表或是或是表上特定的列或是视图进行更新操作。
DELETE：该权限允许对表或是视图进行删除数据的操作。
TRUNCATE：允许对表进行清空操作。
REFERENCES：允许给参照列和被参照列上创建外键约束。
TRIGGER：允许在表上创建触发器。
CREATE：对于数据库，允许在数据库上创建Schema；对于Schema，允许对Schema上创建数据库对象；对于表空间，允许把表或是索引指定到对应的表空间上。
CONNECT：允许用户连接到指定的数据库上。
TEMPORARY或是TEMP：允许在指定数据库的时候创建临时表。
EXECUTE：允许执行某个函数。
USAGE：对于程序语言来说，允许使用指定的程序语言创建函数；对于Schema来说，允许查找该Schema下的对象（不包括授权后的新建对象）；对于序列来说，允许使用currval和nextval函数；对于外部封装器来说，允许使用外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。
ALL PRIVILEGES：表示一次性给予可以授予的权限。
OK，增删改查也就是select ，update，insert，delete 和usage应该是可以归于一类的，而select和usage是十分相似的，至少在schema下，两者是基本雷同的，但需要注意的是，授权后的新建对象，比如新建表，usage是无权查询的，而select显然是不存在此类问题的。
二，
正确的只读用户赋权
1，
第一种赋权
usage---使用权+select查询权
先创建相关schema，名为mytest，相关role，名为test
test=# \c test You are now connected to database "test" as user "postgres". test=# create schema mytest; CREATE SCHEMA test=# \du List of roles Role name | Attributes | Member of --------------------+------------------------------------------------------------+----------- drmc | | {} pg1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} pms30 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser | {} postgres_exporter | | {} postgres_exporter1 | | {} power_common | | {} power_tf | | {} zsk | | {} test=# create user test with password '123456'; CREATE ROLE
赋权：
test=# grant USAGE on SCHEMA mytest to test; GRANT test1=> grant SELECT on ALL tables in schema mytest to test;
测试就不演示了，只是需要注意一点，要赋权两个，usage和select，两者缺一不可，也就是说必须是两个命令！！！！！
OK，以上是用户test赋权select到test数据库下的mytest这个schema，下面为了继续测试，删除test这个用户。
2，
强制删除已赋权过的用户
OK，删除的时候报错了，这就让人比较无语了，报错说的是名为test的数据库有5个对象依赖于用户test，不过还是有解决办法的
postgres=# drop user test; 2023-08-09 01:15:34.031 CST [14975] ERROR: role "test" cannot be dropped because some objects depend on it 2023-08-09 01:15:34.031 CST [14975] DETAIL: 5 objects in database test 2023-08-09 01:15:34.031 CST [14975] STATEMENT: drop user test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 5 objects in database test
强制删除：
需要reassign和drop owner by以及drop user 三条命令，缺一不可。
postgres=# \c test You are now connected to database "test" as user "postgres". test=# \dn List of schemas Name | Owner --------+------- mytest | test public | pg1 (2 rows) test=# REASSIGN OWNED BY test TO postgres; REASSIGN OWNED test=# \dn List of schemas Name | Owner --------+---------- mytest | postgres public | pg1 (2 rows) test=# drop owned BY test cascade; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table mytest.dept drop cascades to table mytest.emp drop cascades to table mytest.bonus drop cascades to table mytest.salgrade DROP OWNED
OK，查询test这个用户是否删除：
可以看到，确实没有了，有强迫症的人士就非常舒服了。
但特别需要注意，该强制删除用户因为是级联删除，因此，很大概率会把依赖的schema和table都删除，所以此方式强制删除用户需要提前备份，防止发生不测。
test1=# \du+ List of roles Role name | Attributes | Member of | Description --------------------+------------------------------------------------------------+-----------+------------- drmc | | {} | pg1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | pms30 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres | Superuser | {} | postgres_exporter | | {} | postgres_exporter1 | | {} | power_common | | {} | power_tf | | {} | zsk | | {} |
3，
第二种赋权
grant select+ owner
test=# create user test with password '123456'; CREATE ROLE test=# \c You are now connected to database "test" as user "postgres". test=# grant SELECT on ALL tables in schema mytest to test; GRANT test=# set search_path to mytest ; SET test=# alter schema mytest owner to test; ALTER SCHEMA
测试：
test=> \c You are now connected to database "test" as user "test". test=> set search_path to mytest ; SET test=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+----------+-------+---------------------------+-------------------+---------- mytest | bonus | table | postgres=arwdDxt/postgres+| | | | | test=r/postgres | | mytest | dept | table | postgres=arwdDxt/postgres+| | | | | test=r/postgres | | mytest | emp | table | postgres=arwdDxt/postgres+| | | | | test=r/postgres | | mytest | salgrade | table | postgres=arwdDxt/postgres+| | | | | test=r/postgres | | (4 rows) test=> \dn List of schemas Name | Owner --------+------- mytest | test public | pg1 (2 rows) test=> set search_path to mytest ; SET test=> select * from emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7788 | SCOTT | ANALYST | 7566 | 0087-04-19 | 3000.00 | | 20 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7876 | ADAMS | CLERK | 7788 | 0087-05-23 | 1100.00 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 (14 rows)
