基础脚本

-- 创建用户
CREATE USER readonly WITH PASSWORD 'TJJe5CvU';

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

-- 授予 schema 使用权限
GRANT USAGE ON SCHEMA public TO readonly;

-- 授予现有表查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 设置默认权限：未来新建的表也自动授予 SELECT
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

这个脚本能跑通，但有几个可以改进的地方。

生产环境强化版

-- ============================================
-- Create Read-Only User for PostgreSQL
-- ============================================

-- Step 1: Create login role (idempotent)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'readonly') THEN
        CREATE ROLE readonly WITH LOGIN PASSWORD 'TJJe5CvU';
        RAISE NOTICE 'Role [readonly] created.';
    ELSE
        RAISE NOTICE 'Role [readonly] already exists.';
    END IF;
END
$$;

-- Step 2: Grant CONNECT on target database
GRANT CONNECT ON DATABASE jgsteel_prm TO readonly;

-- Step 3: Switch to target database (run these inside the database)
-- \c jgsteel_prm

-- Step 4: Grant schema usage
GRANT USAGE ON SCHEMA public TO readonly;

-- Step 5: Grant SELECT on ALL existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Step 6: Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Step 7: (Optional) Grant SELECT on sequences if needed
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;

-- Step 8: (Optional) Revoke write permissions explicitly
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly;

关键点解读

1. ALTER DEFAULT PRIVILEGES — 最容易遗漏的一步

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

这条命令只对执行时已存在的表生效。未来新建的表不会自动获得权限。

ALTER DEFAULT PRIVILEGES 解决了这个问题 — 之后在 public schema 中新建的任何表，都会自动授予 readonly 用户 SELECT 权限。

2. 权限层级关系

PostgreSQL 的权限是分层级的，缺一不可：

CONNECT on DATABASE   →  允许连接数据库
      ↓
USAGE on SCHEMA       →  允许查看 schema 中的对象
      ↓
SELECT on TABLES      →  允许查询表数据

3. 为什么不需要 DENY

PostgreSQL 没有像 SQL Server 那样的 DENY 语句。权限默认就是"最小权限原则" — 只授予你明确需要的权限，未授予的操作默认拒绝。

验证权限

-- 查看用户拥有的角色和属性
\du readonly

-- 查看具体权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'readonly';

-- 测试连接和查询
-- psql -U readonly -d jgsteel_prm -c "SELECT current_user, now();"
-- psql -U readonly -d jgsteel_prm -c "SELECT * FROM your_table LIMIT 5;"
-- psql -U readonly -d jgsteel_prm -c "DELETE FROM your_table;"  -- 应该报错

连接字符串示例

# JDBC
jdbc:postgresql://192.168.1.100:5432/jgsteel_prm?user=readonly&password=TJJe5CvU

# psql CLI
psql -h 192.168.1.100 -p 5432 -U readonly -d jgsteel_prm

# pgAdmin / DBeaver
Host: 192.168.1.100
Port: 5432
Database: jgsteel_prm
Username: readonly
Password: TJJe5CvU

与 SQL Server 的对比

操作 SQL Server PostgreSQL
创建登录 CREATE LOGIN CREATE ROLE ... WITH LOGIN
创建用户 CREATE USER ... FOR LOGIN 角色即用户（无需额外创建）
Schema 权限 GRANT SELECT ON SCHEMA::dbo GRANT USAGE ON SCHEMA public + GRANT SELECT ON ALL TABLES
默认权限 无原生支持 ALTER DEFAULT PRIVILEGES
显式拒绝 DENY INSERT 不支持（最小权限原则）

Professional English Terms

Term Meaning
ALTER DEFAULT PRIVILEGES 设置默认权限，确保未来创建的对象自动继承权限
Schema USAGE Schema 使用权限，允许查看和访问 schema 中的对象
Role / Login Role PostgreSQL 中的角色概念，CREATE ROLE ... WITH LOGIN 等价于创建用户
Idempotent 幂等性，用 DO $$ ... IF NOT EXISTS 确保脚本可重复执行
pg_hba.conf PostgreSQL 客户端认证配置文件，控制允许哪些 IP 连接