先创建一个测试用户
postgres=# create user abce with login password ‘abce‘; CREATE ROLE postgres=# create schema t; CREATE SCHEMA postgres=# alter default privileges in schema t grant select on tables to abce; ALTER DEFAULT PRIVILEGES postgres=#
目录表pg_user中有个列：useconfig。我们可能会觉得是存在这里：
postgres=# \d pg_user
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | abstime | | |
useconfig | text[] | | |
postgres=# select * from pg_user where usename=‘abce‘;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
abce | 74849 | f | f | f | f | ******** | |
(1 row)
postgres=#
但是，这里并没有存储默认的权限。
再来看看目录表pg_namespace
postgres=# select * from pg_namespace where nspname=‘t‘; nspname | nspowner | nspacl ---------+----------+-------- t | 10 | (1 row) postgres=#
也没有存放在pg_namespace表中。但是，这里却给了我们一个提示：ACL(访问控制列表)。让我们来看看是否有相关的目录表存在：
postgres=# select * from pg_tables where tablename like ‘%acl%‘; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+----------------+------------+------------+------------+----------+-------------+------------- pg_catalog | pg_default_acl | postgres | | t | f | f | f (1 row) postgres=#
可以看到，有个pg_default_acl目录表。
继续往下查看：
postgres=# select * from pg_default_acl where defaclnamespace=‘t‘::regnamespace;
defaclrole | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+-------------------
10 | 74850 | r | {abce=r/postgres}
(1 row)
postgres=#
这里“abce=r”表示用户abce在所有对象上有read的权限。
再次尝试修改abce的默认权限：
postgres=# alter default privileges in schema t grant insert on tables to abce;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl where defaclnamespace=‘t‘::regnamespace;
defaclrole | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+--------------------
10 | 74850 | r | {abce=ar/postgres}
(1 row)
postgres=#
现在abce就被增加a权限，a表示append(insert)。权限的缩写以及含义可以查看文档：https://www.postgresql.org/docs/current/ddl-priv.html
这里的“/postgres”表示schema的属主。
postgres=# alter user abce superuser;
ALTER ROLE
postgres=# \c postgres abce
You are now connected to database "postgres" as user "abce".
postgres=# create schema t2;
CREATE SCHEMA
postgres=# select * from pg_default_acl where defaclnamespace=‘t2‘::regnamespace;
defaclrole | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+-----------
(0 rows)
postgres=# create user abce2;
CREATE ROLE
postgres=# alter default privileges in schema t2 grant select on tables to abce2;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl where defaclnamespace=‘t2‘::regnamespace;
defaclrole | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+----------------
74849 | 74852 | r | {abce2=r/abce}
(1 row)
postgres=#