社区博客 | 消除关于 PostgreSQL 默认权限的神话

demodb=# SELECT current_user;
 current_user
--------------
 postgres
(1 row)
demodb=# CREATE SCHEMA test;
CREATE SCHEMA
demodb=# GRANT USAGE ON SCHEMA test TO obj_user;
GRANT
demodb=# CREATE TABLE test.city(id int, name varchar);
CREATE TABLE
demodb=# INSERT INTO test.city VALUES(1,'Mumbai');
INSERT 0 1

demodb=# SET ROLE obj_user;
SET
demodb=> SELECT * FROM test.city;
ERROR:  permission denied for table city

demodb=> SET ROLE postgres;
SET
demodb=# GRANT SELECT ON TABLE test.city TO obj_user;
GRANT

demodb=# SET ROLE obj_user;
SET
demodb=> SELECT * FROM test.city;
 id |  name
----+--------
  1 | Mumbai
(1 row)

demodb=# SELECT current_user;
current_user
--------------
postgres
(1 row)
demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO obj_user;
ALTER DEFAULT PRIVILEGES
demodb=# CREATE TABLE test.state(id int, name varchar);
CREATE TABLE
demodb=# INSERT INTO test.state VALUES(1,'Maharashtra');
INSERT 0 1

demodb=# SET ROLE obj_user;
SET
demodb=> SELECT * FROM test.state;
id | name
----+-------------
1 | Maharashtra
(1 row)

demodb=> SET ROLE obj_creator;
SET
demodb=> CREATE TABLE test.nationality(id int, name varchar);
CREATE TABLE
demodb=> INSERT INTO test.nationality VALUES(1,'Indian');
INSERT 0 1

demodb=> SET ROLE obj_user;
SET
demodb=> SELECT * FROM test.nationality;
ERROR:  permission denied for table nationality

demodb=# SELECT current_user;
current_user
--------------
postgres
(1 row)
demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON TABLES to obj_user;
ALTER DEFAULT PRIVILEGES

demodb=# set role obj_creator;
SET
demodb=> create table test.citizen(id int, name varchar);
CREATE TABLE
demodb=> insert into test.citizen values(1, 'David');
INSERT 0 1

demodb=> set role obj_user;
SET
demodb=> select * from test.citizen;
id | name
----+-------
1 | David
(1 row)

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;

demodb=# 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)

demodb=# 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)