天天看點

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.

繼續閱讀