PostgreSQL權限修改 : ALTER DEFAULT PRIVILEGES

關于權限的問題，曾經有個問題一直困擾着，由于關系不是很大一直沒有深究，今天偶然的一次機會

看手冊時看到 "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.