前言：
postgresql做為一個比較復(fù)雜的關(guān)系型的重型數(shù)據(jù)庫，不管是安裝部署，還是后期的運(yùn)行維護(hù)，都還是有比較多的細(xì)節(jié)問題需要引起關(guān)注。
例如，用戶權(quán)限的合理分配，那么，什么是權(quán)限的合理分配呢？ 自然是權(quán)限的最小化原則，也就是說每個用戶能夠完成其權(quán)限范圍內(nèi)的工作，而不會由于黑客攻擊，漏洞等原因造成安全方面的危險。
在寫這篇文章之前，僅僅是對于一些普通用戶簡單的隨便賦權(quán)就完事了，而這樣的管理工作顯然是不夠的，因此，本文將對postgresql數(shù)據(jù)庫內(nèi)的用戶賦權(quán)和去權(quán)做一個相對完整的總結(jié)，并通過示例說明 usage權(quán)限和select權(quán)限的不同。
一，
用戶的權(quán)限有哪些？
SELECT：該權(quán)限用來查詢表或是表上的某些列，或是視圖，序列。
INSERT：該權(quán)限允許對表或是視圖進(jìn)行插入數(shù)據(jù)操作，也可以使用COPY FROM進(jìn)行數(shù)據(jù)的插入。
UPDATE：該權(quán)限允許對表或是或是表上特定的列或是視圖進(jìn)行更新操作。
DELETE：該權(quán)限允許對表或是視圖進(jìn)行刪除數(shù)據(jù)的操作。
TRUNCATE：允許對表進(jìn)行清空操作。
REFERENCES：允許給參照列和被參照列上創(chuàng)建外鍵約束。
TRIGGER：允許在表上創(chuàng)建觸發(fā)器。
CREATE：對于數(shù)據(jù)庫，允許在數(shù)據(jù)庫上創(chuàng)建Schema；對于Schema，允許對Schema上創(chuàng)建數(shù)據(jù)庫對象；對于表空間，允許把表或是索引指定到對應(yīng)的表空間上。
CONNECT：允許用戶連接到指定的數(shù)據(jù)庫上。
TEMPORARY或是TEMP：允許在指定數(shù)據(jù)庫的時候創(chuàng)建臨時表。
EXECUTE：允許執(zhí)行某個函數(shù)。
USAGE：對于程序語言來說，允許使用指定的程序語言創(chuàng)建函數(shù)；對于Schema來說，允許查找該Schema下的對象（不包括授權(quán)后的新建對象）；對于序列來說，允許使用currval和nextval函數(shù)；對于外部封裝器來說，允許使用外部封裝器來創(chuàng)建外部服務(wù)器；對于外部服務(wù)器來說，允許創(chuàng)建外部表。
ALL PRIVILEGES：表示一次性給予可以授予的權(quán)限。
OK，增刪改查也就是select ，update，insert，delete? 和usage應(yīng)該是可以歸于一類的，而select和usage是十分相似的，至少在schema下，兩者是基本雷同的，但需要注意的是，授權(quán)后的新建對象，比如新建表，usage是無權(quán)查詢的，而select顯然是不存在此類問題的。
二，
正確的只讀用戶賦權(quán)
1，
第一種賦權(quán)
usage---使用權(quán)+select查詢權(quán)
先創(chuàng)建相關(guān)schema，名為mytest，相關(guān)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
賦權(quán)：
test=# grant USAGE on SCHEMA mytest to test;
GRANT
test1=> grant SELECT on ALL tables in schema mytest to test;
測試就不演示了，只是需要注意一點(diǎn)，要賦權(quán)兩個，usage和select，兩者缺一不可，也就是說必須是兩個命令！?。。?！
OK，以上是用戶test賦權(quán)select到test數(shù)據(jù)庫下的mytest這個schema，下面為了繼續(xù)測試，刪除test這個用戶。
2，
強(qiáng)制刪除已賦權(quán)過的用戶
OK，刪除的時候報錯了，這就讓人比較無語了，報錯說的是名為test的數(shù)據(jù)庫有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
強(qiáng)制刪除：
需要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這個用戶是否刪除：
可以看到，確實(shí)沒有了，有強(qiáng)迫癥的人士就非常舒服了。
但特別需要注意，該強(qiáng)制刪除用戶因為是級聯(lián)刪除，因此，很大概率會把依賴的schema和table都刪除，所以此方式強(qiáng)制刪除用戶需要提前備份，防止發(fā)生不測。
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，
第二種賦權(quán)
grant select+ owner文章來源：http://www.zghlxwxcb.cn/news/detail-636798.html
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
測試：文章來源地址http://www.zghlxwxcb.cn/news/detail-636798.html
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)
到了這里，關(guān)于postgresql|數(shù)據(jù)庫|角色（用戶）管理工作---授權(quán)和去權(quán)以及usage和select兩種權(quán)限的區(qū)別的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
