使用华为云postgreSQL(PG)增强版如何创建只读用户

1、创建一个用户名为test密码为******的用户

CREATE USER test WITH ENCRYPTED PASSWORD ‘******’;

2、更新用户默认为只读事务

alter user test set default_transaction_read_only=on;

3、把所有库的public的USAGE权限给到test

GRANT USAGE ON SCHEMA public to test;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test;

4、授予用户连接数据库权限(要进入到具体数据库操作在哪个db环境下执行就授予那个db的权限)

GRANT CONNECT ON DATABASE mytest to test;

5、切换到指定库

\c mytest

6、赋予用户表、序列查看权限：

GRANT USAGE ON SCHEMA public to test;

GRANT USAGE ON SCHEMA XXX to test;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO test;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO test;

7、赋予用户某个视图的查看权限

GRANT SELECT ON XXXX  TO  test;