pg_db_role_setting
pg_db_role_setting
is a system catalogue table storing the default values set for run-time configuration variables, for each user/role and database.
pg_db_role_setting
was added in PostgreSQL 9.0.
This table is shared across all databases in a cluster.
psql
\drds
lists the per-database role settings stored by this table.
Definition by PostgreSQL version
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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: pg_db_role_setting
Change history
This table has not changed since it was first added.
- PostgreSQL 9.0
- added, replacing the
pg_database
columndatconfig
and thepg_authid
columnrolconfig
. (commit 2eda8dfb)
- added, replacing the
Examples
postgres=# SELECT * FROM pg_db_role_setting ; setdatabase | setrole | setconfig -------------+---------+------------------------------------------- 0 | 16384 | {"search_path=app, hstore, public"} 0 | 16385 | {"search_path=admin, users, public"} (2 rows)
The contents can also be displayed with the psql command \drds
postgres=# \drds List of settings Role | Database | Settings ----------+----------+------------------------------------- app | | search_path=app, hstore, public admin | | search_path=admin, users, public
References
- PostgreSQL documentation: pg_db_role_setting