pg_db_role_setting

A system catalogue table storing the default values set for run-time configuration variables

pg_db_role_setting is a system catalogue table storing the default values set for run-time configuration variables, for each role and database connection. This table is shared across all databases in a cluster.

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 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.

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

Categories

Cluster-wide table, Management / adminstration, System catalogue