HGDB创建只读用户

文档用途

本文档用于指导如何在企业版和安全版创建只读用户。

详细信息

一、企业版创建只读用户

1、以超级用户highgo登录数据库，创建用户

highgo=# create user read_only with password 'read';CREATEROLE

2、设置为只读的transaction

highgo=# alter user read_only set default_transaction_read_only = on;ALTERROLE

3、访问非 public 模式中的表

默认在highgo数据库的public模式下的对象是可以访问的,如果要访问别的schema的表，则需要两步：

(1)首先要有使用schema的权限

highgo=# grant usage on schema test_schema to read_only ;GRANT

(2)然后加所有表的只读权限

highgo=# grant select on all tables in schema test_schema to read_only;GRANT

(3)如果不想给所有表的查询权限，则单独给某个表的查询权限

highgo=# grant select on TABLE test_schema.abc to read_only;GRANT

4、可以进行一下权限测试

[highgo@localhost~]$ psql-U read_only highgo highgo=>select*fromtest_schema.abclimit1;a|b---+----------------------------------1|db18340e7e9a86ea85a64addd9ea309f(1row)highgo=>insertintotest_schema.abcvalues(10,'10');ERROR:25006: cannotexecuteINSERTinaread-onlytransactionhighgo=>deletefromtest_schema.abcwherea=1;ERROR:25006: cannotexecuteDELETEinaread-onlytransactionhighgo=>updatetest_schema.abcsetb='xx'wherea=1;ERROR:25006: cannotexecuteUPDATEinaread-onlytransaction

5、如果要在别的数据库访问

(1)先要用highgo（超级用户登录），然后\c到对应的数据库

highgo=# \c testPSQL:Release5.6.4Connectedto:HighGoDatabaseV5.6Enterprise EditionRelease5.6.4-64-bitProduction You are now connectedtodatabase"test"asuser"highgo".

(2)执行下面的命令，将对应的schema的表查询权限给这个用户

test数据库的public模式的usage权限是默认就有的，只需要添加表的只读权限即可：

test=# grant select on all tables in schema public to read_only;GRANT

6、授予对只读用户的默认访问权限，对于对应模式 schema 下后期新创建的表也生效。

默认在对应模式下新创建的数据表,只读用户是没有访问权限的,需要手工赋权; 可以修改默认权限让只读用户直接拥有新表的访问权限 这样即使是该模式中新加的表，read_only用户都有只读权限

test=# alter default privileges in schema public grant select on tables to read_only;ALTERDEFAULTPRIVILEGES

二、安全版开启三权创建只读用户

1、查看安全配置参数hg_sepofpowers=on,已开启三权分立

highgo=>selectshow_secure_param();show_secure_param-----------------------------------------hg_sepofpowers=on,+hg_macontrol=min,+hg_rowsecure=off,+hg_showlogininfo=on,+hg_clientnoinput=30min(s),+hg_idcheck.enable=on,+hg_idcheck.pwdlock=5time(s),+hg_idcheck.pwdlocktime=24hour(s),+hg_idcheck.pwdvaliduntil=7day(s),+hg_idcheck.pwdpolicy=high,+hg_sepv4=v4,+(1row)

2、登录sysdba管理员用户创建只读用户

highgo=# create user read_only_secure with password 'readsecure@123';CREATEROLE

3、设置为只读的transaction

highgo=# alter user read_only_secure set default_transaction_read_only = on;ALTERROLE

4、把test_schema_secure模式usage权限赋给只读用户read_only_secure

highgo=# grant usage on schema test_schema_secure to read_only_secure ;GRANT

5、如果表的属主是sysdba，需要先更改表的属主为普通用户，再赋只读权限给其他用户

(1)把test_schema_secure模式下的表只读权限赋给只读用户read_only_secure报错

highgo=# grant select on all tables in schema test_schema_secure to read_only_secure;ERROR: Can't grant it to other role. highgo=# grant select on TABLE test_schema_secure.abc to read_only_secure; ERROR: Can'tgrantittoother role.

这个时候会发现，不管只把某一张表或者整个模式下表只读权限赋给只读用户read_only_secure都会提示Can’t grant it to other role.

(2)把表的属主更给为普通用户read_secure

highgo=# grant usage on schema test_schema_secure to read_secure ;GRANThighgo=# alter table test_schema_secure.abc owner to read_secure;ALTERTABLEhighgo=>\dt+test_schema_secure.abc ListofrelationsSchema|Name|Type|Owner|Size|Description--------------------+------+-------+-------------+-------+-------------test_schema_secure|abc|table|read_secure|16kB|(1row)

(3)表数量过多，可以通过直接生成sql语句更改表属主

highgo=# SELECThighgo-# format('ALTER TABLE %I.%I OWNER TO read_secure;', schemaname, tablename) AS alter_sqlhighgo-# FROM pg_tableshighgo-# WHERE schemaname = 'test_schema_secure';alter_sql----------------------------------------------------------ALTERTABLEtest_schema_secure.abc OWNERTOread_secure;ALTERTABLEtest_schema_secure.a01 OWNERTOread_secure;ALTERTABLEtest_schema_secure.a02 OWNERTOread_secure;ALTERTABLEtest_schema_secure.abs OWNERTOread_secure;(4rows)

(4)登录普通用户read_secure，grant select权限给只读用户read_only_secure

[root@slave~]# psql highgo read_securehighgo=>grantselectonTABLEtest_schema_secure.abctoread_only_secure;GRANThighgo=# \dp+ test_schema_secure.abcAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|abc|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)

(5)登录普通用户测试

[root@slave~]# psql highgo read_only_securehighgo=>select*fromtest_schema_secure.abc;a|b---+----------------------------------1|db18340e7e9a86ea85a64addd9ea309f(1row)

(6)设置test_schema_secure模式新增表，用户read_only_secure也有只读权限

highgo=# alter default privileges in schema test_schema_secure grant select on tables to read_only_secure;ALTERDEFAULTPRIVILEGES

(7)新增表测试

highgo=# create table test_schema_secure.a02(a int,b text);CREATETABLEhighgo=# \dp+ test_schema_secure.a02AccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|a02|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)

注意：

1、sysdba是数据库管理员，不建议使用sysdba管理业务表；建议新建业务账号，并更改表的属主为新的业务账号，避免影响测评。

2、表的属主为普通用户，无需更改表的属主，可以直接grant select权限给对应用户。

3、如果表的属主为sysdba用户，需要把所有表的只读权限赋给只读用户，不允许关库可以(3)生成对应sql；允许关库可以采用第三步操作，关闭三权，赋权之后再开启三权即可。

4、安全版和企业版一样，默认普通用户有数据库的public模式的usage权限。

三、安全版关闭三权创建只读用户

1、关闭三权，并重启数据库生效

[root@slave~]# psql highgo sysssopsql(4.5.7)Type"help"forhelp.highgo=>selectset_secure_param('hg_sepofpowers','off');set_secure_param---------------------------------setconfiguration successfully.(1row)highgo=>\q[root@slave~]#[root@slave~]# pg_ctl stopwaitingforservertoshut down....done server stopped[root@slave~]# pg_ctl startwaitingforservertostart....2025-07-2815:52:06.526CST[17087]LOG: Password detection moduleisdisabled2025-07-2815:52:06.529CST[17087]LOG:startingHighGo Security Enterprise EditionDatabaseSystem4.5.7onCentOS7 x86_64,buildon202108042025-07-2815:52:06.531CST[17087]LOG: listeningonIPv4 address"0.0.0.0",port58662025-07-2815:52:06.531CST[17087]LOG: listeningonIPv6 address"::",port58662025-07-2815:52:06.533CST[17087]LOG: listeningonUnix socket"/tmp/.s.PGSQL.5866"2025-07-2815:52:06.588CST[17088]LOG:databasesystem was shut down at2025-07-2815:52:00CST2025-07-2815:52:06.590CST[17088]LOG: Switchover the SSHA Role.CurrentisNONE2025-07-2815:52:06.598CST[17087]LOG:databasesystemisreadytoaccept connections done server started

2、查看安全配置参数hg_sepofpowers=off,已关闭三权分立

highgo=>selectshow_secure_param();show_secure_param-----------------------------------------hg_sepofpowers=off,+hg_macontrol=min,+hg_rowsecure=off,+hg_showlogininfo=on,+hg_clientnoinput=30min(s),+hg_idcheck.enable=off,+hg_idcheck.pwdlock=5time(s),+hg_idcheck.pwdlocktime=24hour(s),+hg_idcheck.pwdvaliduntil=7day(s),+hg_idcheck.pwdpolicy=high,+hg_sepv4=off,+(1row)

3、单独将表abs的只读权限赋给用户read_only_secure

highgo=# grant select on TABLE test_schema_secure.abs to read_only_secure;GRANThighgo=# \dp+ test_schema_secure.absAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|abs|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)

4、将所有表只读权限赋给用户read_only_secure

highgo=# grant select on all tables in schema test_schema_secure to read_only_secure;GRANT

5、查看相关权限

highgo=# \dt+ test_schema_secure.*ListofrelationsSchema|Name|Type|Owner|Size|Description--------------------+------+-------+--------+------------+-------------test_schema_secure|a01|table|sysdba|8192bytes|test_schema_secure|abc|table|sysdba|16kB|test_schema_secure|abs|table|sysdba|16kB|(3rows)highgo=# \dp+ test_schema_secure.a01AccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|a01|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)highgo=# \dp+ test_schema_secure.abcAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|abc|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)highgo=# \dp+ test_schema_secure.absAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies--------------------+------+-------+---------------------------+-------------------+----------test_schema_secure|abs|table|sysdba=arwdDxt/sysdba+|||||read_only_secure=r/sysdba||(1row)

6、设置test_schema_secure模式新增表，用户read_only_secure也有只读权限

highgo=# alter default privileges in schema test_schema_secure grant select on tables to read_only_secure;ALTERDEFAULTPRIVILEGES