PostgreSQL permission grant/revoke in schema clause emulate
背景
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