PostgreSQL权限梳理整理

PostgreSQL Cluster (集群)
  │  
  ├── Role (角色)
  │      ├── User (用户角色 - 可登录)
  │      └── Group (组角色 - 不可登录)
  │
  └── Database (数据库)  
       │
       ├── Schema (模式)
       │     │
       │     ├── Table (表)
       │     ├── View (视图)
       │     ├── Function (函数)
       │     └── Sequence (序列)
       │
       └── public (默认schema)

1. Cluster (集群)

PostgreSQL实例的最顶层容器
一个集群可包含多个数据库
所有数据库共享同一套角色(Role)系统
配置文件：postgresql.conf, pg_hba.conf

2. Role (角色)

PostgreSQL中不区分用户和组，统一使用Role概念。

Role的两种类型：

User Role（用户角色）：具有LOGIN属性，可以连接数据库
Group Role（组角色）：不具有LOGIN属性，用于权限管理

-- 创建用户角色（可登录）
CREATE ROLE test_app WITH LOGIN PASSWORD 'test_app';
-- 创建组角色（不可登录）
CREATE ROLE readonly_group;
-- 将用户加入组
GRANT readonly_group TO test_app;

3. Database (数据库)

数据的逻辑隔离单元
每个数据库相互独立，不能跨库查询（除非使用dblink或postgres_fdw）
创建时自动包含三个默认schema：public, pg_catalog, information_schema

CREATE DATABASE test_app OWNER test_app ENCODING 'UTF8';

4. Schema (模式)

Schema是数据库内的命名空间，用于组织和隔离对象，其作用：

逻辑分组：将相关表、视图、函数组织在一起
权限隔离：不同schema可以有不同的访问控制
命名空间：不同schema可以有同名对象
多租户：每个租户一个schema

CREATE SCHEMA test_app AUTHORIZATION test_app;

5. public Schema

每个数据库自动创建的默认schema
所有用户默认对public有USAGE和CREATE权限（PostgreSQL 14之前）
PostgreSQL 15+：默认撤销了public的CREATE权限

6. Table (表)

表是实际存储数据的对象，必须属于某个schema。

-- 完整表名格式
database.schema.table
-- 示例
SELECT * FROM myapp_db.app_schema.users;

-- 使用search_path后可简化
SELECT * FROM users;

7.权限层级体系

PostgreSQL的权限是层级式的，需要逐层授权：

1. 集群级别 (Cluster Level)：角色属性 (SUPERUSER, CREATEDB, CREATEROLE等)

2. 数据库级别 (Database Level)： CONNECT, CREATE, TEMP

3. Schema级别 (Schema Level)：USAGE, CREATE

4. 对象级别 (Object Level)：SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER

8.权限传递规则

要访问表，必须具备：

数据库的CONNECT权限
Schema的USAGE权限
表的具体操作权限（SELECT/INSERT等）

9.实战

创建两个用户（testa、testb）和两个数据库（testa、testb），并配置以下权限：

testa用户
- 对 testa 库下的 testa schema：DML + 建表 + 查询
- 对 testb 库下的 testb schema：DML + 建表 + 查询
- 对 testb 库下的 public schema：DML + 建表 + 查询
testb用户
- 对 testb 库下的 testb schema：DML + 建表 + 查询

步骤1：创建用户

-- 以超级用户身份连接（如postgres）
-- psql -U postgres
-- 创建testa用户
CREATE USER testa WITH LOGIN PASSWORD 'testa_password_123';

-- 创建testb用户
CREATE USER testb WITH LOGIN PASSWORD 'testb_password_123';

-- 验证用户创建
-- 验证用户创建
SELECT 
  rolname AS role_name,
  rolcanlogin AS can_login,
  rolconnlimit AS connection_limit,
  rolcreatedb AS can_create_db,
  rolcreaterole AS can_create_role
FROM pg_roles
WHERE rolname IN ('testa', 'testb')
ORDER BY rolname;

步骤2：创建数据库

-- 创建testa数据库（所有者为testa）
CREATE DATABASE testa  OWNER testa;

-- 创建testb数据库（所有者为testb）
CREATE DATABASE testb OWNER testb;

-- 验证数据库创建
SELECT 
  datname AS database_name,
  pg_catalog.pg_get_userbyid(datdba) AS owner,
  pg_encoding_to_char(encoding) AS encoding,
  datcollate AS collate,
  datctype AS ctype
FROM pg_database
WHERE datname IN ('testa', 'testb')
ORDER BY datname;

步骤3：创建schema

-- 切换到testa数据库
-- psql命令：\c testa postgres
-- 或使用新连接：psql -U postgres -d testa
-- 创建testa schema（所有者为testa）
CREATE SCHEMA testa AUTHORIZATION testa;

-- 切换到testb数据库
-- psql命令：\c testb postgres
-- 或使用新连接：psql -U postgres -d testb

-- 创建testb schema（所有者为testb）
CREATE SCHEMA testb AUTHORIZATION testb;

-- 验证schema创建
SELECT 
  nspname AS schema_name,
  pg_catalog.pg_get_userbyid(nspowner) AS owner,
  obj_description(oid, 'pg_namespace') AS description
FROM pg_namespace
WHERE nspname IN ('testxxx', 'public')
ORDER BY nspname;

步骤4：授权testa用户

4.1 授权testa用户在testa数据库的权限

-- 切换到testa数据库
-- psql命令：\c testa postgres
-- 或使用新连接：psql -U postgres -d testa

-- 确保testa用户可以连接到testa数据库（作为owner已经有权限，但明确授予）
GRANT CONNECT ON DATABASE testa TO testa;

-- testa用户对自己的schema已经有完全权限（作为owner，但为了明确授予）
GRANT ALL PRIVILEGES ON SCHEMA testa TO testa;

-- 授予对schema中所有现有表的权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA testa TO testa;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA testa TO testa;

-- 设置默认权限（未来创建的对象自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA testa FOR ROLE testa GRANT ALL PRIVILEGES ON TABLES TO testa;
ALTER DEFAULT PRIVILEGES IN SCHEMA testa  FOR ROLE testa GRANT ALL PRIVILEGES ON SEQUENCES TO testa;

4.2 授权testa用户在testb数据库的权限

-- 切换到testb数据库
-- psql命令：\c testb postgres
-- 或使用新连接：psql -U postgres -d testb

-- 授予testa用户连接testb数据库的权限
GRANT CONNECT ON DATABASE testb TO testa;

-- 授予testa用户对testb schema的使用和创建权限
GRANT USAGE, CREATE ON SCHEMA testb TO testa;

-- 授予对schema中所有现有表的DML权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA testb TO testa;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA testb TO testa;

-- 设置默认权限（testb创建的新表，testa自动获得权限）
ALTER DEFAULT PRIVILEGES IN SCHEMA testb  FOR ROLE testb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testa;
ALTER DEFAULT PRIVILEGES IN SCHEMA testb  FOR ROLE testb GRANT USAGE, SELECT ON SEQUENCES TO testa;

-- 授予testa在testb schema中创建表的权限（testa自己创建的表）
ALTER DEFAULT PRIVILEGES IN SCHEMA testb  FOR ROLE testa GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testa;
ALTER DEFAULT PRIVILEGES IN SCHEMA testb   FOR ROLE testa GRANT USAGE, SELECT ON SEQUENCES TO testa;

-- 授予testa用户对public schema的使用和创建权限
GRANT USAGE, CREATE ON SCHEMA public TO testa;

-- 授予对public schema中所有现有表的DML权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO testa;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO testa;

-- 设置默认权限（public创建的新表，testa自动获得权限）
ALTER DEFAULT PRIVILEGES IN SCHEMA public  FOR ROLE postgres GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public  FOR ROLE testa  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testa;

步骤5：授权testb用户

-- 切换到testb数据库
-- psql命令：\c testb postgres
-- 或使用新连接：psql -U postgres -d testb

-- 确保testb用户可以连接到testb数据库（作为owner已经有权限，为了明确）
GRANT CONNECT ON DATABASE testb TO testb;

-- testb用户对自己的schema已经有完全权限（作为owner已经有权限，为了明确）
GRANT ALL PRIVILEGES ON SCHEMA testb TO testb;

-- 授予对schema中所有现有表的权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA testb TO testb;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA testb TO testb;

-- 设置默认权限（未来创建的对象自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA testb  FOR ROLE testb  GRANT ALL PRIVILEGES ON TABLES TO testb;
ALTER DEFAULT PRIVILEGES IN SCHEMA testb  FOR ROLE testb GRANT ALL PRIVILEGES ON SEQUENCES TO testb;