greenplum 创建只读用户

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

postgres=# CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

2、用户只读事务

postgres=# alter user readonly set default_transaction_read_only=on;
ALTER ROLE

default_transaction_read_only 注解：

设置default_transaction_read_only为on，默认开启的事务为只读事务。

-- 当前用户：readonly 库：two_dw  default_transaction_read_only ： on
-- 所有会话以readonly 用户连接，自动进入read only的默认事务模式。
two_dw=> \c
You are now connected to database "two_dw" as user "readonly".
two_dw=> show default_transaction_read_only ; 
 default_transaction_read_only 
-------------------------------
 on
(1 row)
-- 创建t1 表失败，会话模式为read-only
two_dw=> create table t1 (id int);
ERROR:  transaction is read-only
-- 切换超级管理用户
two_dw=> \c postgres gpadmin
You are now connected to database "postgres" as user "gpadmin".
-- 关闭default_transaction_read_only 模式：off
postgres=# alter user readonly set default_transaction_read_only=off;
ALTER ROLE
postgres=> show default_transaction_read_only ; 
 default_transaction_read_only 
-------------------------------
 off
-- 创建read only 用户登录two_dw库
postgres=# \c two_dw readonly
Password for user readonly: 
You are now connected to database "two_dw" as user "readonly".
-- 创建 t1 表 可以创建成功
two_dw=> create table t1 (id int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
two_dw=> select count(*) from t1;
 count 
-------
     0
(1 row)

用户如果使用begion transaction read write可破解。

-- 测试 begion transaction read write 
two_dw=> \c postgres gpadmin
You are now connected to database "postgres" as user "gpadmin".
-- 切回 default_transaction_read_only  on状态
postgres=# alter user readonly set default_transaction_read_only=on;
ALTER ROLE
postgres=# \c two_dw readonly
Password for user readonly: 
You are now connected to database "two_dw" as user "readonly".
two_dw=> begin transaction read write;
BEGIN
two_dw=>  create table t1 (id int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
two_dw=> select count(*) from t1;
 count 
-------
     0
(1 row)
-- 结束会话
two_dw=> end;
COMMIT
-- 新会话，仍然为read-only模式
two_dw=> create table t1 (id int);
ERROR:  transaction is read-only

3、把所有库的语言的USAGE权限给到readonly

postgres=# GRANT USAGE ON SCHEMA public to readonly;
GRANT

4、授予select权限

-- 进行授权数据库，进行授权
postgres=> \c two_dw two
grant select on all tables in schema public to readonly;
two_dw => \c two_dw readonly
Password for user readonly: 
two_dw => \c
You are now connected to database "two_dw" as user "readonly".
two_dw => grant select on all tables in schema public to readonly;
GRANT
two_dw => select count(*) from two_test;
 count  
--------
 149491

文档下载：

《PL/Java.pdf》文档下载：https://www.modb.pro/doc/70867
《GP的资源队列.pdf》文档下载：https://www.modb.pro/doc/67644
《Greenplum psql客户端免交互执行SQL.pdf》https://www.modb.pro/doc/69806
《Oracle 自动收集统计信息机制》：https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》：https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》https://www.modb.pro/db/414692

                    欢迎点赞支持或留言指正