目录

pg_db_role_setting

存储为运行时配置变量设置的角色默认值的一个系统目录表

pg_db_role_setting 是一个 系统目录 表，用于存储为每个用户/角色和数据库设置的运行时配置变量的默认值。

pg_db_role_setting 是在 PostgreSQL 9.0 中添加的。

该表在集群的所有数据库之间共享。

psql

\drds 列出了此表存储的每个数据库的角色设置。

按 PostgreSQL 版本定义

pg_db_role_setting (PostgreSQL 19)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 18)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 17)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 16)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 15)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 14)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 13)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 12)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null | 
 setrole     | oid    |           | not null | 
 setconfig   | text[] | C         |          | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 11)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 10)

         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.6)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.5)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.4)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.3)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.2)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.1)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

pg_db_role_setting (PostgreSQL 9.0)

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"
Tablespace: "pg_global"

文档: pg_db_role_setting

变更历史

示例

postgres=# ALTER ROLE admin SET search_path='app, users, public';
ALTER ROLE

postgres=# ALTER ROLE app IN DATABASE appdb SET search_path='app, hstore, public';
ALTER ROLE

postgres=# SELECT * FROM pg_db_role_setting;
 setdatabase | setrole |                setconfig
-------------+---------+-----------------------------------------
           0 |   16389 | {"search_path=\"app, users, public\""}
       16391 |   16390 | {"search_path=\"app, hstore, public\""}
(2 rows)

可以使用 psql 命令 \drds 以人类可读的格式显示 pg_db_role_setting 的内容。

postgres=# \drds
                   List of settings
 Role  | Database |             Settings
-------+----------+-----------------------------------
 admin |          | search_path="app, users, public"
 app   | appdb    | search_path="app, hstore, public"
(2 rows)

分类

集群范围表, 管理/行政, 系统目录

另请参阅

pg_authid

反馈

提交关于“pg_db_role_setting”的任何评论、建议或更正，请在此处 提交。