postgres 只读账号

只读账号

--创建readonly用户
postgres=> c postgres sa
You are now connected to database "postgres" as user "sa".
postgres=# create user readonly with password '123456';
CREATE ROLE
--设置用户默认事务只读
postgres=# alter user readonly set default_transaction_read_only = on;
ALTER ROLE
--赋予用户连接数据库权限
postgres=# grant connect  on database postgres to  readonly;
GRANT
--赋予schema，序列，表查看权限
postgres=# grant usage on schema public to readonly;
GRANT
postgres=# grant select on all sequences in schema public to readonly;
GRANT
postgres=# grant select on all tables in schema public to readonly;
GRANT
--设置未来新增表的查看权限
postgres=# alter default privileges in schema public grant select on tables to readonly;
ALTER DEFAULT PRIVILEGES