PostgreSQL 权限授予与回收

权限层级体系

PostgreSQL的权限体系分为多个层级，从高到低依次为：

全局权限：数据库集群级别的权限（如CREATEDB、CREATEROLE、SUPERUSER等）
数据库级权限：单个数据库的权限（如CONNECT、CREATE等）
Schema级权限：Schema的权限（如USAGE、CREATE等）
对象级权限：表、视图、函数等对象的权限（如SELECT、INSERT、UPDATE、DELETE等）
列级权限：表中特定列的权限

权限授予（GRANT）

1. 数据库级权限授予

sql

-- 授予连接数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;

-- 授予创建数据库权限（全局权限）
GRANT CREATEDB TO db_creator;

-- 授予所有数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO db_admin;

2. Schema级权限授予

sql

-- 授予使用Schema权限
GRANT USAGE ON SCHEMA public TO app_user;

-- 授予创建对象权限
GRANT CREATE ON SCHEMA public TO app_developer;

-- 授予所有Schema权限
GRANT ALL PRIVILEGES ON SCHEMA public TO db_admin;

3. 表级权限授予

sql

-- 授予表的SELECT权限
GRANT SELECT ON TABLE mytable TO app_user;

-- 授予表的多个权限
GRANT INSERT, UPDATE, DELETE ON TABLE mytable TO app_writer;

-- 授予所有表权限
GRANT ALL PRIVILEGES ON TABLE mytable TO db_admin;

-- 授予Schema中所有现有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;

4. 列级权限授予

sql

-- 授予表中特定列的权限
GRANT SELECT (id, name, email), UPDATE (name, email) ON TABLE users TO app_user;

5. 函数和存储过程权限授予

sql

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION my_function(INTEGER) TO app_user;

-- 授予Schema中所有函数的执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

6. 默认权限设置

sql

-- 设置未来创建的表自动授予SELECT权限
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_reader;

-- 设置未来创建的函数自动授予EXECUTE权限
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO app_user;

权限回收（REVOKE）

1. 基本回收语法

sql

-- 回收表的DELETE权限
REVOKE DELETE ON TABLE mytable FROM app_user;

-- 回收多个权限
REVOKE INSERT, UPDATE ON TABLE mytable FROM app_user;

-- 回收所有权限
REVOKE ALL PRIVILEGES ON TABLE mytable FROM app_user;

-- 回收Schema中所有表的SELECT权限
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM app_user;

2. 回收继承权限

sql

-- 回收角色继承
REVOKE app_role FROM app_user;

-- 注意：回收角色不会自动回收该角色已授予的权限
-- 需要单独回收具体权限

3. 级联回收

sql

-- 级联回收权限
REVOKE ALL PRIVILEGES ON TABLE mytable FROM app_user CASCADE;

角色权限管理

1. 授予角色权限

sql

-- 创建角色
CREATE ROLE read_role NOLOGIN;

-- 授予角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;

-- 将角色授予用户
CREATE ROLE app_user LOGIN PASSWORD 'password';
GRANT read_role TO app_user;

2. 回收角色权限

sql

-- 回收角色的特定权限
REVOKE SELECT ON TABLE sensitive_table FROM read_role;

-- 回收用户的角色
REVOKE read_role FROM app_user;

权限管理最佳实践

1. 基于角色的权限管理

sql

-- 创建功能角色
CREATE ROLE readonly NOLOGIN;
CREATE ROLE readwrite NOLOGIN;
CREATE ROLE admin NOLOGIN;

-- 授予角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin;

-- 授予默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;
  GRANT INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- 创建用户并分配角色
CREATE ROLE app_user1 LOGIN PASSWORD 'pass1';
CREATE ROLE app_user2 LOGIN PASSWORD 'pass2';
CREATE ROLE app_admin LOGIN PASSWORD 'adminpass';

GRANT readonly TO app_user1;
GRANT readwrite TO app_user2;
GRANT admin TO app_admin;

2. 最小权限原则

sql

-- 只授予必要的权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT (id, name) ON TABLE users TO app_user;

3. 定期权限审计

sql

-- 查看用户权限
SELECT 
  grantee,
  privilege_type,
  table_schema,
  table_name
FROM information_schema.role_table_grants
WHERE grantee = 'app_user';

-- 查看角色权限继承关系
SELECT 
  r.rolname AS role_name,
  m.rolname AS member_name
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
ORDER BY role_name, member_name;

常见权限管理场景

1. 只读用户配置

sql

-- 创建只读角色
CREATE ROLE readonly NOLOGIN;

-- 授予只读权限
GRANT CONNECT ON DATABASE production_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

-- 创建只读用户
CREATE ROLE report_user LOGIN PASSWORD 'report_pass';
GRANT readonly TO report_user;

2. 开发人员权限配置

sql

-- 创建开发角色
CREATE ROLE developer NOLOGIN;

-- 授予开发权限
GRANT CONNECT ON DATABASE dev_db TO developer;
GRANT USAGE, CREATE ON SCHEMA public TO developer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer;
  GRANT EXECUTE ON FUNCTIONS TO developer;

-- 创建开发用户
CREATE ROLE dev1 LOGIN PASSWORD 'dev_pass1';
GRANT developer TO dev1;

3. 管理角色配置

sql

-- 创建管理角色
CREATE ROLE db_admin LOGIN PASSWORD 'admin_pass';

-- 授予管理权限
GRANT CREATEDB, CREATEROLE TO db_admin;
GRANT ALL PRIVILEGES ON DATABASE mydb TO db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;

权限故障排查

1. 权限不足问题

问题：用户执行操作时提示权限不足

解决方案：

sql

-- 检查用户权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'app_user';

-- 检查角色继承关系
SELECT * FROM pg_auth_members WHERE member = (SELECT oid FROM pg_roles WHERE rolname = 'app_user');

-- 授予缺失的权限
GRANT INSERT ON TABLE mytable TO app_user;

2. 默认权限不生效

问题：新创建的表没有自动获得权限

解决方案：

sql

-- 检查默认权限设置
SELECT * FROM pg_default_acl;

-- 重新设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_reader;

3. 权限冲突

问题：用户同时属于多个角色，权限冲突

解决方案：

sql

-- 检查用户所属角色
SELECT r.rolname FROM pg_roles r JOIN pg_auth_members am ON r.oid = am.roleid WHERE am.member = (SELECT oid FROM pg_roles WHERE rolname = 'app_user');

-- 调整角色权限或用户所属角色
REVOKE conflicting_role FROM app_user;

常见问题（FAQ）

Q1：GRANT和REVOKE的基本语法是什么？

A1：基本语法如下：

sql

-- GRANT语法
GRANT [权限类型] ON [对象类型] [对象名] TO [角色/用户];

-- REVOKE语法
REVOKE [权限类型] ON [对象类型] [对象名] FROM [角色/用户];

Q2：如何查看用户拥有的所有权限？

A2：可以通过以下查询查看：

sql

-- 查看表级权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- 查看数据库级权限
SELECT * FROM pg_database WHERE datname = 'dbname';

-- 查看角色属性
SELECT * FROM pg_roles WHERE rolname = 'username';

Q3：如何授予用户创建表的权限？

A3：需要授予CREATE和USAGE权限：

sql

-- 授予Schema的USAGE和CREATE权限
GRANT USAGE, CREATE ON SCHEMA public TO username;

Q4：如何撤销用户的所有权限？

A4：可以使用以下命令：

sql

-- 撤销表权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;

-- 撤销Schema权限
REVOKE ALL PRIVILEGES ON SCHEMA public FROM username;

-- 撤销数据库权限
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

Q5：默认权限（ALTER DEFAULT PRIVILEGES）有什么作用？

A5：默认权限用于为未来创建的对象自动授予权限，避免每次创建新对象都需要手动授权。

Q6：如何授予用户执行所有函数的权限？

A6：可以使用以下命令：

sql

-- 授予现有函数执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO username;

-- 授予未来创建函数的执行权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO username;

Q7：权限继承是如何工作的？

A7：当用户被授予角色时，默认会继承该角色的所有权限。可以通过设置INHERIT或NOINHERIT属性控制继承行为。

Q8：如何查看角色的权限继承关系？

A8：可以使用以下查询：

sql

SELECT r.rolname AS role_name, m.rolname AS member_name 
FROM pg_auth_members am 
JOIN pg_roles r ON am.roleid = r.oid 
JOIN pg_roles m ON am.member = m.oid 
ORDER BY role_name, member_name;

PostgreSQL 权限授予与回收 ​

权限层级体系 ​

权限授予（GRANT） ​

1. 数据库级权限授予 ​

2. Schema级权限授予 ​

3. 表级权限授予 ​

4. 列级权限授予 ​

5. 函数和存储过程权限授予 ​

6. 默认权限设置 ​

权限回收（REVOKE） ​

1. 基本回收语法 ​

2. 回收继承权限 ​

3. 级联回收 ​

角色权限管理 ​

1. 授予角色权限 ​

2. 回收角色权限 ​

权限管理最佳实践 ​

1. 基于角色的权限管理 ​

2. 最小权限原则 ​

3. 定期权限审计 ​

常见权限管理场景 ​

1. 只读用户配置 ​

2. 开发人员权限配置 ​

3. 管理角色配置 ​

权限故障排查 ​

1. 权限不足问题 ​

2. 默认权限不生效 ​

3. 权限冲突 ​

常见问题（FAQ） ​

Q1：GRANT和REVOKE的基本语法是什么？ ​

Q2：如何查看用户拥有的所有权限？ ​

Q3：如何授予用户创建表的权限？ ​

Q4：如何撤销用户的所有权限？ ​

Q5：默认权限（ALTER DEFAULT PRIVILEGES）有什么作用？ ​

Q6：如何授予用户执行所有函数的权限？ ​

Q7：权限继承是如何工作的？ ​

Q8：如何查看角色的权限继承关系？ ​

PostgreSQL 权限授予与回收

权限层级体系

权限授予（GRANT）

1. 数据库级权限授予

2. Schema级权限授予

3. 表级权限授予

4. 列级权限授予

5. 函数和存储过程权限授予

6. 默认权限设置

权限回收（REVOKE）

1. 基本回收语法

2. 回收继承权限

3. 级联回收

角色权限管理

1. 授予角色权限

2. 回收角色权限

权限管理最佳实践

1. 基于角色的权限管理

2. 最小权限原则

3. 定期权限审计

常见权限管理场景

1. 只读用户配置

2. 开发人员权限配置

3. 管理角色配置

权限故障排查

1. 权限不足问题

2. 默认权限不生效

3. 权限冲突

常见问题（FAQ）

Q1：GRANT和REVOKE的基本语法是什么？

Q2：如何查看用户拥有的所有权限？

Q3：如何授予用户创建表的权限？

Q4：如何撤销用户的所有权限？

Q5：默认权限（ALTER DEFAULT PRIVILEGES）有什么作用？

Q6：如何授予用户执行所有函数的权限？

Q7：权限继承是如何工作的？

Q8：如何查看角色的权限继承关系？