pg18权限篇章
一，权限介绍
1. 通用语法结构
sql
GRANT { {权限1, 权限2, ...} | ALL [PRIVILEGES] } ON [对象类型] 对象名 TO 角色 [WITH GRANT OPTION];权限：指定要授予的一个或多个权限，用逗号分隔。
ALL [PRIVILEGES]：一次性授予该对象类型支持的所有权限。
对象类型：某些对象类型在
ON后面需要明确写出（如DATABASE、SCHEMA等），有些可以省略（如表、视图默认为TABLE）。WITH GRANT OPTION：允许被授予者将获得的权限再转授给其他人。
2. 各对象类型的详细 GRANT
🔹 表、视图、物化视图（TABLE, VIEW, MATERIALIZED VIEW）
适用权限：
SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,MAINTAIN语法：
sql
GRANT {权限列表 | ALL} ON TABLE 表名 TO 角色; -- TABLE 关键字可以省略（默认就是表）示例：
sql
GRANT SELECT, INSERT ON employees TO joe; GRANT ALL ON active_customers TO PUBLIC;
🔹 列级别（COLUMN）
适用权限：
SELECT,INSERT,UPDATE,REFERENCES（只能针对特定列）语法：
sql
GRANT {权限 (列1, 列2, ...) | ALL (列1, 列2, ...)} ON TABLE 表名 TO 角色;示例：
sql
GRANT UPDATE (salary, bonus) ON employees TO hr_clerk; GRANT SELECT (name, email) ON customers TO support_team;
🔹 序列（SEQUENCE）
适用权限：
USAGE,SELECT,UPDATE语法：
sql
GRANT {权限列表 | ALL} ON SEQUENCE 序列名 TO 角色;示例：
sql
GRANT USAGE ON order_id_seq TO joe;
🔹 数据库（DATABASE）
适用权限：
CONNECT,CREATE,TEMPORARY（别名TEMP）语法：
sql
GRANT {权限列表 | ALL} ON DATABASE 数据库名 TO 角色;示例：
sql
GRANT CONNECT, TEMPORARY ON DATABASE mydb TO joe;
🔹 模式（SCHEMA）
适用权限：
CREATE,USAGE语法：
sql
GRANT {权限列表 | ALL} ON SCHEMA 模式名 TO 角色;示例：
sql
GRANT CREATE, USAGE ON SCHEMA public TO joe;
🔹 函数 / 存储过程（FUNCTION / PROCEDURE）
适用权限：
EXECUTE语法：
sql
GRANT {EXECUTE | ALL} ON FUNCTION 函数名(参数类型列表) TO 角色; GRANT {EXECUTE | ALL} ON PROCEDURE 存储过程名(参数类型列表) TO 角色;示例：
sql
GRANT EXECUTE ON FUNCTION calculate_tax(numeric) TO joe;
🔹 语言（LANGUAGE）
适用权限：
USAGE语法：
sql
GRANT {USAGE | ALL} ON LANGUAGE 语言名 TO 角色;示例：
sql
GRANT USAGE ON LANGUAGE plpgsql TO joe;
🔹 数据类型 / 域（TYPE / DOMAIN）
适用权限：
USAGE语法：
sql
GRANT {USAGE | ALL} ON TYPE 类型名 TO 角色; GRANT {USAGE | ALL} ON DOMAIN 域名 TO 角色;示例：
sql
GRANT USAGE ON TYPE address_type TO joe;
🔹 外部数据包装器（FOREIGN DATA WRAPPER）
适用权限：
USAGE语法：
sql
GRANT {USAGE | ALL} ON FOREIGN DATA WRAPPER 包装器名 TO 角色;示例：
sql
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO joe;
🔹 外部服务器（FOREIGN SERVER）
适用权限：
USAGE语法：
sql
GRANT {USAGE | ALL} ON FOREIGN SERVER 服务器名 TO 角色;示例：
sql
GRANT USAGE ON FOREIGN SERVER remote_server TO joe;
🔹 大对象（LARGE OBJECT）
适用权限：
SELECT,UPDATE语法：
sql
GRANT {权限列表 | ALL} ON LARGE OBJECT 大对象OID TO 角色;示例：
sql
GRANT SELECT ON LARGE OBJECT 12345 TO joe;
🔹 表空间（TABLESPACE）
适用权限：
CREATE语法：
sql
GRANT {CREATE | ALL} ON TABLESPACE 表空间名 TO 角色;示例：
sql
GRANT CREATE ON TABLESPACE fast_space TO joe;
🔹 配置参数（PARAMETER）
适用权限：
SET,ALTER SYSTEM语法：
sql
GRANT {权限列表 | ALL} ON PARAMETER 参数名 TO 角色;示例：
sql
GRANT SET ON PARAMETER work_mem TO joe; GRANT ALTER SYSTEM ON PARAMETER shared_buffers TO admin;
3. 其他说明
PUBLIC角色：表示所有当前及未来的用户。sql
GRANT SELECT ON employees TO PUBLIC;
WITH GRANT OPTION：允许被授予者转授权限。sql
GRANT SELECT ON employees TO joe WITH GRANT OPTION;
ALL [PRIVILEGES]：一次性授予所有适用权限，但不包含WITH GRANT OPTION，需要单独加。多个对象：可以一次授予多个对象（同类型），用逗号分隔：
sql
GRANT SELECT ON table1, table2, table3 TO joe;
4. 收回权限（REVOKE）
对应的收回命令语法类似，只需将GRANT换成REVOKE，并且可能需要FROM而不是TO，以及处理级联回收等选项。
sql
REVOKE {权限列表 | ALL} ON [对象类型] 对象名 FROM 角色;示例：
sql
REVOKE UPDATE ON employees FROM joe; REVOKE ALL ON DATABASE mydb FROM PUBLIC;
二，权限应用
1，创建只读用户
--创建doki_database CREATE DATABASE doki_database ; -- 创建用户doki CREATE USER doki PASSWORD 'password'; -- 更新用户的默认事务为只读的（这意味着它只能执行读操作，不能CUD） ALTER USER doki set default_transaction_read_only=on; -- 授权连接 GRANT CONNECT ON DATABASE doki_database TO doki; -- 授权public的使用 GRANT USAGE ON SCHEMA PUBLIC TO doki; -- 切换到doki_database \c doki_database -- 授予doki_database下public下所有表、序列、函数 GRANT SELECT ON ALL TABLES IN SCHEMA public TO doki; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO doki; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO doki; -- 对于新建的对象，也授予上面三个权限,sql命令执行后的新表起作用，旧表不起作用 --ALTER DEFAULT PRIVILEGES -- [ FOR { ROLE | USER } target_role [, ...] ] -- [ IN SCHEMA schema_name [, ...] ] -- abbreviated_grant_or_revoke; --授权grant ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO doki; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO doki; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO doki; --切用户postgres \c doki_database postgres; create table lga(name varchar(10)); insert into lga values('xxxx'); --切用户 doki \c doki_database doki; --验证 select * from lga; delete from lga; drop table lga; create table t1(name varchar(10)); --回收revoke -- 回收在 public 模式中未来表的 SELECT 权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM doki; -- 回收在 public 模式中未来序列的 SELECT 权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON SEQUENCES FROM doki; -- 回收在 public 模式中未来函数的 EXECUTE 权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM doki;