揭开 PostgreSQL 默认权限的神秘面纱

alter default privileges 命令允许我们设置将应用于将来创建的对象的权限。需要注意的是，这不会影响分配给现有对象的权限；默认权限可以全局设置在当前数据库上，或者指定的 schema 上。

 

数据库使用者对默认权限的行为有很多误解，我经常听到一些抱怨，说即使为特定 schema 分配了默认权限，用户也没有访问权限。

 

了解默认权限

创建对象时，会给它分配一个所有者。最初，所有者是执行创建对象语句的角色(后期可以更改)。对于大多数类型的对象，只有所有者(或超级用户)拥有对象的所有权。不过，可以通过 alter default privileges 语句更改默认权限来改变这种行为。这样，我们就可以在任何类型的对象上为任何预期用户分配任何默认权限。目前，只有 schema 、表(包括视图和 foreign 表)、序列、函数和类型(包括域)的权限可以使用 alter default privileges 进行更改。

 

示例：

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)
postgres=# create schema test;
CREATE SCHEMA
postgres=# grant usage on schema test to obj_user;
GRANT
postgres=# create table test.city(id int, name varchar);
CREATE TABLE
postgres=# insert into test.city values(1, 'didu');
INSERT 0 1
postgres=# 

现在测试角色 obj_user 的权限：

postgres=# set role obj_user;
SET
postgres=> select * from test.city;
ERROR:  permission denied for table city
postgres=> 

需要显式授权：

postgres=> set role postgres;
SET
postgres=# grant select on table test.city to obj_user;
GRANT
postgres=# 

postgres=# set role obj_user;
SET
postgres=> select * from test.city;
 id | name 
----+------
  1 | didu
(1 row)

postgres=> 

这里，用户 obj_user 缺省时没有 select 权限，必须显式授予。如果总是碰到这种场景，就该考虑自动授予 select 权限了。这可以通过 alter default privileges 实现。

 

设置默认权限

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# alter default privileges in schema test grant select on tables to obj_user;
ALTER DEFAULT PRIVILEGES
postgres=# create table test.state(id int, name varchar);
CREATE TABLE
postgres=# insert into test.state values(1, 'myabc');
INSERT 0 1
postgres=# 
postgres=# set role obj_user;
SET
postgres=> select * from test.state;
 id | name  
----+-------
  1 | myabc
(1 row)

postgres=> 

可以查询了。

 

现在，考虑不用 postgres 用户创建一个对象。用另一个用户 obj_creator 创建一个表，并测试 obj_user 的权限：

postgres=> set role obj_creator;
set
postgres=> create table test.nationality(id int, name varchar);
create table
postgres=> insert into test.nationality values(1,'USA');
insert 0 1

postgres=> set role obj_user;
set
postgres=> select * from test.nationality;
error:  permission denied for table nationality

这里就执行失败了。但这是 PostgreSQL 默认权限的实际行为。许多用户认为，既然我们授予了默认权限，那么无论对象的创建者是谁，它都会正常工作。

 

 

不生效的原因

用户 obj_user 并没有自动获得新建的表的权限，因为默认权限只有在对象创建者与 alter default privileges 语句的执行者（默认情况下为当前用户，在本例中为 postgres）相同时才会起作用。

 

破解迷思

为了解决这个问题，执行下面的语句，假设 obj_creator 将是test模式中对象的创建者：

postgres=# SELECT current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON TABLES to obj_user;
ALTER DEFAULT PRIVILEGES
postgres=# set role obj_creator;
SET
postgres=# create table test.citizen(id int, name varchar);
CREATE TABLE
postgres=# insert into test.citizen values(1, 'abc');
INSERT 0 1
postgres=# set role obj_user;
SET
postgres=> select * from test.citizen;
 id | name  
----+-------
  1 | David
(1 row)

postgres=> 

生效了。通过在 alter default privieges 语句中指定了对象的创建者，我们可以保证obj_user自动获取到obj_creator用户在test schema中创建的对象上的 select 权限。

 

更多示例语句

授权：

alter default privileges in schema test for role obj_creator grant select, insert, update, delete on tables to obj_user;
alter default privileges in schema test for role obj_creator grant usage on types to obj_user;
alter default privileges in schema test for role obj_creator grant execute on functions to obj_user;
alter default privileges in schema test for role obj_creator grant select on sequences to obj_user;
alter default privileges for role obj_creator grant usage on schemas to obj_user;

取消授权：

alter default privileges in schema test for role obj_creator revoke insert,update,delete on tables from obj_user;
alter default privileges in schema test for role obj_creator revoke usage on types from obj_user;
alter default privileges in schema test for role obj_creator revoke execute on functions from obj_user;
alter default privileges in schema test for role obj_creator revoke select on sequences from obj_user;
alter default privileges for role obj_creator revoke usage on schemas from obj_user;

列出默认权限

可以使用\ddp密令查看列出默认权限

postgres-> \ddp
               Default access privileges
    Owner    | Schema | Type  |   Access privileges    
-------------+--------+-------+------------------------
 obj_creator | test   | table | obj_user=r/obj_creator
 postgres    | test   | table | obj_user=r/postgres
(2 rows)

postgres-> 

也可以使用以下查询：

postgres=> SELECT
defaclnamespace::regnamespace AS schema,
CASE defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS obj_type,
(aclexplode(defaclacl)).privilege_type AS privilege_type,
(aclexplode(defaclacl)).grantor::regrole AS for_role,
(aclexplode(defaclacl)).grantee::regrole AS to_user
FROM pg_default_acl;
 schema | obj_type | privilege_type |  for_role   | to_user  
--------+----------+----------------+-------------+----------
 test   | table    | SELECT         | postgres    | obj_user
 test   | table    | SELECT         | obj_creator | obj_user
(2 rows)

postgres=> 

使用 alter default privileges，我们可以自动简化权限管理，确保正确的用户可以访问新对象，而无需人工干预。