创建只读用户

以超级用户登录，连接到TOPDFM_DEV_V6数据库。

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建
CREATE ROLE reporter WITH LOGIN PASSWORD '123456' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

-- 授权
GRANT CONNECT ON DATABASE "TOPDFM_DEV_V6" TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reporter;

-- 为将来的表授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

删除只读用户

以超级用户登录，连接到postgres数据库。

1
2
3
REASSIGN OWNED BY reporter TO postgres;
DROP OWNED BY reporter;
DROP ROLE reporter;

Reference