關于權限的問題,曾經有個問題一直困擾着,由于關系不是很大一直沒有深究,今天偶然的一次機會
看手冊時看到 "ALTER DEFAULT PRIVILEGES" 指令,立即解決了困擾已久的問題。
--1 問題描述
在生産資料庫中通常不允許開發人員使用生産帳号連接配接資料庫,那麼有時候開發人員又需要查詢資料庫
權限,用于日常問題排查,通常的做法是建立一個查詢帳号,并把資料庫中指定表的查詢權限開放給給查詢
帳号,這也是目前我們生産系統維護過程中使用較多的方法,但是如果開發人員希望對整庫所有表的查詢權
限均開放,而且以後生産庫中建立的表也需要預設的開放此帳号的查詢權限,這時如何處理?這時,今天學
習到的指令即可解決這個問題,指令為 "ALTER DEFAULT PRIVILEGES",下面簡單測試下。
--2 環境資訊
PostgreSQL 版本: 9.1.0
資料庫名: mydb
資料庫屬主: mydb
查詢帳号 mydb_select
備注:假設資料庫 mydb 庫中使用者僅建立 mydb schema。
--3 建立隻讀帳号
[[email protected] ~]$ psql psql (9.1.0) Type "help" for help. postgres=# create role mydb_select LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE encrypted password 'mydb_select'; CREATE ROLE |
備注:上面建立帳号 mydb_select。
--4 給帳号 mydb_select 賦權
postgres=# c mydb mydb You are now connected to database "mydb" as user "mydb". mydb=> grant connect on database mydb to mydb_select; GRANT mydb=> grant usage on schema mydb to mydb_select; GRANT mydb=> grant select on all tables in schema mydb to mydb_select; GRANT |
備注:上面指令給帳号 mydb_select 開通了資料庫 mydb 的隻讀權限,可以通路mydb 庫中 mydb schema 下的所有表。
--5 權限測試
mydb=> c mydb mydb_select; You are now connected to database "mydb" as user "mydb_select". mydb=> select * from mydb.test limit 1; id | name --------+------ 831681 | AAA (1 row) |
備注: mydb_select 使用者查詢 mydb.test 表成功,沒有問題。
--6 在 mydb 庫中建立一張新表
mydb=> create table test_33 as select * From test limit 10 ; SELECT 10 mydb=> dp test_33 Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------+-------+-------------------+-------------------------- mydb | test_33 | table | mydb=arwdDxt/mydb+| (1 row) mydb=> c mydb mydb_select; You are now connected to database "mydb" as user "mydb_select". mydb=> select * from mydb.test_33; ERROR: permission denied for relation test_33 |
備注:此時使用者 mydb_select 沒有權限查詢,因為 mydb.test_33 是賦權後建立的表。
--7 使用 "ALTER DEFAULT PRIVILEGES" 賦權
mydb=> ALTER DEFAULT PRIVILEGES IN SCHEMA mydb grant select on tables to mydb_select; ALTER DEFAULT PRIVILEGES |
備注:我們更改使用者 mydb_select 的預設權限,使得在資料庫 mydb中 mydb 模式下建立的新表,
預設給使用者 mydb_select 開通查詢權限,這個指令僅對未來建立的資料庫對像生效,對
目前存在的資料庫對像不起作用, 這個指令的詳細資訊,參考本文末尾的附一。
--8 權限測試
mydb=> create table test_34 as select * From test limit 10 ; SELECT 10 mydb=> dp test_34 Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------+-------+--------------------+-------------------------- mydb | test_34 | table | mydb=arwdDxt/mydb +| | | | mydb_select=r/mydb | (1 row) mydb=> c mydb mydb_select You are now connected to database "mydb" as user "mydb_select". mydb=> select * From mydb.test_34 limit 1; id | name --------+------ 831681 | AAA (1 row) mydb=> select * From mydb.test_33 limit 1; ERROR: permission denied for relation test_33 |
備注:這時使用者 mydb_select 果然擁有之後建立表(mydb.test_34) 的查詢權限了,而對之前的表
mydb.test_33 依然沒有查詢權限,這也正好驗證子這前關于這個指令的說明,即僅對未來
建立的資料庫對像生效。
附一 ALTER DEFAULT PRIVILEGE 指令
Name
ALTER DEFAULT PRIVILEGES -- define default access privileges
Synopsis
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTIONS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTIONS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
Description
ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in
the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the
privileges for tables (including views), sequences, and functions can be altered.