• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4 (EOL)

Useful articles

Useful links

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

         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"
    

         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"
    

         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"
    

         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"
    

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"
    

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"
    

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"
    

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"
    

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"
    

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"
    

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"
    

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)

postgres=# \drds
                     List of settings
   Role   | Database |              Settings
----------+----------+-------------------------------------
 app      |          | search_path=app, hstore, public
 admin    |          | search_path=admin, users, public

References

Categories