pg_db_role_setting

A system table storing the role 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 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 column datconfig and the pg_authid column rolconfig. (commit 2eda8dfb)

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

Version charts

Useful overviews

Useful links

Contents

pg_db_role_setting

psql

Definition by PostgreSQL version

pg_db_role_setting (PostgreSQL 15)

pg_db_role_setting (PostgreSQL 14)

pg_db_role_setting (PostgreSQL 13)

pg_db_role_setting (PostgreSQL 12)

pg_db_role_setting (PostgreSQL 11)

pg_db_role_setting (PostgreSQL 10)

pg_db_role_setting (PostgreSQL 9.6)

pg_db_role_setting (PostgreSQL 9.5)

pg_db_role_setting (PostgreSQL 9.4)

pg_db_role_setting (PostgreSQL 9.3)

pg_db_role_setting (PostgreSQL 9.2)

pg_db_role_setting (PostgreSQL 9.1)

pg_db_role_setting (PostgreSQL 9.0)

Change history

Examples

References

Categories

See also