本文主要分层介绍讨论PG的权限体系，从数据库，用户，模式，对象等全方位进行说明和测试。一定让你能清楚PG的复杂权限体系。

Table of Contents

• 1 Postgresql 权限体系
• 权限总结：
• 2 实例权限
• 3 数据库权限
• 4 用户权限
• 5.1 pg_user、pg_authid、pg_roles、pg_auth_members区别
• 5 schema权限
• 5.1 **查看schema**
• 5.2 **如何创建schema和在此schema上创建对象**
• 5.3 创建一个他人拥有的模式
• 5.4 搜索路径
• 5.4.1 如何让search_path永久生效呢
• 5.5 public模式
• 5.5.1 如何撤销public相关权限
• 5.5.2 只读账号的使用方式
• 5.5.3 用户针对某一个database和schema方式只读（另一种方式）
• 5.5.4 其他测试
• db owner不一定能操作其下面的某个schema，schema owner不一定能操作其下面的某张表
• 光授权select on all tables in schema，而没有授权usage on schema，用户无法查询schema下的表
• 没有createdb权限，则无法创建database，有了createdb权限还可以在自己创建的db下创建schema
• 在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表
• 在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表
• 5.6 系统表模式-pg_catalog
• 5.7 移植性
• 6 对象权限和系统权限
• 6.1 grant的语法参考
• 6.2 授权测试
• 6.2.1 单表/所有表授权
• 6.2.2 列授权
• 6.2.3 序列授权
• 6.2.4 database创建schema，连接，使用temp
• 6.2.5 schema上的授权
• 6.2.6 授权用户
• 6.2.7 WITH ADMIN OPTION
• 6.3 如何查看和解读一个对象的当前权限状态
• 6.4 some_privileges说明
• 6.5 ALL PRIVILEES说明
• 7 表空间权限使用
• 8 用户和权限常用语句
• 9 权限模型最佳实践（转载）
• Hologres专家权限模型最佳实践一
• Hologres专家权限模型最佳实践二
• 项目场景案例

1 Postgresql 权限体系

权限总结：

1 首先在创建用户方面的权限，比如XXX

2 在数据库创建模式的权限

3 在模式中创建各种对象的权限，创建表，索引等

4 之后是查询表，在表插入，删除，更新，

5 最后是针对表的字段进行操作的权限

2 实例权限

关于实例连接的权限，我之前有一个单独的分享https://www.modb.pro/db/102674 文章，这么就不再赘述了。

3 数据库权限

数据库的权限主要包括：

• 允许连接数据库

• 允许创建数据库和调整数据库

• 允许数据中创建schema

允许用户连接数据库

默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接

1 首先创建要给testuser
postgres@[local]:5432=#create user testuser password 'test';
CREATE ROLE
postgres@[local]:5432=#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}
 yanwei    | Superuser, Create role, Create DB                          | {}


2 用testuser连接数据库，说明任何用户都可以连接到任何数据库上
postgres@[local]:5432=#\l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | pguser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | pguser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 yanwei    | yanwei   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 yanwei1   | yanwei   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

postgres@[local]:5432=#\c yanwei testuser
You are now connected to database "yanwei" as user "testuser".

3 可以随意创建对象，但是都是创建到public schema上
yanwei@[local]:5432=>create table test1 (name text);
CREATE TABLE
yanwei@[local]:5432=>\d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | test  | table | yanwei
 public | test1 | table | testuser
(2 rows)

yanwei@[local]:5432=>

yanwei@[local]:5432=>\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

允许创建数据库和调整数据库

1 看看默认创建的用户权限
yanwei@[local]:5432=>select * from information_schema.table_privileges where grantee='testuser';
 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
 testuser | testuser | yanwei        | public       | test1      | INSERT         | YES          | NO
 testuser | testuser | yanwei        | public       | test1      | SELECT         | YES          | YES
 testuser | testuser | yanwei        | public       | test1      | UPDATE         | YES          | NO
 testuser | testuser | yanwei        | public       | test1      | DELETE         | YES          | NO
 testuser | testuser | yanwei        | public       | test1      | TRUNCATE       | YES          | NO
 testuser | testuser | yanwei        | public       | test1      | REFERENCES     | YES          | NO
 testuser | testuser | yanwei        | public       | test1      | TRIGGER        | YES          | NO

yanwei@[local]:5432=>\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}
 yanwei    | Superuser, Create role, Create DB                          | {}

这里testuser 权限中是没有create DB的
2 尝试创建数据库
postgres@[local]:5432=>\h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html

postgres@[local]:5432=>create database testa owner=testuser;
ERROR:  permission denied to create database
postgres@[local]:5432=>

3 超级用户进行此用户的授权和测试后是可以创建的
postgres@[local]:5432=#alter user testuser createdb;
ALTER ROLE
postgres@[local]:5432=#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create DB                                                  | {}
 yanwei    | Superuser, Create role, Create DB                          | {}

postgres@[local]:5432=#\h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html

postgres@[local]:5432=#\c postgres testuser
You are now connected to database "postgres" as user "testuser".
postgres@[local]:5432=>create database testa owner=testuser;
CREATE DATABASE
postgres@[local]:5432=>\l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | pguser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | pguser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 testa     | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 yanwei    | yanwei   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 yanwei1   | yanwei   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)

postgres@[local]:5432=>

允许数据中创建schema

* 默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。
* 默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

1 测试非owner和超级用户连接到数据库中去创建schema

postgres@[local]:5432=>\c yanwei testuser
You are now connected to database "yanwei" as user "testuser".
yanwei@[local]:5432=>\h create schema
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-createschema.html

yanwei@[local]:5432=>create schema test;
ERROR:  permission denied for database yanwei
yanwei@[local]:5432=>

2 默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象
这个测试上面已经测试过了

4 用户权限

这里只是简单总结一下：同时单独写了一个文档https://www.modb.pro/db/103876 Postgresql 用户和角色综合测试

角色/用户在CREATE USER创建时赋予权限，ALTER USER方式变更权限	说明
SUPERUSER	数据库的超级用户拥有该数据库的所有权限
CREATEDB	角色要想创建数据库，必须明确赋予创建数据库的属性(包含变更与删除权限)
CREATEROLE	一个角色要想创建更多角色，必须明确给予创建角色的属性
LOGIN	登录的权限
REPLICATION	专门用于执行复制动作的权限

角色/用户在GRANT方式赋予权限，REVOKE方式撤销权限	说明
SELECT	允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。
INSERT	允许将新行INSERT到指定的表中。如果指定特定列，那么其它列将以默认值写入。
UPDATE	允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE	允许删除指定表中的行，需要SELECT权限。
TRUNCATE	允许在指定的表上创建触发器。
REFERENCES	允许创建引用指定表或表的指定列的外键约束。
TRIGGER	允许在指定的表上创建触发器。
CREATE	对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT	允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY	允许在使用指定数据库时创建临时表。
EXECUTE	允许使用指定的函数或过程以及在函数。
USAGE	对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES	一次授予所有可用权限。
SAME PRIVILEGES

详细的测试在object 权限中一一测试

5.1 pg_user、pg_authid、pg_roles、pg_auth_members区别

pg_user:

postgres@[local]:5432=#\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     | timestamp with time zone |           |          |
 useconfig    | text[]                   | C         |          |

名字	类型	描述
usename	name	用户名
usesysid	oid	用户的ID
usecreatedb	bool	用户是否能创建数据库
usesuper	bool	用户是否为超级用户
userepl	bool	用户能否开启流复制以及将系统转入/转出备份模式。
usebypassrls	bool	用户能否绕过所有的行级安全性策略，详见 第 5.8 节。
passwd	text	不是口令（总是显示为********）
valuntil	timestamptz	口令过期时间（只用于口令认证）
useconfig	text[]	运行时配置变量的会话默认值

postgres@[local]:5432=#select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
 pguser   |    16387 | t           | t        | t       | f            | ******** |          |
 yanwei   |    16432 | t           | t        | f       | f            | ******** |          |
 testuser |    16488 | t           | f        | f       | f            | ******** |          |
(4 rows)

口令（总是显示为`********`）

pg_authid

目录pg_authid包含关于数据库授权标识符（角色）的信息。角色把“用户”和“组”的概念包含在内。一个用户实际上就是一个rolcanlogin标志被设置的角色。任何角色（不管rolcanlogin设置与否）都能够把其他角色作为成员，参见pg_auth_members。

由于这个目录包含口令，它不能是公共可读的。pg_roles是在pg_authid上的一个公共可读视图，它隐去了口令域。

postgres@[local]:5432=#\d pg_authid
                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 oid            | oid                      |           | not null |
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     | C         |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"

postgres@[local]:5432=#

名字	类型	描述
oid	oid	行标识符
rolname	name	角色名
rolsuper	bool	角色是否拥有超级用户权限
rolinherit	bool	如果本角色是另一个角色的成员，本角色是否自动另一个角色的权限
rolcreaterole	bool	角色是否能创建更多角色
rolcreatedb	bool	角色是否能创建数据库
rolcanlogin	bool	角色是否能登录。即该角色是否能够作为初始会话授权标识符
rolreplication	bool	角色是一个复制角色。复制角色可以启动复制连接并且创建和删除复制槽。
rolbypassrls	bool	角色是否可以绕过所有的行级安全性策略，详见 第 5.8 节。
rolconnlimit	int4	对于可以登录的角色，本列设置该角色可以同时发起最大连接数。-1表示无限制。
rolpassword	text	口令（可能被加密过），如果没有口令则为空。格式取决于使用的加密方法的形式。
rolvaliduntil	timestamptz	口令过期时间（只用于口令鉴定），如果永不过期则为空

postgres@[local]:5432=#select * from pg_authid ;
  oid  |          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnl
imit |                                                              rolpassword                                                              | ro
lvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+---------
-----+---------------------------------------------------------------------------------------------------------------------------------------+---
------------
  3373 | pg_monitor                | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  3374 | pg_read_all_settings      | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  3375 | pg_read_all_stats         | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  3377 | pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  4569 | pg_read_server_files      | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  4570 | pg_write_server_files     | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  4571 | pg_execute_server_program | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
  4200 | pg_signal_backend         | f        | t          | f             | f           | f           | f              | f            |
  -1 |                                                                                                                                       |
    10 | postgres                  | t        | t          | t             | t           | t           | t              | t            |
  -1 | md53175bce1d3201d16594cebf9d7eb3f9d                                                                                                   |
 16387 | pguser                    | t        | t          | t             | t           | t           | t              | f            |
  -1 | md5e37525e5632540767d3611d0f153913b                                                                                                   |
 16432 | yanwei                    | t        | t          | t             | t           | t           | f              | f            |
  -1 | SCRAM-SHA-256$4096:sAS+aWEQAEOfVvZHCTIutw==$pY4vHMhb1V1/0dYIEWC6dHWBLW4ILL4xZs7ISShh6qU=:0RGke73vM39fz5xAg/Bj7S9Ha53SNFmBEu2RWgYg9tE= |
 16488 | testuser                  | f        | t          | f             | t           | t           | f              | f            |
  -1 | SCRAM-SHA-256$4096:Um3yOFmtieLKFURAZ6y/BQ==$p4MMIOoNLCK1/C6PhUhSDhG/7f8fqDhnqBbYo8Hzl7E=:A/JO8W33iJHF4jY1DEfFGMbH9KRDLzSxt11+aMCAaDI= |
(12 rows)

pg_roles

pg_roles是在pg_authid上的一个公共可读视图，它隐去了口令域

备注：创建角色，赋予了login权限，则相当于创建了用户，如果没有赋予login权限，则这个角色只能在pg_roles里面看到，而在pg_user里面看不到

postgres@[local]:5432=#\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     | timestamp with time zone |           |          |
 useconfig    | text[]                   | C         |          |

postgres@[local]:5432=#\d pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          |
 rolsuper       | boolean                  |           |          |
 rolinherit     | boolean                  |           |          |
 rolcreaterole  | boolean                  |           |          |
 rolcreatedb    | boolean                  |           |          |
 rolcanlogin    | boolean                  |           |          |
 rolreplication | boolean                  |           |          |
 rolconnlimit   | integer                  |           |          |
 rolpassword    | text                     |           |          |
 rolvaliduntil  | timestamp with time zone |           |          |
 rolbypassrls   | boolean                  |           |          |
 rolconfig      | text[]                   | C         |          |
 oid            | oid                      |           |          |

备注：创建角色，赋予了login权限，则相当于创建了用户，如果没有赋予login权限，则这个角色只能在pg_roles里面看到，而在pg_user里面看不到

pg_auth_members

目录pg_auth_members展示了角色之间的成员关系。允许任何无环的关系集合

postgres@[local]:5432=#\d pg_auth_members
           Table "pg_catalog.pg_auth_members"
    Column    |  Type   | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
 roleid       | oid     |           | not null |
 member       | oid     |           | not null |
 grantor      | oid     |           | not null |
 admin_option | boolean |           | not null |
Indexes:
    "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global"
    "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global"
Tablespace: "pg_global"

postgres@[local]:5432=#select * from pg_auth_members
postgres-# ;
 roleid | member | grantor | admin_option
--------+--------+---------+--------------
   3374 |   3373 |      10 | f
   3375 |   3373 |      10 | f
   3377 |   3373 |      10 | f
(3 rows)

查看组角色的名字。

postgres@[local]:5432=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.roleid;
       rolname
----------------------
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
(3 rows)
查看成员角色的名字
postgres@[local]:5432=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.member;
  rolname
------------
 pg_monitor
 pg_monitor
 pg_monitor
(3 rows)

如果需要用一个结果集获取角色之间的隶属关系，可以将这两个结果集作为子查询后再进行关联。
SELECT r.rolname, r.rolsuper, r.rolinherit,r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,r.rolconnlimit, r.rolvaliduntil,ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication, r.rolbypassrls FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;

postgres@[local]:5432=#SELECT r.rolname, r.rolsuper, r.rolinherit,r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,r.rolconnlimit, r.rolvaliduntil,ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication, r.rolbypassrls FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolconnlimit | rolvaliduntil | memberof | rolreplication | rolbyp
assrls
----------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------+----------------+-------
-------
 pguser   | t        | t          | t             | t           | t           |           -1 |               | {}       | t              | f
 postgres | t        | t          | t             | t           | t           |           -1 |               | {}       | t              | t
 testuser | f        | t          | f             | t           | t           |           -1 |               | {}       | f              | f
 yanwei   | t        | t          | t             | t           | t           |           -1 |               | {}       | f              | f
(4 rows)
postgres@[local]:5432=#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create DB                                                  | {}
 yanwei    | Superuser, Create role, Create DB                          | {}

postgres@[local]:5432=#

5 schema权限

​ 一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突；比如，schema1和myschema都可以包含一个名为mytable的表。和数据库不同，模式不是严格分离的：只要有权限，一个用户可以访问他所连接的数据库中的任意模式中的对象。

​ schema级别的权限，包括允许查看schema中的对象，允许在schema中创建对象。
​ 默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

schema的特点

• schema概念像命名空间
• schema下不能再有schema嵌套
• 各个对象比如表,函数等存放在各个schema下
• 同一个schema下不能有重复的对象名字,但在不同schema下可以重复

使用schema的作用

• 方便管理多个用户共享一个数据库,但是又可以互相独立.
• 方便管理众多对象,更有逻辑性
• 方便兼容某些第三方应用程序,创建对象时是有schema的

比如要设计一个复杂系统，由众多模块构成，有时候模块间又需要有独立性。各模块存放单独的数据库显然是不合适的。 这时候使用schema来分类各模块间的对象，再对用户进行适当的权限控制，这样逻辑也非常清晰。

常用数据库中的schema异同

大多数数据库都有schema或者同等意义的概念，但是含义和具体操作不同。

• PostgreSQL中，一个database下可以有多个schema。可以给schema指定一个owner，如果没有指定，那么当前用户就是schema的默认owner。
• 在Oracle数据库中不能直接新建一个schema，系统在创建一个用户的同时为这个用户创建一个同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同。
• 在MySQL中没有schema，所以创建一个database的效果和建立一个schema是相同的。我们可以简单的理解为，MySQL中的database就是schema。

5.1 查看schema

postgres@[local]:5432=#\dnS+  --列出所有模式
                                     List of schemas
        Name        |  Owner   |  Access privileges   |           Description
--------------------+----------+----------------------+----------------------------------
 information_schema | postgres | postgres=UC/postgres+|
                    |          | =U/postgres          |
 osdba              | postgres |                      |
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema
                    |          | =U/postgres          |
 pg_temp_1          | postgres |                      |
 pg_toast           | postgres |                      | reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |                      |
 pguser             | pguser   |                      |
 public             | postgres | postgres=UC/postgres+| standard public schema
                    |          | =UC/postgres         |
(8 rows)

postgres@[local]:5432=#\dn
  List of schemas
  Name  |  Owner
--------+----------
 osdba  | postgres
 pguser | pguser
 public | postgres
(3 rows)

postgres@[local]:5432=#\dnS
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 osdba              | postgres
 pg_catalog         | postgres
 pg_temp_1          | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 pguser             | pguser
 public             | postgres
(8 rows)

postgres@[local]:5432=#


\du[S+] [PATTERN]      列出角色

\ddp     [模式]    列出默认权限

\drds [模式1 [模式2]] 列出每个数据库的角色设置

5.2 如何创建schema和在此schema上创建对象

创建schema yanwei
yanwei@[local]:5432=#create schema yanwei;
CREATE SCHEMA
yanwei@[local]:5432=#\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 yanwei | yanwei
(2 rows)


指定方式创建yanwei.test1
yanwei@[local]:5432=#create table yanwei.test1 (id int);
CREATE TABLE
yanwei@[local]:5432=#create table test1 (id int);
ERROR:  relation "test1" already exists

不指定方式创建
yanwei@[local]:5432=#create table test2 (id int);
CREATE TABLE
yanwei@[local]:5432=#\d
        List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+--------
 public | test  | table | yanwei
 yanwei | test1 | table | yanwei
 yanwei | test2 | table | yanwei
(3 rows)

删除schema
yanwei@[local]:5432=#drop schema yanwei;
ERROR:  cannot drop schema yanwei because other objects depend on it
DETAIL:  table test1 depends on schema yanwei
table test2 depends on schema yanwei
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
yanwei@[local]:5432=#drop schema yanwei cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table test1
drop cascades to table test2
DROP SCHEMA
yanwei@[local]:5432=#\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

yanwei@[local]:5432=#\d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | test  | table | yanwei
 public | test1 | table | testuser
(2 rows)

5.3 创建一个他人拥有的模式

通常你想创建一个他人拥有的模式(因为这是一种限制用户在定义良好的模式中的活动的方法)。其语法如下：

CREATE SCHEMA schemaname AUTHORIZATION username;
你甚至可以省略模式名字，这时模式名将和用户名同名。

yanwei@[local]:5432=#create user jack superuser;
CREATE ROLE
yanwei@[local]:5432=#\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

yanwei@[local]:5432=#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 jack      | Superuser                                                  | {}
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create DB                                                  | {}
 yanwei    | Superuser, Create role, Create DB                          | {}

postgres@[local]:5432=#CREATE SCHEMA schemaname AUTHORIZATION yanwei;
CREATE SCHEMA
postgres@[local]:5432=#\dn
    List of schemas
    Name    |  Owner
------------+----------
 osdba      | postgres
 pguser     | pguser
 public     | postgres
 schemaname | yanwei
(4 rows)

schemaname拥有者就是yanwei不是jack。

postgres@[local]:5432=#create table schemaname.test(id int);
CREATE TABLE

postgres@[local]:5432=#\d
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | heap_t    | table | postgres
 public | tbl_david | table | postgres
 public | test      | table | postgres
 public | v_data    | view  | postgres
(4 rows)

postgres@[local]:5432=#SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

postgres@[local]:5432=#\c - yanwei
You are now connected to database "postgres" as user "yanwei".
postgres@[local]:5432=#\d
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | heap_t    | table | postgres
 public | tbl_david | table | postgres
 public | test      | table | postgres
 public | v_data    | view  | postgres
(4 rows)

postgres@[local]:5432=#\dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | heap_t    | table | postgres
 public | tbl_david | table | postgres
 public | test      | table | postgres
(3 rows)

postgres@[local]:5432=#\dn
    List of schemas
    Name    |  Owner
------------+----------
 osdba      | postgres
 pguser     | pguser
 public     | postgres
 schemaname | yanwei
(4 rows)

因为默认是的搜索路径是"$user", public；因此不管是jack和yanwei，都无法搜索到 schemaname.test
可以直接查询
postgres@[local]:5432=#select * from  schemaname.test;
 id
----
(0 rows)

postgres@[local]:5432=#\d pg_tables
              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          |
 tablename   | name    |           |          |
 tableowner  | name    |           |          |
 tablespace  | name    |           |          |
 hasindexes  | boolean |           |          |
 hasrules    | boolean |           |          |
 hastriggers | boolean |           |          |
 rowsecurity | boolean |           |          |

postgres@[local]:5432=#select　* from pg_tables where schemaname='schemaname';
ERROR:  syntax error at or near "select　"
LINE 1: select　* from pg_tables where schemaname='schemaname';
        ^
postgres@[local]:5432=#select * from pg_tables where schemaname='schemaname';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 schemaname | test      | jack       |            | f          | f        | f           | f
(1 row)

postgres@[local]:5432=#

schema使用 , 特别注意

According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else, or a superuser chooses to create objects in it.

schema的owner默认是该schema下的所有对象的owner，但是PostgreSQL又允许用户在别人的schema下创建对象，所以一个对象可能属于两个owner，而且schema 的owner有 drop对象的权限。  
对于两个owner都有drop的权限，这个我个人认为是一个BUG。  

所以千万不要把自己的对象创建到别人的schema下面，那很危险。

5.4 搜索路径

SHOW search_path;
在缺省的设置中，返回下面的东西：

search_path
--------------
"$user",public
第一个元素声明搜索和当前用户同名的模式。因为还没有这样的模式存在，所以这条记录被忽略。第二个元素指向我们已经看过的公共模式。

要设置模式的搜索路径，可以用(省略了$user是因为并不立即需要它)

SET search_path TO schemaname,public;
然后我们就可以不使用模式修饰来访问表了：
postgres@[local]:5432=#SET search_path TO schemaname,public;
SET
postgres@[local]:5432=#show search_path
postgres-# ;
    search_path
--------------------
 schemaname, public
(1 row)

postgres@[local]:5432=#\d
             List of relations
   Schema   |   Name    | Type  |  Owner
------------+-----------+-------+----------
 public     | heap_t    | table | postgres
 public     | tbl_david | table | postgres
 public     | v_data    | view  | postgres
 schemaname | test      | table | jack
(4 rows)


退出psql后，恢复默认值
postgres@[local]:5432=#\q
[postgres@pgserver12 ~]$ psql
psql (12.5)
Type "help" for help.

postgres@[local]:5432=#\c yanwei
You are now connected to database "yanwei" as user "postgres".
yanwei@[local]:5432=#show search_path
yanwei-# ;
   search_path
-----------------
 "$user", public
(1 row)



DROP TABLE test;
同样，因为schemaname是路径中的第一个元素，新对象缺省时将创建在这里。

我们也可以写成：

SET search_path TO myschema;
然后我们如果不明确修饰的话，就不能再访问 public 模式了。public 模式没有任何特殊之处，只不过它缺省时就存在。我们也可以删除它。

5.4.1 如何让search_path永久生效呢

1、您可以在数据库级别设置默认的search_path ：

 ALTER DATABASE <database_name> SET search_path TO schema1,schema2; 
2 或者在用户或angular色级别：
 ALTER ROLE <role_name> SET search_path TO schema1,schema2; 

3 或者，如果在所有数据库中都有一个通用的默认模式，则可以使用search_path选项在configuration文件中设置系统范围的默认模式。
创build数据库时，默认情况下是从名为template1的隐藏“模板”数据库创build的，您可以更改该数据库以指定将来创build的所有数据库的新默认searchpath。 您还可以创build另一个模板数据库，并使用CREATE DATABASE <database_name> TEMPLATE <template_name>创build数据库。


4 ALTER ROLE <role_name> IN DATABASE <db_name> SET search_path TO schema1,schema2; 
在一个特定的数据库中设置用户的searchpath。

ALTER ROLE yanwei IN DATABASE postgres SET search_path TO schemaname,"$user",public; 

比如：这样退出就不会

postgres@[local]:5432=#ALTER ROLE yanwei IN DATABASE postgres SET search_path TO schemaname,"$user",public;
ALTER ROLE
postgres@[local]:5432=#
postgres@[local]:5432=#show search_path
postgres-# ;
   search_path
-----------------
 "$user", public
(1 row)

postgres@[local]:5432=#\c postgres yanwei
You are now connected to database "postgres" as user "yanwei".
postgres@[local]:5432=#show search_path
;
         search_path
-----------------------------
 schemaname, "$user", public
(1 row)

postgres@[local]:5432=#\q
[postgres@pgserver12 ~]$ psql postgres yanwei;
psql (12.5)
Type "help" for help.

postgres@[local]:5432=#show search_path
;
         search_path
-----------------------------
 schemaname, "$user", public
(1 row)

postgres@[local]:5432=#

5.5 public模式

我们没有声明任何模式名字就创建了表。缺省时，这样的表(以及其它对象)都自动放到一个叫做"public"的模式中去了。每个新数据库都包含一个这样的模式。因此，下面的命令是等效的：

CREATE TABLE products ( … );
和：

CREATE TABLE public.products ( … );

public模式的权限配置比较灵活，但是也易导致一些安全隐患，所以如果不需要使用public模式可以将它删除或者撤销默认的权限来将它锁定

5.5.1 如何撤销public相关权限

revoke create on schema public from PUBLIC;
这条命令中，我们移除了public模式中的CREATE权限。注意，我们使用了两个public，分别为一个大写和一个小写。小写的public指的是模式，实际使用中可以被替换为数据库中其他任意有效的模式名。
而大写的PUBLIC是一个特殊的关键字，代表了all users。实际使用中可以被替换为一个特定的角色名或者以逗号分隔的角色名列表。

postgres@[local]:5432=#\dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 osdba      | postgres |                      |
 pguser     | pguser   |                      |
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =UC/postgres         |
 schemaname | yanwei   |                      |
(4 rows)

postgres@[local]:5432=#revoke create on schema public from PUBLIC;
REVOKE
postgres@[local]:5432=#\dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 osdba      | postgres |                      |
 pguser     | pguser   |                      |
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =U/postgres          |
 schemaname | yanwei   |                      |
(4 rows)

使用\dn+命令查询修改后的权限，发现权限信息中第二行里的C权限已经被移除，证明之前的REVOKE命令是有效的。现在，除了postgres用户以外的用户将不再能在public模式中创建表、视图或其他对象。

进一步的锁定措施可能需要通过移除USAGE权限来拒绝对模式的查询访问。
REVOKE usage on schema public from PUBLIC;
postgres@[local]:5432=#REVOKE usage on schema public from PUBLIC;
REVOKE
postgres@[local]:5432=#\dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 osdba      | postgres |                      |
 pguser     | pguser   |                      |
 public     | postgres | postgres=UC/postgres | standard public schema
 schemaname | yanwei   |                      |
(4 rows)

第三列的=U/postgres 完全消失了，这下只有postgres能用了，其他的都白扯了

如何恢复？

grant usage on schema public to PUBLIC;
grant create on schema public to PUBLIC;


postgres@[local]:5432=#grant usage on schema public to PUBLIC;
GRANT
postgres@[local]:5432=#\dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 osdba      | postgres |                      |
 pguser     | pguser   |                      |
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =U/postgres          |
 schemaname | yanwei   |                      |
(4 rows)

postgres@[local]:5432=#grant create on schema public to PUBLIC;
GRANT
postgres@[local]:5432=#\dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 osdba      | postgres |                      |
 pguser     | pguser   |                      |
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =UC/postgres         |
 schemaname | yanwei   |                      |
(4 rows)

postgres@[local]:5432=#

5.5.2 只读账号的使用方式

通过上述的讲解可以快速创建一个只读账号，过程如下：

revoke create on schema public from public;  -因为每个用户都可以创建到public

然后创建用户 create user readonly with password ‘test’;

然后在把所有比如public的表查询权限授权：

grant select on all tables in schema public to readonly；

但是如果新创建了，就不能查询到了。如何测试和处理呢？

alter default priviges in schame public grant select on tables to readonly;

如果想只读其他schema下的表：

1）grant select on all tables in schema xxx to readonly；

2)alter default priviges in schame public grant select on tables to readonly;

5.5.3 用户针对某一个database和schema方式只读（另一种方式）

创建数据库即读写 只读用户(一个用户对数据库拥有所有权限)：
postgres:
create role yanwei_rw with password ‘123’ login;
create role yanwei_r with password ‘123’ login;
create database dba_db owner yanwei;
revoke all on DATABASE dba_db from PUBLIC;
grant CONNECT on DATABASE dba_db to yanwei_r;
切换至dba_db数据库下
create schema yanweidba;
grant USAGE on SCHEMA yanweidba to yanwei_r;
grant SELECT on ALL tables in schema yanwei_rw to yanwei_r;
alter user yanwei_r set default_transaction_read_only to off; –设置只读用户



默认权限设置，上面的权限赋予只能解决已经存在的表权限，当我们需要给用户新建表的权限时，需要设置默认权限。

alter default privileges in schema schma_name grant select ON tables to username;

查询用户权限：
select from information_schema.table_privileges where grantee='user_name';*

5.5.4 其他测试

参考：http://blog.itpub.net/30126024/viewspace-2661690/

db owner不一定能操作其下面的某个schema，schema owner不一定能操作其下面的某张表

db owner不一定能操作其下面的某个schema
schema owner不一定能操作其下面的某张表

1、superuser建立3个用户dbuser1、schemauser1、schemauser2，授权用户dbuser1具备create db权限
create user dbuser1 createdb password '123456';
create user schemauser1 password '123456';
create user schemauser2 password '123456';

2、dbuser1创建DB1，superuser授权schemauser1、schemauser2在db1上有创建schema的权限
\c - dbuser1
create database db1;
\c - postgres
grant create on database db1 to schemauser1;
grant create on database db1 to schemauser2;

3、schemauser1、schemauser2分别在db1上创建schema1、schema2，并建立表schema1.table1、schema2.table2
\c db1
\c - schemauser1
create schema schema1;
create table schema1.table1 (hid int);
insert into schema1.table1 values (1),(2);
select * from schema1.table1;
\c - schemauser2
create schema schema2;
create table schema2.table2 (hid int);
insert into schema2.table2 values (1),(2);
select * from schema2.table2;

4、superuser在db1.schema1、db1.schema2上建立表supertable1，supertable2
\c - postgres
create table schema1.supertable1 (hid int);
insert into schema1.supertable1 values (1),(2);
select * from schema1.supertable1;
create table schema2.supertable2 (hid int);
insert into schema2.supertable2 values (1),(2);
select * from schema2.supertable2;

5、验证
5.1、dbuser1是否可以查询schema1.table1、schema2.table2、schema1.supertable1、schema2.supertable2
不可以
5.2、dbuser1是否可以在schema1、schema2上建立表schema1.dbtable1、schema2.dbtable2   
不可以  
5.3、schemauser1是否可以查询schema1.supertable1、schema2.table2、schema2.supertable2
不可以
5.4、schemauser2是否可以查询schema2.supertable2、schema1.table1、schema1.supertable1
不可以

\c - dbuser1
db1=> select * from pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-------------+-------------+------------+------------+----------+-------------+-------------
 schema1  | supertable1 | postgre2  |      | f     | f    | f      | f
 schema2  | supertable2 | postgre2  |      | f     | f    | f      | f
 schema1  | table1   | schemauser1 |      | f     | f    | f      | f
 schema2  | table2   | schemauser2 |      | f     | f    | f      | f
(4 rows)

db1=> select * from schema1.table1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> select * from schema1.supertable1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.supertable1;

db1=> create table schema1.dbtable1 (hid int);
ERROR: permission denied for schema schema1
LINE 1: create table schema1.dbtable1 (hid int);
db1=> create table schema2.dbtable2 (hid int);
ERROR: permission denied for schema schema2
LINE 1: create table schema2.dbtable2 (hid int);

光授权select on all tables in schema，而没有授权usage on schema，用户无法查询schema下的表

光授权select on all tables in schema，而没有授权usage on schema，用户无法查询schema下的表

postgres=# create user testuser1 password '123456';
CREATE ROLE
postgres=# create user testuser2 password '123456';
CREATE ROLE

db1=# grant select on all tables in schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> \c - postgres
db1=# grant usage on schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
 count
\-------
   2
(1 row)

db1=# grant usage on schema schema1 to testuser2;
GRANT
db1=# grant select on all tables in schema schema1 to testuser2;
GRANT
db1=# \c - testuser2
You are now connected to database "db1" as user "testuser2".
db1=> select count(*) from schema1.table1;
 count
\-------
   2
(1 row)

没有createdb权限，则无法创建database，有了createdb权限还可以在自己创建的db下创建schema

没有createdb权限，则无法创建database，有了createdb权限还可以在自己创建的db下创建schema
postgres=# \c - testuser1
You are now connected to database "postgres" as user "testuser1".
postgres=> create database testdb;
ERROR: permission denied to create database
postgres=>\c - postgres
postgres=# alter user testuser1 createdb;
postgres=# \c - testuser1
postgres=> create database testdb;
CREATE DATABASE
postgres=> \c testdb
You are now connected to database "testdb" as user "testuser1".
testdb=> create schema tests1;
CREATE SCHEMA

在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表

在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表
testdb=> \c db1
You are now connected to database "db1" as user "testuser1".
db1=> create schema tests2;
ERROR: permission denied for database db1
testdb=>\c - postgres
db1=# grant CREATE on database db1 to testuser1;
db1=# \c - testuser1
db1=> create schema tests2;
db1=> create table tests2.table1 (hid int);

在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表

在其他db_ower的db下，没有授权CREATE on database权限的话，用户无法创建schema，有了create权限后，在自己建立的schema下可以创建表
testdb=> \c db1
You are now connected to database "db1" as user "testuser1".
db1=> create schema tests2;
ERROR: permission denied for database db1
testdb=>\c - postgres
db1=# grant CREATE on database db1 to testuser1;
db1=# \c - testuser1
db1=> create schema tests2;
db1=> create table tests2.table1 (hid int);

在其他schema_owner的schema下，没有CREATE on schema权限的话，用户无法创建表
db1=> \c - postgres
db1=# create schema tests3;
db1=# \c - testuser1
db1=> create table tests3.table (hid int);
ERROR: permission denied for schema tests3
LINE 1: create table tests3.table (hid int);
db1=> \c - postgres
db1=# grant CREATE on schema tests3 to testuser1;
db1=> create table tests3.table (hid int);
CREATE TABLE

5.6 系统表模式-pg_catalog

​ 除了public和用户创建的模式之外，每个数据库都包含一个pg_catalog模式，它包含系统表和所有内置数据类型、函数、操作符。pg_catalog总是搜索路径中的一部分。如果它没有明确出现在路径中，那么它隐含地在所有路径之前搜索。这样就保证了内置名字总是可以被搜索。不过，你可以明确地把pg_catalog放在搜索路径之后，如果你想使用用户自定义的名字覆盖内置的名字的话。

​ 自从系统表名以pg_开头开始，最好避免使用这样的名字，以保证自己将来不会和新版本冲突：那些版本也许会定义一些和你的表同名的表(在缺省搜索路径中，一个对你的表的无修饰引用将解析为系统表)。系统表将继续遵循以pg_开头的传统，因此，只要你的表不是以pg_开头，就不会和无修饰的用户表名字冲突。

撤销查询权限

REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC;

GRANT SELECT ON TABLE [table] TO [user];

5.7 移植性

在 SQL 标准里，在同一个模式里的对象被不同的用户所有的概念是不存在的。而且，有些实现不允许你创建和它们的所有者不同名的模式。实际上，模式的概念和用户在那些只实现了标准中规定的基本模式支持的数据库系统里几乎是一样的。因此，许多用户考虑对名字加以修饰，使它们真正由username.tablename 组成。如果你为每个用户都创建了一个模式，这实际上就是PostgreSQL的行为。

同样，在 SQL 标准里也没有public模式的概念。为了最大限度地遵循标准，你不应该使用(可能甚至是应该删除)public模式

6 对象权限和系统权限

6.1 grant的语法参考

角色/用户在GRANT方式赋予权限，REVOKE方式撤销权限	说明
SELECT	允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。
INSERT	允许将新行INSERT到指定的表中。如果指定特定列，那么其它列将以默认值写入。
UPDATE	允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE	允许删除指定表中的行，需要SELECT权限。
TRUNCATE	允许在指定的表上创建触发器。
REFERENCES	允许创建引用指定表或表的指定列的外键约束。
TRIGGER	允许在指定的表上创建触发器。
CREATE	对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT	允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY	允许在使用指定数据库时创建临时表。
EXECUTE	允许使用指定的函数或过程以及在函数。
USAGE	对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES	一次授予所有可用权限。
SAME PRIVILEGES

对象级别的权限，每种类型的对象权限属性都不一样，
以表为例，可以有SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER这些权限。

postgres@[local]:5432=#\h grant
Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-grant.html

SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。

INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。

UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。

DELETE：允许删除指定表中的行，需要SELECT权限。

TRUNCATE：允许在指定的表上创建触发器。

REFERENCES：允许创建引用指定表或表的指定列的外键约束。

TRIGGER：允许在指定的表上创建触发器。 

CREATE：对于数据库，允许在数据库中创建新的schema、table、index。

CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。

TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。

EXECUTE：允许使用指定的函数或过程以及在函数。

USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。

ALL PRIVILEGES：一次授予所有可用权限。

6.2 授权测试

6.2.1 单表/所有表授权

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

##单表授权：授权yanwei账号可以访问schema为schema1的test表
grant select,insert,update,delete on schema1.test to yanwei;
##所有表授权：
grant select,insert,update,delete on all tables in schema schema1 to yanwei;

6.2.2 列授权

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

##列授权，授权指定列(schema1 schema下的test表的name列)的更新权限给yanwei用户
grant update (name) on schema1.test to yanwei;
##指定列授不同权限，zjy schema下的zjy表，查看更新name、age字段，插入name字段
grant select (name),update (name,age),insert(name,comm) on schema1.test to yanwei;

6.2.3 序列授权

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    

##序列（自增键）属性授权，指定schema1 schema下的seq_id_seq 给yanwei用户
grant select,update on sequence schema1.seq_id_seq to yanwei;
##序列（自增键）属性授权，给用户zjy授权zjy schema下的所有序列
grant select,update on all sequences in schema schema1 to yanwei;

6.2.4 database创建schema，连接，使用temp

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    
##连接数据库权限，授权test1用户连接数据库testdb
grant connect on database testdb to test1;

##授权test1可以在testdb上创建schema
grant create on database testdb to test1;

##允许在使用该数据库的时候创建临时表。
grant TEMP on database testdb to test1;

6.2.5 schema上的授权

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    
 ##连接schema权限，授权test1访问schema1 schema权限
grant usage on schema schema1 to test1;

6.2.6 授权用户

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]
##把test1用户的权限授予用户test。
grant test to test1;

具体集成，用户组等参考我的https://www.modb.pro/db/103876

6.2.7 WITH ADMIN OPTION

WITH ADMIN OPTION表示被赋予权限的用户，拿到对应的权限后，还能将对应的权限赋予给其他人，否则只能自己有这个权限，但是不能再赋予给其他人。

6.3 如何查看和解读一个对象的当前权限状态

以表为例 ：

select relname,relacl from pg_class where relkind='r';

或者执行

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT polname
    || CASE WHEN polcmd != '*' THEN
           E' (' || polcmd || E'):'
       ELSE E':' 
       END
    || CASE WHEN polqual IS NOT NULL THEN
           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
       ELSE E''
       END
    || CASE WHEN polwithcheck IS NOT NULL THEN
           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
       ELSE E''
       END    || CASE WHEN polroles <> '{0}' THEN
           E'\n  to: ' || pg_catalog.array_to_string(
               ARRAY(
                   SELECT rolname
                   FROM pg_catalog.pg_roles
                   WHERE oid = ANY (polroles)
                   ORDER BY 1
               ), E', ')
       ELSE E''
       END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n')
    AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

得到权限说明如下

 Schema |      Name       |   Type   |       Access privileges        | Column privileges | Policies 
--------+-----------------+----------+--------------------------------+-------------------+----------
 public | sbtest1         | table    | postgres=arwdDxt/postgres     +|                   | 
        |                 |          | digoal=a*r*w*d*D*x*t*/postgres |                   | 
 public | sbtest10        | table    | postgres=arwdDxt/postgres      |                   | 
 public | sbtest10_id_seq | sequence |                                |                   | 
 public | sbtest11        | table    | postgres=arwdDxt/postgres      |                   | 
 public | sbtest11_id_seq | sequence |                                |                   | 
 public | sbtest12        | table    | postgres=arwdDxt/postgres      |                   | 
 public | sbtest12_id_seq | sequence |                                |                   | 

解释一下 Access privileges
rolename=xxx 其中rolename就是被赋予权限的用户名，即权限被赋予给谁了?
=xxx 表示这个权限赋予给了public角色，即所有人
/yyyy 表示是谁赋予的这个权限?
权限的含义如下

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

例子
赋予权限的人是postgres用户， sbtest2表的select权限被赋予给了digoal用户。

postgres=# grant select on sbtest2 to digoal;
GRANT
postgres=# \dp+ sbtest2
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies 
--------+---------+-------+---------------------------+-------------------+----------
 public | sbtest2 | table | postgres=arwdDxt/postgres+|                   | 
        |         |       | digoal=r/postgres         |                   | 
(1 row)

回收权限一定要针对已有的权限来，如果你发现这里的权限还在，那照着权限回收即可。
例如

revoke select on sbtest2 from digoal;  

6.4 some_privileges说明

some_priviledges表示在这个数据库对象中的权限，database_object_type是对象类型：如table，序列，schema等

grant some_privileges on <database_object_type> object_name to role_name;

some_privilege“ 主要包含一下权限：

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
TEMP
ALL PRIVILEGES

6.5 ALL PRIVILEES说明

ALL PRIVILEGES：表示一次性给予可以授予的权限。PRIVILEGES可以省略。

7 表空间权限使用

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

查询是否具有tablespace相关权限

postgres@[local]:5432=#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 jack      | Superuser                                                  | {}
 pguser    | Superuser, Create role, Create DB, Replication             | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create DB                                                  | {}
 yanwei    | Superuser, Create role, Create DB                          | {}

postgres@[local]:5432=#\db
                  List of tablespaces
    Name    |  Owner   |            Location
------------+----------+--------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 tbs_mydb   | pguser   | /data/pg12.5/pgdata/tbs_mydb
 yanwei_tbs | yanwei   | /data/pg12.5/pgdata/yanwei_tbs


select has_tablespace_privilege('yanwei','yanwei_tbs','create'); 
postgres@[local]:5432=#select has_tablespace_privilege('yanwei','yanwei_tbs','create');
 has_tablespace_privilege
--------------------------
 t
(1 row)

postgres@[local]:5432=#


查看大小
postgres=# select pg_size_pretty(pg_tablespace_size('yanwei_tbs')); 

8 用户和权限常用语句

查看用户权限

select * from information_schema.table_privileges where grantee='xxx';

查看所有schema

admindb=# \dnS
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
relation_schema | postgres

查看public下所有表

admindb=# \dt public.*
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | authors | table | postgres
public | books | table | admin

查看表的权限

select relname,relacl from pg_class where relkind='r';

9 权限模型最佳实践（转载）

Hologres专家权限模型最佳实践一

本次实践以表对象为例，您也可以选用其他对象进行实验。

表的Owner是对应的XXX_DEV_GROUP权限组，因此，DEV_GROUP组中的任意成员都可以管理或删除该表。

例如，用户被添加到PROJ1_DEV_GROUP用户组之后，就拥有PROJ1项目中表的管理或删除权限。具体操作步骤如下：

1. 创建用户组。

   您可以根据业务需求划分权限模型，由Superuser创建相应的用户组，以PROJ1项目为例，示例语句如下。

   create role PROJ1_DEV_GROUP; //表的Owner，拥有表的所有操作权限。
   create role PROJ1_WRITE_GROUP; //表的写入权限，可以写入数据至相应表。
   create role PROJ1_VIEW_GROUP; //查看表数据权限，可以查看相应表的数据。
   

2. 授权用户组Schema的权限。

   您需要授予创建完成的用户组Schema的权限，示例项目PROJ1可以在Schema1中工作，语句如下。

   授权PROJ1拥有SCHEMA1中的相关权限。
   grant create,usage on schema SCHEMA1 to PROJ1_DEV_GROUP;
   grant usage on schema SCHEMA1 to PROJ1_WRITE_GROUP;
   grant usage on schema SCHEMA1 to PROJ1_VIEW_GROUP;
   

   说明

   • 一个项目可以对应多个Schema，一个Schema也可以对应多个项目。
   • 默认public Schema中所有用户都有CREATE和USAGE权限。

3. 创建用户并管理用户组。

   授权用户组Schema权限后，Superuser需要创建用户并添加用户至对应的用户组，示例语句如下。

   create user "USER1";
   grant PROJ1_DEV_GROUP to "USER1"; 
   
   create user "USER2";
   grant PROJ1_VIEW_GROUP to "USER2";
   

4. 创建表并授权。

   创建表等对象时，由表的创建者（必须为PROJ1_DEVE_GROUP的成员）或Superuser执行相应的授权语句（假设新创建的表为TABLE1）。 示例如下。

   grant all on table SCHEMA1.TABLE1 to PROJ1_WRITE_GROUP; //授予PROJ1_WRITE_GROUP写入数据至TABLE1的权限。
   grant select on table SCHEMA1.TABLE1 to PROJ1_VIEW_GROUP; //授予PROJ1_VIEW_GROUP TABLE1的SELECT权限。
   alter table SCHEMA1.TABLE1 owner to PROJ1_DEV_GROUP; //修改TABLE1的Owner为PROJ1_DEV_GROUP。
   

Hologres专家权限模型最佳实践二

本次实践，使用ALTER DEFAULT PRIVILEGES语句简化对每张表的授权操作。

您需要提前确定创建的表默认属于哪个项目。具体操作步骤如下：

1. 创建用户组。

   您可以根据业务需求划分权限模型，由Superuser创建相应的用户组，以PROJ1项目为例，示例语句如下。

   create role PROJ1_DEV_GROUP; //表的Owner，拥有表的所有操作权限。
   create role PROJ1_WRITE_GROUP; //表的写入权限，可以写入数据至相应表。
   create role PROJ1_VIEW_GROUP; //查看表数据权限，可以查看相应表的数据。
   

2. 授权用户组Schema的权限。

   您需要授予创建完成的用户组Schema的权限，示例项目PROJ1可以在Schema1中工作，语句如下。

   授权PROJ1拥有SCHEMA1中的相关权限。
   grant create,usage on schema SCHEMA1 to PROJ1_DEV_GROUP;
   grant usage on schema SCHEMA1 to PROJ1_WRITE_GROUP;
   grant usage on schema SCHEMA1 to PROJ1_VIEW_GROUP;
   

   说明

   • 一个项目可以对应多个Schema，一个Schema也可以对应多个项目。
   • 默认public Schema中所有用户都有CREATE和USAGE权限。

3. 创建用户并设置默认授权。

   完成Schema授权后，需要Superuser创建用户并添加用户至相应的组中，同时设置该用户创建表时拥有的默认权限。

   USER1创建的表默认属于PROJ1_DEV_GROUP，并且USER1为合法的阿里云账号。示例设置授权语句如下。

   create user "USER1";
   
   alter default privileges for role "USER1" grant all on tables to PROJ1_DEV_GROUP; //设置USER1创建的表，PROJ1_DEV_GROUP默认都有读写权限。
   alter default privileges for role "USER1" grant all on tables to PROJ1_WRITE_GROUP; //设置USER1创建的表，PROJ1_WRITE_GROUP默认都有读写权限。
   alter default privileges for role "USER1" grant select on tables to PROJ1_VIEW_GROUP; //设置USER1创建的表，PROJ1_VIEW_GROUP默认都有读写权限。
   
   grant PROJ1_DEV_GROUP to "USER1"; //添加USER1至PROJ1_DEV_GROUP。
   

4. 修改表的Owner。

   如果您希望DEV_GROUP中的其他用户也可以管理或删除创建的表，则可以修改表的Owner为对应项目的DEV_GROUP，例如PROJ1_DEV_GROUP。

   修改表Owner的语句必须由表的创建者或Superuser执行。例如，示例中表的创建者必须是PROJ1_DEV_GROUP的成员。假设新创建的表为TABLE1。示例修改表Owner的语句如下。

   alter table SCHEMA1.TABLE1 owner to PROJ1_DEV_GROUP; //修改TABLE1的Owner为PROJ1_DEV_GROUP。
   

   如下情况，您可以修改表的Owner：

   • 新创建的表，由Superuser定期修改表Owner。
   • 在需要管理或删除表之前修改表Owner。

   说明 如果您可以确认表的管理或删除操作是由表的创建者或Superuser执行的，您也可以不执行上述命令。

5. 修改用户的默认项目。

   调整用户的默认项目，需要Superuser或用户本人执行 alter default privileges命令，撤销已设置的缺省权限后，使用新的 alter default privileges创建默认授权。

   将USER1的默认项目由PROJ1改为PROJ2，该操作不影响现有的表。示例语句如下。

   取消原有默认授权。
   alter default privileges for role "USER1" revoke all on tables from PROJ1_DEV_GROUP;
   alter default privileges for role "USER1" revoke all on tables from PROJ1_WRITE_GROUP;
   alter default privileges for role "USER1" revoke select on tables from PROJ1_VIEW_GROUP;
   
   创建新的默认授权。
   alter default privileges for role "USER1" grant all on tables to PROJ2_DEV_GROUP;
   alter default privileges for role "USER1" grant all on tables to PROJ2_WRITE_GROUP;
   alter default privileges for role "USER1" grant select on tables to PROJ2_
   

项目场景案例

业务场景：

三个独自子系统bas、app1、app3

bas系统的数据为app1和app3系统所共有的基础数据

app1可修改bas系统数据，但不能删除

app3只能查询bas系统数据，不能修改和删除

需求：只需配置一次，后续新增表无需再次配置。

1. 删除public模式，各自使用私有模式管理数据

test=# drop schema if exists public; 
DROP SCHEMA

2. 创建三个用户

test=# create user bas encrypted password '123456';
CREATE ROLE
test=# create user app1 encrypted password '123456';
CREATE ROLE
test=# create user app3 encrypted password '123456';
CREATE ROLE

3. 创建三个用户对应的schema并各自关联

test=# create schema bas authorization bas;
CREATE SCHEMA
test=# create schema app1 authorization app1;
CREATE SCHEMA
test=# create schema app3 authorization app3;
CREATE SCHEMA

4. 三个用户各自创建测试表

bas用户

[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas1(id int);
CREATE TABLE
test=> create table tbl_bas2(id int);
CREATE TABLE
test=> insert into tbl_bas1 values(100);
INSERT 0 1
test=> insert into tbl_bas2 values(200),(300);
INSERT 0 2

app1用户

[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> create table tbl_app1(id int);
CREATE TABLE

app3用户

[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> create table tbl_app3(id int);
CREATE TABLE

5. 权限配置

使用bas用户配置schema的usage权限给app1和app3用户

test=> grant usage on schema bas to app1,app3;
GRANT

使用bas用户配置当前所有表的select权限

test=> grant select on all tables in schema bas to app1,app3;
GRANT

使用bas用户配置当前所有表的update权限

test=> grant update on all tables in schema bas to app1;
GRANT

使用bas用户配置新增表的默认权限

test=> alter default privileges in schema bas \
grant select on tables to app1,app3;
ALTER DEFAULT PRIVILEGES
test=> alter default privileges in schema bas \
grant update on tables to app1;
ALTER DEFAULT PRIVILEGES

6. 测试验证

app3用户测试

test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=null;
ERROR:  permission denied for table tbl_bas1
test=> update bas.tbl_bas2 set id=null;
ERROR:  permission denied for table tbl_bas2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2

app1用户

test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=id+1;
UPDATE 1
test=> update bas.tbl_bas2 set id=id+1;
UPDATE 2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2

bas用户新增表tbl_bas3

[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas3(id int);
CREATE TABLE
test=> insert into tbl_bas3 values(500),(900);
INSERT 0 2

app1用户

[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> select * from bas.tbl_bas3;
 id  
-----
 500
 900
(2 rows)
test=> update bas.tbl_bas3 set id=id+1;
UPDATE 2

app3用户
[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> select * from bas.tbl_bas3;
 id  
-----
 501
 901
(2 rows)
test=> update bas.tbl_bas3 set id=id+1;
ERROR:  permission denied for table tbl_bas3

第1~3步需要数据库管理用户操作，后面配置不需要。