1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL：如何查询基于用户(role)设置的参数

• 数据库

PostgreSQL 可以针对服务级别，数据库级别，用户级别进行参数设置，例如在 postgresql.conf 设置的参数是针对整个服务级别，当然偶尔有这样的需求：需要对指定用户设置相关的参数，设置好用户的参数后，如何查询呢？下面演示下：

方法一: 查询 pg_user 视图

1.1 设置用户的 log_statement 参数

1
2
 
postgres=# alter role francs set log_statement="all";
ALTER ROLE

1.2 验证

1
2
3
4
5
6
7
8
9
10
11
 
postgres=# select * From pg_user where usename='francs';
-[ RECORD 1 ]--------------------
usename   | francs
usesysid  | 24920
usecreatedb | f
usesuper  | f
usecatupd  | f
userepl   | f
passwd   | 
valuntil  |
useconfig  | {log_statement=all}

1.3 设置用户的 maintenance_work_mem 参数

1
2
 
postgres=# alter role francs set maintenance_work_mem="1GB";
ALTER ROLE

1.4 再次验证

1
2
3
4
5
6
7
8
9
10
11
 
postgres=# select * From pg_user where usename='francs';
-[ RECORD 1 ]---------------------------------------------
usename   | francs
usesysid  | 24920
usecreatedb | f
usesuper  | f
usecatupd  | f
userepl   | f
passwd   | 
valuntil  |
useconfig  | {log_statement=all,maintenance_work_mem=1GB}

备注：上面是通过 pg_user.useconfig 查询。

方法二: 查询 pg_db_role_setting catalog 系统表

2.1 pg_db_role_setting 表结构

1
2
3
4
5
6
7
8
 
Table "pg_catalog.pg_db_role_setting"
 Column  | Type | Modifiers
-------------+--------+-----------
setdatabase | oid  | not null
setrole   | oid  | not null
setconfig  | text[] |
Indexes:
"pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"

备注：可见 pg_db_role_setting 会针对数据库，用户级别进行记录。

2.2 验证

1
2
3
4
5
6
7
8
9
10
 
postgres=# select oid,rolname from pg_authid where rolname='francs';
 oid | rolname
-------+---------
24920 | francs
(1 row)
  
postgres=# select * From pg_db_role_setting where setrole=24920;
setdatabase | setrole |         setconfig         
-------------+---------+----------------------------------------------
     0 |  24920 | {log_statement=all,maintenance_work_mem=1GB}

备注：因为之前的设置只针对用户 francs 设置，而没指定相应的数据库，所以 setdatabase 值为 0。接着往下看。

2.3 用户 francs 登陆 francs 库时，设置 client_min_messages 参数

1
2
3
4
5
6
7
8
 
postgres=# alter role francs in database francs set client_min_messages='warning';
ALTER ROLE
  
postgres=# select * From pg_db_role_setting where setrole=24920;
setdatabase | setrole |         setconfig         
-------------+---------+----------------------------------------------
     0 |  24920 | {log_statement=all,maintenance_work_mem=1GB}
   24922 |  24920 | {client_min_messages=warning}

备注：此时 setdatabase 值为 24922, 结果很明显了，不多解释。

2.4 再次查询 pg_user

1
2
3
4
5
6
7
8
9
10
11
 
postgres=# select * From pg_user where usename='francs';
-[ RECORD 1 ]---------------------------------------------
usename   | francs
usesysid  | 24920
usecreatedb | f
usesuper  | f
usecatupd  | f
userepl   | f
passwd   | 
valuntil  |
useconfig  | {log_statement=all,maintenance_work_mem=1GB}

备注：之前针对数据库 francs 设置的 client_min_messages 参数在 pg_user 里是查不到的。这是与 pg_user 比较大的区别。

方法三: 通过 show 命令查看

这种方法使用某个用户登陆到相应数据库后，通过 show 命令查看，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
[pg92@redhatB ~]$ psql
psql (9.2.1)
Type "help" for help.
  
postgres=# show log_statement;
log_statement
---------------
none
(1 row)
  
postgres=# /c francs francs
You are now connected to database "francs" as user "francs".
francs=> show log_statement;
log_statement
---------------
all
(1 row)

备注：这种方法最直接，但很不方便。

取消用户级参数设置

4.1 取消对用户 francs 的 maintenance_work_mem 设置

1
2
 
postgres=# alter role francs reset maintenance_work_mem;
ALTER ROLE

4.2 验证

1
2
3
4
5
6
7
8
9
10
11
 
postgres=# select * From pg_user where usename='francs';
-[ RECORD 1 ]--------------------
usename   | francs
usesysid  | 24920
usecreatedb | f
usesuper  | f
usecatupd  | f
userepl   | f
passwd   | 
valuntil  |
useconfig  | {log_statement=all}

备注：如果想取消多个参数，则使用”RESET ALL” 语法。

4.3 RESET ALL 语法

1
 
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL

备注：比较简单，不再演示。

总结

  1. pg_user 只能查询针对用户的设置，如果设置了用户登陆到某个库的参数设置，则查不到。
  2. 用户级别的参数设置建议查询 pg_db_role_setting 系统表，因为这记录了 “数据库 + 用户” 的参数设置。

参考

原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/237953.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论