PostgreSQL permission grant/revoke in schema clause emulate

5 minute read

背景

PostgreSQL 9.0 版本增加了”GRANT”/”REVOKE IN SCHEMA”的权限管理子句.

方便了大批量对象的赋权和回收. 如下 :

Release 9.0  
  
Object Permissions  
     * Add the ability to make mass permission changes across a whole  
       schema using the new "GRANT"/"REVOKE IN SCHEMA" clause (Petr  
       Jelinek)  
       This simplifies management of object permissions and makes it  
       easier to utilize database roles for application data security.  

但是低版本的PostgreSQL 并不支持, 今天一位兄弟问到这个, 刚好他们用的是8.3的版本.

要对大量函数进行赋权.

还好plpgsql语言, 可以通过动态SQL来对大批量的对象进行赋权.

下面来看看怎么使用 :

首先创建几个测试函数 :

postgres=# create or replace function f1() returns void as $$  
postgres$# declare  
postgres$# begin  
postgres$# end; $$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# create or replace function f2() returns void as $$  
declare  
begin  
end; $$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# create or replace function f3() returns void as $$  
declare  
begin  
end; $$ language plpgsql;  
CREATE FUNCTION  

这三个函数都在public 这个schema下面 :

postgres=# \df f1  
                        List of functions  
 Schema | Name | Result data type | Argument data types |  Type    
--------+------+------------------+---------------------+--------  
 public | f1   | void             |                     | normal  
(1 row)  
  
postgres=# \df f2  
                        List of functions  
 Schema | Name | Result data type | Argument data types |  Type    
--------+------+------------------+---------------------+--------  
 public | f2   | void             |                     | normal  
(1 row)  
  
postgres=# \df f3  
                        List of functions  
 Schema | Name | Result data type | Argument data types |  Type    
--------+------+------------------+---------------------+--------  
 public | f3   | void             |                     | normal  
(1 row)  

查看当前的权限 :

postgres=# select proname, (aclexplode(proacl)).* from pg_proc where proname in ('f1', 'f2', 'f3');  
 proname | grantor | grantee | privilege_type | is_grantable   
---------+---------+---------+----------------+--------------  
(0 rows)  

通过以下SQL得到public schema的oid.

select oid into soid from pg_namespace where nspname='public';  

通过以下SQL来拼装赋权的SQL :

select 'grant execute on function "'||proname||'"('||pg_catalog.pg_get_function_arguments(oid)||') to digoal;' from pg_proc where pronamespace=soid;  

通过这个DO来完成整个public schema下的函数的权限赋予给digoal用户.

postgres=# do                                                                             
language plpgsql  
$$  
declare  
  soid oid;  
  sql text;  
begin  
  select oid into soid from pg_namespace where nspname='public';  
  for sql in select 'grant execute on function "'||proname||'"('||pg_catalog.pg_get_function_arguments(oid)||') to digoal;' from pg_proc where pronamespace=soid  
  loop  
    execute sql;  
  end loop;  
end;  
$$;  
DO  

执行完后, 查看这三个函数是否已经赋权?

postgres=# select proname, (aclexplode(proacl)).* from pg_proc where proname in ('f1', 'f2', 'f3');  
 proname | grantor | grantee | privilege_type | is_grantable   
---------+---------+---------+----------------+--------------  
 f1      |      10 |       0 | EXECUTE        | f  
 f1      |      10 |      10 | EXECUTE        | f  
 f1      |      10 |   16384 | EXECUTE        | f  
 f2      |      10 |       0 | EXECUTE        | f  
 f2      |      10 |      10 | EXECUTE        | f  
 f2      |      10 |   16384 | EXECUTE        | f  
 f3      |      10 |       0 | EXECUTE        | f  
 f3      |      10 |      10 | EXECUTE        | f  
 f3      |      10 |   16384 | EXECUTE        | f  
(9 rows)  

这里的grantor是赋权者, grantee是被赋权者. 如下 :

postgres=# select oid,rolname from pg_roles;  
  oid  | rolname    
-------+----------  
    10 | postgres  
 16384 | digoal  
 33481 | skycac  
(3 rows)  

回收类似, 如下 :

postgres=# do                                                                                        
language plpgsql  
$$  
declare  
soid oid;  
sql text;  
begin  
select oid into soid from pg_namespace where nspname='public';  
for sql in select 'revoke execute on function "'||proname||'"('||pg_catalog.pg_get_function_arguments(oid)||') from digoal;' from pg_proc where pronamespace=soid  
loop  
execute sql;  
end loop;  
end;  
$$;  
DO  

执行完后, digoal角色的权限就被回收了, 如下 :

postgres=# select proname, (aclexplode(proacl)).* from pg_proc where proname in ('f1', 'f2', 'f3');  
 proname | grantor | grantee | privilege_type | is_grantable   
---------+---------+---------+----------------+--------------  
 f1      |      10 |       0 | EXECUTE        | f  
 f1      |      10 |      10 | EXECUTE        | f  
 f2      |      10 |       0 | EXECUTE        | f  
 f2      |      10 |      10 | EXECUTE        | f  
 f3      |      10 |       0 | EXECUTE        | f  
 f3      |      10 |      10 | EXECUTE        | f  
(6 rows)  

所有支持IN SCHEMA子句的权限管理如下 :

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { SEQUENCE sequence_name [, ...]  
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]  
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

注意

如果要修改以后在schema中创建的对象的默认权限, 可以使用ALTER DEFAULT PRIVILEGES语法, 如下 :

即默认就赋予给某些角色什么权限.

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 ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    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 ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  

参考

1. http://www.postgresql.org/docs/9.2/static/sql-grant.html

2. http://www.postgresql.org/docs/9.2/static/sql-revoke.html

3. http://www.postgresql.org/docs/9.2/static/sql-alterdefaultprivileges.html

Flag Counter

digoal’s 大量PostgreSQL文章入口