SQL

基础脚本 -- 创建用户 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; 这条命令只对执行时已存在的表生效。未来新建的表不会自动获得权限。 ...