postgresql用户及权限管理
目录
- postgresql用户及权限管理
- 一、概述
- 1.1 角色和用户
- 1.2 用户和用户组
- 1.3 PUBLIC 角色
- 1. 创建角色
- 2. 角色属性
- 3. 角色权限继承
- 二、权限授予
- 2.1 实例权限
- PostgreSql 连接访问控制
- 一、概述
- 二、配置文件修改
- 1）postgresql.conf 文件
- 2）pg_hba.conf 文件
- 3）pg_ident.conf 文件
- 2.2 数据库权限
- 2.3 模式权限
- 2.4 对象权限
- 2.5 系统权限
- 三、权限查看
- 3.1 权限查看
- 补充默认角色
- 3.2 ACL 格式说明
- 四、撤销权限
- 五、默认权限修改
- 5.1 概述
- 5.2 语法
- 六、示例
- 6.1 创建只读用户
- 6.2 删除只读用户
- 四、特殊权限与默认设置
- 五、权限检查流程
- 六、最佳实践
- 总结
- 一、概述
一、概述
1.1 角色和用户
PostgreSQL 使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在 PostgreSQL 中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。
用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户“postgres”（通常我们把数据库安装在此用户下）下的，那么这个数据库超级用户的名称也叫“postgres”。可以用这个超级用户连接数据库，然后创建出更多的普通用户或其他超级用户。
在 SQL 标准中，用户和角色之间的区别很清楚，并且用户不会自动继承权限而角色会继承。这种行为在 PostgreSQL 中也可以实现：为要用作 SQL 角色的角色给予 INHERIT 属性，而为要用作 SQL 用户的角色给予 NOINHERIT 属性。不过，为了向后兼容 8.1 以前的发布（在其中用户总是拥有它们所在组的权限），PostgreSQL 默认给所有的角色 INHERIT 属性。
1.2 用户和用户组
PostgreSQL 可以把用户分组在一起，权限可以被授予一整个组或从一整个组回收。一旦组角色存在，可以使用 GRANT 和 REVOKE 命令增加和移除成员：
GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;
注意事项：
- 一个用户可以是多个组的成员。
- 不允许环状的成员关系。
- 不允许把一个用户授予给 PUBLIC。
1.3 PUBLIC 角色
PUBLIC 是 PostgreSQL 数据库中一个特殊的角色组，在元数据表（pg_roles）中都查不到该角色，数据库中所创建的角色都可以理解为是 PUBLIC 角色组成员。而且对 PUBLIC 权限的继承完全不受 NOINHERIT 的控制，一旦创建了一个拥有 login 权限的角色，它会立即继承 PUBLIC 角色组拥有的权限，此时如果想通过 revoke（比如 revoke connect on database）来回收的话不会成功，只能从 PUBLIC 组回收相关权限（比如 revoke connect on database from PUBLIC）。
**PU- 数据库的 connect，temp/temprary 权限。任何新建的数据库，系统会自动为 PUBLIC 角色赋予connect 和在任何 schema 下创建临时表的权限。
- public 模式的 usage，create 权限。在任何新建的数据库的 public 模式下有 usage 和 create 的权限。
- 函数的 execute 权限（仅限于 public 模式下）。BLIC 默认权限**
- 数据库的 connect，temp/temprary 权限。任何新建的数据库，系统会自动为 PUBLIC 角色赋予connect 和在任何 schema 下创建临时表的权限。
- public 模式的 usage，create 权限。在任何新建的数据库的 public 模式下有 usage 和 create 的权限。
- 函数的 execute 权限（仅限于 public 模式下）。
- 语言和数据类型（包括域）的 usage 权限。
1. 创建角色
-- 创建普通角色（无登录权限）
CREATE ROLE group_developer;-- 创建登录角色（用户）并设置密码
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';-- 创建超级用户（拥有所有权限）
CREATE ROLE super_user WITH SUPERUSER;
2. 角色属性
LOGIN：允许角色登录数据库（即用户）。
SUPERUSER：超级用户可绕过所有权限检查，创建 / 删除数据库、角色等。
CREATEDB：允许创建数据库。
CREATEROLE：允许创建和修改其他角色。
INHERIT：默认继承父角色的权限（可显式关闭）。
VALID UNTIL：角色有效期（如 VALID UNTIL '2024-12-31'）。
3. 角色权限继承
-- 将角色添加到组（继承组权限）
GRANT group_developer TO app_user;-- 撤销角色继承
REVOKE group_developer FROM app_user;
二、权限授予
在 PostgreSQL 数据库中，每个数据库对象都有一个所有者（owner）,对于大部分类型的对象，默认只有其所有者和超级用户（管理员用户）能够对该对象做任何事情。为了允许其他角色使用它，必须分配对应的权限。
根据 PostgreSQL 数据库的逻辑结构：实例（集簇）->数据库->schema->数据库对象，可将其权限分为如下几类：
- 实例权限：由 postgresql.conf，pg_hba.conf 文件控制，控制哪些用户哪些IP以哪种方式连接数据库。
- 数据库权限：是否允许连接数据库，是否允许在数据库中创建模式（schema）。
- 模式权限：是否允许查看模式中的对象，是否允许在模式下建表。
- 对象权限：对表来说就是增删改查，对函数来说就是执行，修改等。
- 系统权限：也可以称为角色属性，即创建用户时，自身携带的一些权限。
2.1 实例权限
PostgreSql 连接访问控制
一、概述
PostgreSql 数据库安装完成后，再需要做一些配置，才可以正常访问。
一般需要修改 postgresql.conf，pg_hba.conf 配置文件，有其他特殊需求的，可能还需要修改 pg_ident.conf 配置文件。
二、配置文件修改
1）postgresql.conf 文件
数据库集簇安装部署完成后，要更改其中的监听地址，否则默认只监听数据库服务器本地 localhost 地址。
另外确保监听的端口号要通畅，不被防火墙或其他网络安全策略所限制，默认端口号 5432。
listen_addresses = '*'
port = 5866
2）pg_hba.conf 文件
数据库集簇安装部署完成后，默认只允许本地连接，且连接认证方式均为 trust，生产环境建议更改为 md5 连接认证方式，并根据需求增加允许访问的客户端地址。
# TYPE DATABASE USER ADDRESS METHODlocal all all md5host all all 127.0.0.1/32 md5host all all 0.0.0.0/0 md5host all all ::1/128 md5local replication all md5host replication all 127.0.0.1/32 md5host replication all ::1/128 md5
TYPE，数据库连接方式，有如下四种
- local：匹配使用Unix域套接字的连接，如果没有此类型的记录，则不允许使用Unix域套接字连接。
- host：匹配使用TCP/IP进行的连接，主机记录匹配SSL或非SSL连接，需要配置listen_addresses。
- hostssl：匹配使用TCP/IP进行的连接，仅限于使用SSL加密进行连接，需要配置ssl参数。
- hostnossl：匹配通过TCP/IP进行的连接，不使用SSL的连接。
DATABASE：指定哪些数据库可以被连接
- 匹配的数据库名称，all 指定它匹配所有数据库。
- 复制（replication）不指定数据库。
- 多个数据库可以用逗号分隔。
USER：指定哪些用户可以连接
- 匹配的数据库用户名，all 指定它匹配所有用户。
- 可以通过用逗号分隔来提供多个用户名。
ADDRESS：指定哪些IP地址可以连接
- 匹配的客户端计算机地址，all 匹配任何 IP 地址。
- 0.0.0.0/0 表示所有 IPv4 地址。
:: 0/0 表示所有 IPv6 地址。 - 192.168.100.101/32 允许这个 ip 登录。
- 192.168.100.0/24 允许 19.168.100.0~192.168.100.255 网段登录数据库
在 IP 网络（尤其是 CIDR 表示法）中，24、32、128 指的是子网掩码中连续为 1 的比特位数，即前缀长度。它们分别用于 IPv4 和 IPv6 地址，含义如下：
| 前缀长度 | 适用协议 | 子网掩码（点分十进制 / 缩写） | 包含的 IP 地址数 | 典型用途 |
|---|---|---|---|---|
| 24 | IPv4 | 255.255.255.0 |
256 个（254 个可用主机） | 小型局域网（如 /24 网段） |
| 32 | IPv4 | 255.255.255.255 |
1 个 | 单个主机（如 Loopback 或点对点链路中的具体地址） |
| 128 | IPv6 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff |
1 个 | IPv6 中的单个主机地址（类似 IPv4 的 /32） |
注意：IPv4 地址共 32 位，因此前缀长度最大为 32；IPv6 地址共 128 位，前缀长度最大为 128。所以 128 只可能出现在 IPv6 语境中，表示掩码为全 1，即一个唯一的 IPv6 地址。
举例说明
- /24：IP 地址 192.168.1.0/24 表示网络部分为前 24 位（192.168.1），主机部分为后 8 位。
- /32：IP 地址 10.0.0.1/32 表示该地址被当作一台独立主机，没有其他主机在同一网段。
- /128：IPv6 地址 2001:db8::1/128 表示这是一个确定的单一 IPv6 地址。
如果问题中的“128”指的是子网掩码中的某个十进制数字（如 255.255.255.128），那对应的前缀长度是 25（而不是 128）。但根据常见网络术语，24、32、128 通常就是指 CIDR 前缀长度。
METHOD：客户端认证方式
- trust：只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。
- am-sha-256：密码认证，这是当前提供的方法中最安全的一种，但是旧的客户端库不支持这种方法。
- md5：是常用的密码认证方式，如果你不使用ident，最好使用md5。密码是以md5形式传送给数据库，较安全，且不需建立同名的操作系统用户。
- password：以明文密码传送给数据库，建议不要在生产环境中使用。
- ident：Linux下PostgreSQL默认的local认证方式，凡是能正确登录操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。操作系统名，数据库用户名，数据库名一致。
- reject：拒绝认证，这对于从组中“过滤掉”某些主机非常有用。
修改完pg_hba.conf文件之后，需要重新加载配置，不用重启数据库：
--系统命令行执行
pg_ctl reload
或
--连入数据库执行
select pg_reload_conf();
3）pg_ident.conf 文件
数据库映射文件，ident 认证方式的扩展，标注操作系统用户与数据库用户的映射关系，配合pg_hba.conf 使用。
允许数据库服务器上指定的操作系统用户，使用指定的数据库用户，免密连入数据库。
pg_ident.conf 文件
# MAPNAME SYSTEM-USERNAME PG-USERNAMEss aaa testss syd sydpg_hba.conf 文件
# TYPE DATABASE USER ADDRESS METHODlocal all all ident map=sshost all all 127.0.0.1/32 md5host all all 0.0.0.0/0 md5host all all ::1/128 md5local replication all md5host replication all 127.0.0.1/32 md5host replication all ::1/128 md5MAPNAME：映射名，自定义配置在 pg_hba.conf 文件中。
SYSTEM-USERNAME：系统用户名。
PG-USERNAME ：数据库用户名。
eg：
--aaa 操作系统用户，使用 test 数据库用户，免密连入数据库。
[root@dj ~]# su - aaa
[aaa@dj ~]$ psql -U test
psql (12.4)
Type "help" for help.
test=> select user;
user
------
test
(1 row)--syd 操作系统用户，使用 syd 数据库用户，免密连入数据库
[root@dj ~]# su - syd
[syd@dj ~]$ psql -U syd
psql (12.4)
Type "help" for help.
syd=> select user;
user
------
syd
(1 row)
2.2 数据库权限
为已创建用户授权数据库操作权限。
grant {{create|connect|temporary|temp}|all[ privileges]} on database 数据库名 to 用户名|pubilc [with grant option];参数说明：
create：对于数据库，允许在数据库中创建新的schema、table、index。
connect：允许用户连接到指定的数据库。在连接启动时检查此权限。
temporary、temp：允许在使用指定数据库时创建临时表。
all privileges：一次授予所有可用权限。
public：所有用户。eg:
-- 授予连接数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
-- 允许用户在数据库中创建模式
GRANT CREATE ON DATABASE mydb TO app_user;2.3 模式权限
为已创建用户授权模式操作权限。
grant {{create|usage}|all [privileges]} on schema 模式名 to 用户名|pubilc [with grant option];参数说明：
create：对于schema，允许在模式中创建新的 table、index等。
usage：对于schema，允许访问指定模式中包含的对象；对于 sequence，允许使用 currval 和nextval 函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
all privileges：一次授予所有可用权限。
public：所有用户。eg：
-- 授予模式访问权限
GRANT USAGE ON SCHEMA public TO app_user;
-- 允许在模式中创建对象
GRANT CREATE ON SCHEMA public TO app_user;2.4 对象权限
为已创建用户授权基本对象操作权限
grant {{select|insert|update|delete|truncate|references|trigger}|all [privileges]} on {[table] 表名|all tables in schema 模式名} to 用户名|pubilc [with grant option];参数说明：
select：允许从指定表，视图或序列的任何列或列出的特定列进行 select。也允许使用 copy to。在 update 或 delete 中引用现有列值也需要此权限。对于序列，此权限还允许使用 currval函数。对于大对象，此权限允许读取对象。
insert：允许将新行 insert 到指定的表中。如果列出了特定列，则只能在 insert 命令中为这些列分配（因此其他列将接收默认值）。也允许 copy from。
update：允许更新指定表的任何列或列出的特定列，需要 select 权限。 （可指定列，如 UPDATE (col1, col2)）。
delete：允许删除指定表中的行，需要 select 权限。
truncate：允许清空指定表。
references：允许创建引用指定表或表的指定列的外键约束。
trigger：允许在指定的表上创建触发器。
execute：允许使用指定的函数或过程以及在函数。
all privileges：一次授予所有可用权限。
public：所有用户。eg
-- 授予表读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON table employees TO app_user;-- 仅允许更新特定列
GRANT UPDATE (salary) ON table employees TO app_user;-- 允许在表上创建外键
GRANT REFERENCES (id) ON table employees TO app_user;-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE user_id_seq TO app_user;-- 函数（Function）权限
EXECUTE：允许调用函数。
GRANT EXECUTE ON FUNCTION calculate_salary() TO app_user;2.5 系统权限
创建用户时携带属性，创建后也可使用 alter 进行修改。create user 是 create role 的一个别名。唯一的区别是 create user 中 login 被作为默认值，而 create role 中 nologin 是默认值。
create user/role 用户名|public [with] option;
alter user/role 用户名|public [with] option;
option 可以为
superuser|nosuperuser：超级权限，拥有所有权限，默认 nosuperuser。
inherit|noinherit：继承权限，可以把除superuser权限继承给其他用户/角色，默认 inherit。
login|nologin：登录权限，作为连接的用户，默认 nologin，除非是 create user（默认登录）。
password ‘password’|null：设置密码，密码仅用于有 login 属性的用户，不使用密码身份验证，则可以省略此选项，可以选择将空密码显式写为password null。
valid until ‘timestamp’：密码有效期时间，不设置则用不失效。
createdb|nocreatedb：建库权限，默认 nocreatedb。
createrole|nocreaterole：建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
replication|noreplication：复制权限，用于物理或则逻辑复制（复制和删除 slots），默认是 noreplication。
bypassrls|nobypassrls：安全策略rls权限，默认nobypassrls。
connection limit connlimit：限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
public：所有用户。
三、权限查看
3.1 权限查看
--数据库权限查看
postgres=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------appdb | appuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| | | | | postgres=CTc/postgres+| | | | | u1=c/postgrespostgres | 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/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres
(5 rows)--模式权限查看
postgres=# \dn+List of schemasName | Owner | Access privileges | Description
--------+----------+----------------------+------------------------public | postgres | postgres=UC/postgres+| standard public schema| | =UC/postgres |sch1 | postgres | |
(2 rows)--对象权限查看
d1=> select * from information_schema.table_privileges where grantee = 'u1';grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------u1 | u1 | d1 | d1s1 | t1 | INSERT | YES | NOu1 | u1 | d1 | d1s1 | t1 | SELECT | YES | YESu1 | u1 | d1 | d1s1 | t1 | UPDATE | YES | NOu1 | u1 | d1 | d1s1 | t1 | DELETE | YES | NOu1 | u1 | d1 | d1s1 | t1 | TRUNCATE | YES | NOu1 | u1 | d1 | d1s1 | t1 | REFERENCES | YES | NOu1 | u1 | d1 | d1s1 | t1 | TRIGGER | YES | NO
(7 rows)--系统权限查看
d1=> select * from pg_roles;rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373syd | f | t | f | f | t | f | -1 | ******** | | f | | 16387u1 | f | t | f | f | t | f | -1 | ******** | | f | | 41008pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377u2 | f | t | f | f | t | f | -1 | ******** | | f | | 41009repmgr | t | t | f | f | t | f | -1 | ******** | | f | | 32769
(13 rows)补充默认角色
PostgreSQL提供了一组默认角色，它们提供对特定的、通常需要的、需要特权的功能和信息的访问。管理员可以把这些角色GRANT给其环境中的用户或者其他角色，让这些用户能够访问指定的功能和信息。在授予这些角色时应当非常小心，以确保它们只被用在需要的地方。
角色 允许的访问
pg_read_all_settings 读取所有配置变量，甚至是那些通常只对超级用户可见的变量。
pg_read_all_stats 读取所有的 pg_stat_* 视图并且使用与扩展相关的各种统计信息，甚至是那些通常只对超级用户可见的信息。
pg_stat_scan_tables 执行可能会在表上取得ACCESS SHARE锁的监控函数（可能会持锁很长时间）。
pg_monitor 读取/执行各种不同的监控视图和函数。这角色是pg_read_all_settings，pg_read_all_stats和pg_stat_scan_tables的成员。
pg_signal_backend 发信号到其他后端以取消查询或中止它的会话。
pg_read_server_files 允许使用COPY以及其他文件访问函数从服务器上该数据库可访问的任意位置读取文件。
pg_write_server_files 允许使用COPY以及其他文件访问函数在服务器上该数据库可访问的任意位置中写入文件。
pg_execute_server_program 允许用运行该数据库的用户执行数据库服务器上的程序来配合COPY和其他允许执行服务器端程序的函数。
3.2 ACL 格式说明
上述数据库及模式权限均显示为 ACL（访问控制列表）缩写，显示格式为：
接受赋权的用户（省略时表示 PUBLIC 角色组）= 授予的权限/授予的用户
授予的权限具体含义如下：
权限 缩写 适用对象类型
SELECT r LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT a TABLE, table column
UPDATE w LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE d TABLE
TRUNCATE D TABLE
REFERENCES x TABLE, table column
TRIGGER t TABLE
CREATE C DATABASE, SCHEMA, TABLESPACE
CONNECT c DATABASE
TEMPORARY T DATABASE
EXECUTE X FUNCTION, PROCEDURE
USAGE U DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
访问权限摘要：
对象类型 所有权限 默认 PUBLIC 权限 psql 命令
DATABASE CTc Tc \l
DOMAIN U U \dD+
FUNCTION or PROCEDURE X X \df+
FOREIGN DATA WRAPPER U none \dew+
FOREIGN SERVER U none \des+
LANGUAGE U U \dL+
LARGE OBJECT rw none none
SCHEMA UC none \dn+
SEQUENCE rwU none \dp
TABLE (and table-like objects) arwdDxt none \dp
Table column arwx none \dp
TABLESPACE C none \db+
TYPE U U \dT+
四、撤销权限
上述所有通过 grant 方式授予的权限，均可通过列语法撤销。
revoke [grant option for] {上述所有权限} from {用户名|public} [cascade|restrict];public：所有用户。
cascade：级联回收权限。
restrict：不级联回收权限。
五、默认权限修改
5.1 概述
ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权（它不会影响分配给已经存在的对象的特权）。当前，只能修改用于模式、表（包括视图和外部表）、序列、函数和类型（包括域）的特权。其中，可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时，单词 FUNCTIONS 和 ROUTINES 是等效的。（推荐使用 ROUTINES，因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中，只允许单词 FUNCTIONS。无法为函数或过程单独设置默认特权。）只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置（即对当前数据库中创建的所有对象），或者只对在指定模式中创建的对象设置。
5.2 语法
ALTER DEFAULT PRIVILEGES[ FOR { ROLE | USER } target_role [, ...] ][ IN SCHEMA schema_name [, ...] ]abbreviated_grant_or_revoke其中abbreviated_grant_or_revoke是下列之一：GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON TABLESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON SEQUENCESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTIONS | ROUTINES }TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON TYPESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMASTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON TABLESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON SEQUENCESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTIONS | ROUTINES }FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON TYPESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMASFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]参数说明：
target_role：一个现有角色的名称，当前角色是它的一个成员。如果 FOR ROLE 被忽略，将假定为当前角色。
schema_name：一个现有模式的名称。如果被指定，以后在那个模式中创建的对象的默认特权会被修改。如果 IN SCHEMA 被忽略，全局默认特权会被修改。 当设置特权给模式时不能使用 IN SCHEMA，因为模式不能嵌套。
role_name：要为其授予或者收回特权的一个现有角色的名称。这个参数以及所有 abbreviated_grant_or_revoke 中的其他参数会按照 GRANT 或者 REVOKE 中描述的方式运作，不过这里是为一整类的对象而不是特别指定的对象设置权限。六、示例
6.1 创建只读用户
创建 readonly 用户，只可查询 test 模式下的表，连入对应数据库执行如下操作。
--创建只读用户
create user readonly with password 'postgres';--将 schema 中 usage 权限赋予给 readonly 用户,访问所有已存在的表
grant usage on schema test to readonly;
grant select on all tables in schema test to readonly;--未来访问 test 模式下所有新建的表
alter default privileges in schema test grant select on tables to readonly ;
6.2 删除只读用户
修改过默认特权的用户要进行删除，均先删除修改的默认特权后在进行删除用户操作
--删除授予的默认特权
drop owned by readonly;--删除用户
drop user readonly;
四、特殊权限与默认设置
-
- PUBLIC 角色
PUBLIC 代表所有角色，对 PUBLIC 授权等同于对所有用户授权。
- PUBLIC 角色
-- 允许所有用户查询 public 模式下的表
GRANT SELECT ON ALL TABLES IN SCHEMA public TO PUBLIC;
-
- 所有权（Ownership）
对象创建者自动成为所有者，可通过 ALTER OWNER 更改：
ALTER TABLE employees OWNER TO group_developer;
-
- 默认权限（Default Privileges）
设置新创建对象的默认权限（需在会话或数据库级别配置）：
- 默认权限（Default Privileges）
-- 对未来创建的表，授予 group_developer 读写权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO group_developer;
五、权限检查流程
- 超级用户：绕过所有权限检查，可访问任何对象。
- 普通角色：
- 检查是否拥有对象的对应权限（如 SELECT）。
- 若角色通过继承（GRANT role TO role）获得权限，需确保父角色拥有该权限。
- 外键约束检查：创建外键时，需拥有引用表的 REFERENCES 权限。
六、最佳实践
- 最小权限原则：仅授予角色必要的权限（如只读角色不授予 INSERT）。
- 使用角色组：通过创建组角色（如 dev_group、report_group）批量管理权限，避免重复授权。
- 分离开发与生产环境：生产环境禁用 CREATE、TRUNCATE 等高危权限。
- 定期审查权限：通过 \z 或系统视图检查异常权限配置。
- 避免 PUBLIC 权限：谨慎对 PUBLIC 授权，防止未授权访问。
PostgreSQL 14引入了两个新角色，从应用程序开发人员的角度提供了显著的好处：
pg_read_all_data
pg_write_all_data
例如，当向应用程序用户提供只读权限时，通常的方法包括为每个模式执行多个命令：
GRANT SELECT on all tables in schema1 to application_user;
GRANT SELECT on all tables in schema2 to application_user;
ALTER DEFAULT PRIVILEGES in schema1 GRANT select on tables to application_user;
ALTER DEFAULT PRIVILEGES in schema2 GRANT select on tables to application_user;
问题是，当处理大量的模式和编写用户时，这个过程会变得重复。
对于像pg_read_all_data这样的预定义角色，我们只需要运行一个命令：
GRANT pg_read_all_data to application_user;
建议在PostgreSQL中设置细粒度访问控制的方法如下：
- 1.为每个应用程序或服务创建单独的用户。这有助于轻松识别任何连接问题，并有助于通过审核日志跟踪访问。
- 2.根据应用程序访问要求定义具有特定权限的多个角色。相应地将这些角色分配给用户，因为这有助于简化管理并简化授予或撤销权限的过程。
- 3.仅向用户和角色授予其各自任务所需的权限。
- 4.要小心超级用户权限，因为他们拥有对数据库的不受限制的访问权限，如果被泄露，会带来重大的安全风险。
- 5.为数据库用户实施强大的密码策略，以防止未经授权的访问。定期轮换密码。
- 6.利用SSL/TLS协议对客户端和数据库服务器之间的通信进行加密。
eg:
- 创建角色和账号
-- 创建业务角色（用于权限管理）
CREATE ROLE app_role;-- 创建程序账号（禁止直接登录，通过角色继承权限）
CREATE USER app_user WITH PASSWORD 'your_secure_password';
GRANT app_role TO app_user;
- 设置默认权限（关键步骤）
假设所有业务对象由 app_owner 用户创建（推荐集中管理）：
-- 以对象创建者身份执行（如 app_owner）
ALTER DEFAULT PRIVILEGES FOR ROLE app_ownerGRANT ALL ON TABLES TO app_role; -- 表和视图ALTER DEFAULT PRIVILEGES FOR ROLE app_ownerGRANT ALL ON FUNCTIONS TO app_role; -- 函数ALTER DEFAULT PRIVILEGES FOR ROLE app_ownerGRANT ALL ON SEQUENCES TO app_role; -- 序列（如自增ID）ALTER DEFAULT PRIVILEGES FOR ROLE app_ownerGRANT ALL ON TYPES TO app_role; -- 自定义类型
- 处理已有对象（可选）
如果已有历史对象需要授权：
-- 对现有表/视图授权
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_role;
-- 对现有函数授权
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO app_role;
⚠️ 注意事项
-
1.默认权限仅对指定创建者生效
如果未来对象由其他用户（如 postgres 或随机用户）创建，ALTER DEFAULT PRIVILEGES 不会生效。建议统一用 app_owner 创建对象。 -
2.权限范围
ALL PRIVILEGES 包括 SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER（表）和 EXECUTE（函数）。
如需更细粒度权限，可替换为具体权限（如 SELECT, INSERT）。 -
3.Schema 权限
如果程序账号需要在特定 Schema 下创建对象，需额外授权：
GRANT USAGE, CREATE ON SCHEMA your_schema TO app_role;
通过以上配置，程序账号 app_user 将自动继承所有未来对象的完整权限，无需手动干预。
总结
PostgreSQL 的权限体系通过 角色 和 对象权限 实现细粒度控制，支持复杂的权限继承和默认策略。合理设计角色层级和权限分配，可有效提升数据库安全性和管理效率。如需进一步优化，可结合数据库行级安全（RLS）或扩展模块（如 pgaudit）实现更高级的访问控制。