pgsql创建读写、只读账号

1.创建只读账号

--- 创建用户并设置密码和给与连接权限
CREATE USER dendrite_reader WITH PASSWORD '4e20a7aa1514017e12a6';
GRANT CONNECT ON DATABASE dendrite TO dendrite_reader;

-- 授权 public schema
GRANT USAGE ON SCHEMA public TO dendrite_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dendrite_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dendrite_reader;

-- 授权 xxai schema
GRANT USAGE ON SCHEMA xxai TO dendrite_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA xxai TO dendrite_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA xxai GRANT SELECT ON TABLES TO dendrite_reader;

-- 授权 wallet schema
GRANT USAGE ON SCHEMA wallet TO dendrite_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA wallet TO dendrite_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA wallet GRANT SELECT ON TABLES TO dendrite_reader;

2.创建读写账号

-- 替换 your_db_name 为实际数据库名（如 dendrite）
-- 1. 创建用户（请修改密码）
---CREATE USER xxai1 WITH PASSWORD 'xxai1';

-- 2. 授予数据库级权限
--GRANT CONNECT, TEMPORARY ON DATABASE xxai TO xxai1;

-- 3. 授权现有对象 + 设置默认权限（支持 a, b, c 或更多）
DO
$$

DECLARE
target_schemas TEXT[] := ARRAY['public', 'xxai', 'public']; -- ← 在此添加/修改 schema 列表
s TEXT;
BEGIN
FOREACH s IN ARRAY target_schemas
LOOP
-- 3.1 Schema 权限
EXECUTE format('GRANT USAGE, CREATE ON SCHEMA %I TO xxai1', s);

-- 3.2 表权限
EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO xxai1', s);

-- 3.3 序列权限（关键！）
EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA %I TO xxai1', s);

-- 3.4 函数权限（推荐）
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO xxai1', s);

-- 3.5 自定义类型权限（推荐）
--- EXECUTE format('GRANT USAGE ON ALL TYPES IN SCHEMA %I TO xxai1', s);

-- 3.6 默认权限（对未来对象生效）
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON TABLES TO xxai1', s);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON SEQUENCES TO xxai1', s);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT EXECUTE ON FUNCTIONS TO xxai1', s);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON TYPES TO xxai1', s);
END LOOP;
END
$$
;

2.1修改属主

（1）修改schema库属主
ALTER SCHEMA public OWNER TO xxai1;
ALTER SCHEMA wallet OWNER TO xxai1;
ALTER SCHEMA xxai OWNER TO xxai1;
（2）给出修改表属主的sql
SELECT 'ALTER TABLE ' || quote_ident(tablename) || ' OWNER TO xxai1;' AS sql FROM pg_tables WHERE schemaname IN ('public', 'wallet', 'xxai');
（3）给出修改序列属主的sql
SELECT 'ALTER SEQUENCE ' || quote_ident(sequence_name) || ' OWNER TO xxai1;' AS sql FROM information_schema.sequences WHERE sequence_schema = IN ('public', 'wallet', 'xxai');
（4）给出修改函数属主的sql
SELECT 'ALTER FUNCTION ' ||
quote_ident(n.nspname) || '.' ||
quote_ident(p.proname) || '(' ||
pg_get_function_identity_arguments(p.oid) ||
') OWNER TO xxai1;' AS sql
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname IN ('public', 'wallet', 'xxai') -- 你的 schema 列表
AND pg_get_userbyid(p.proowner) != 'xxai1';

（4）执行给的alert语句

3.创建备份用户

-- 1. 创建专用备份用户（带强密码）
CREATE USER backup_user WITH PASSWORD 'YourStrong!Backup#Password2025';
-- 2. 允许连接到目标数据库（例如 dendrite）
GRANT CONNECT ON DATABASE dendrite TO backup_user;
-- 3. 授予全局只读权限（核心！）
GRANT pg_read_all_data TO backup_user;

4.测试权限

-- 测试 1: 创建表（含 SERIAL）
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);

-- 测试 2: 插入数据（触发序列）
INSERT INTO test_table (name) VALUES ('test');

-- 测试 3: 查询
SELECT * FROM test_table;

-- 测试 4: 使用函数（如 pgcrypto）
SELECT gen_random_uuid(); -- 需要 EXECUTE 权限

-- 清理（可选）
DROP TABLE test_table;