List Per-Role and Database Settings with pg_db_role_setting

calendar Oct 27, 2026 / · 12 min read · alter role alter database configuration psql work_mem statement_timeout pg_db_role_setting pg_database pg_roles  ·
Share on: linkedin copy

List Per-Role and Database Settings with pg_db_role_setting

A reporting user logs in and gets a work_mem that matches nothing in postgresql.conf. On the same server, statement_timeout differs between two databases, and neither value is in a file. In PostgreSQL, ALTER ROLE ... SET and ALTER DATABASE ... SET store their defaults in the pg_db_role_setting catalog, and those defaults override postgresql.conf when a session starts. The query below lists every override in the cluster by role and database.

Purpose and Overview

Three commands write to the same catalog: ALTER ROLE ... SET, ALTER DATABASE ... SET and ALTER ROLE ... IN DATABASE ... SET. Since PostgreSQL 9.0, all role and database settings are stored in pg_db_role_setting, which records the defaults for each role and database combination. The catalog is shared. There is one copy per cluster, not one per database, so a connection to any database sees the overrides for all of them.

The raw catalog is hard to read. setdatabase and setrole hold OIDs, and zero in either column means the setting is not tied to a database or a role. The values sit in setconfig, a text[] array. The older views don't fill the gap: pg_roles, pg_shadow and pg_user do not show combination settings, so pg_roles.rolconfig misses anything set with IN DATABASE.

The query returns three columns. Role and Database hold names. Either one is NULL when the setting applies to any role or any database. Settings puts each stored setting on its own line. psql sends this same query for \drds, which lists settings that are role-specific, database-specific, or both.

Sample Code

 1-- Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
 2-- Portions Copyright (c) 1994, The Regents of the University of California
 3--
 4-- Permission to use, copy, modify, and distribute this software and its
 5-- documentation for any purpose, without fee, and without a written agreement
 6-- is hereby granted, provided that the above copyright notice and this
 7-- paragraph and the following two paragraphs appear in all copies.
 8--
 9-- IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
10-- DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
11-- LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
12-- DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
13-- POSSIBILITY OF SUCH DAMAGE.
14--
15-- THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
16-- INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
17-- AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
18-- ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
19-- PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
20
21SELECT rolname AS "Role", datname AS "Database",
22pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
23FROM pg_catalog.pg_db_role_setting s
24LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
25LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
26ORDER BY 1, 2;

The filtered variant, for one role in one database:

 1-- Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
 2-- Portions Copyright (c) 1994, The Regents of the University of California
 3--
 4-- Permission to use, copy, modify, and distribute this software and its
 5-- documentation for any purpose, without fee, and without a written agreement
 6-- is hereby granted, provided that the above copyright notice and this
 7-- paragraph and the following two paragraphs appear in all copies.
 8--
 9-- IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
10-- DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
11-- LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
12-- DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
13-- POSSIBILITY OF SUCH DAMAGE.
14--
15-- THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
16-- INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
17-- AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
18-- ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
19-- PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
20
21SELECT rolname AS "Role", datname AS "Database",
22pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
23FROM pg_catalog.pg_db_role_setting s
24LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
25LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
26WHERE r.rolname = 'app_user' AND d.datname = 'appdb'
27ORDER BY 1, 2;

Notes: Needs PostgreSQL 9.0 or later, the release that introduced the catalog. Rows written by ALTER ROLE ALL SET appear only on 9.3 and later, because that form arrived in 9.3. Both queries only read catalogs, and one run covers every database in the cluster. In the second query, change app_user and appdb to the role and database you're checking. The filter drops rows where either name is NULL, so it returns only the role-in-database override. Use the first query to see the role's own settings and the database's settings as well.

Code Breakdown

The query has three parts: one catalog read, two joins that turn OIDs into names, and a function that flattens the settings array.

FROM pg_db_role_setting

Each row is one database and role pair, with its array of settings. A setting made with ALTER ROLE name SET has setdatabase of 0. One made with ALTER DATABASE name SET has setrole of 0. On PostgreSQL 9.3 and later, a row with both columns at 0 is the ALTER ROLE ALL SET level, which applies to every role in every database. Because only superusers can change a setting for all roles in all databases, that row is the one to read first when a value looks wrong everywhere.

The s alias is never used. The column names are unique across the three relations, so the query refers to them unqualified.

The two LEFT JOINs

OID 0 matches no row in pg_database or pg_roles. An inner join would drop every role-only, database-only and global row. The LEFT JOIN keeps them and puts NULL in the missing name. This is why \drds with no pattern also lists settings that are not role-specific or database-specific.

setrole references pg_authid.oid, but the query joins pg_roles, which is a publicly readable view of pg_authid that blanks out the password field. You get the role name, and the query never reads the password column.

array_to_string(setconfig, E'\n')

setconfig is a text[] array. array_to_string with a newline separator turns it into one string with one setting per line, which reads cleanly in psql's aligned output. To get one row per setting, so you can filter on a parameter name or load the result into another tool, swap it for unnest(setconfig) in the select list.

ORDER BY 1, 2 and the filter

ORDER BY 1, 2 sorts by the first and second output columns, Role and then Database. When you pass patterns to \drds, psql adds its filters on r.rolname and d.datname. The filtered variant uses plain equality on the same two columns. To see every setting that touches one role, keep r.rolname = 'app_user' and remove the datname condition. Rows with a NULL database then come back too.

Key Setting Precedence Rules

Five levels, one winner

When settings conflict, database-role-specific settings override role-specific ones, which in turn override database-specific ones. Settings for specific databases or specific roles override settings for all roles. A role default overrides whatever is in postgresql.conf or was received from the postgres command line. From strongest to weakest:

  1. ALTER ROLE name IN DATABASE db SET
  2. ALTER ROLE name SET
  3. ALTER DATABASE db SET
  4. ALTER ROLE ALL SET (PostgreSQL 9.3 and later; on 9.0 to 9.2 this level does not exist)
  5. postgresql.conf and the server command line

ALL IN DATABASE is ALTER DATABASE

Using ALL with IN DATABASE is effectively the same as ALTER DATABASE. Both commands store a setting for that database with no role. In the query output, both show up as a NULL Role next to the database name. You can't tell from the catalog which command wrote the row.

Applied at login, and only at login

An override becomes the session default when the session starts. This happens only at login time. Running SET ROLE or SET SESSION AUTHORIZATION does not set new configuration values. For a database, the value becomes the session default whenever a new session starts in that database.

So sessions that are already open keep the defaults they logged in with. A connection pool that holds connections open keeps serving the old value until those connections are replaced. A superuser who runs SET ROLE app_user to test a change still has their own defaults, not app_user's.

Reading which level won

pg_settings has a source column for where the current value came from. For these levels, the strings are database user, user, database and global. A value from the file shows configuration file, and a SET run in the session shows session.

Two more columns help. reset_val is the value RESET would reset the parameter to in the current session. If it differs from setting, a session-level SET has changed the value. sourcefile is null for values set from sources other than configuration files, so a NULL there alongside a source of user sends you back to this catalog. It is also null for a user who is not a superuser and lacks pg_read_all_settings, so read it from a role that has that access.

Who can set what

Some configuration variables can't be set this way at all, or only when a superuser issues the command.

Practical Applications

All four scenarios start from the same output. What changes is the next step: trace one value, save the rows, check them against what the role should have, or remove them.

Explaining a mystery timeout or work_mem value

A report times out in one database and finishes in another. Run the first query and find the rows for the reporting role. A row with both the role and the database name beats a row with the role alone, which beats a row with only the database. Then, in a fresh session as that role, check pg_settings.source for statement_timeout or work_mem. A value of database user confirms that the combination row won.

Pre-migration or upgrade inventory

These overrides live in a catalog, not in any file, so a copy of postgresql.conf doesn't capture them. Before you rebuild or move a cluster, save the query output with the rest of the server inventory. The catalog is shared, so one run covers every database. Run it again on the new cluster and compare the two outputs line by line.

Auditing one application role

Use the filtered variant when an application role should have one setting in one database and nothing else. A command such as ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG; creates the kind of row it returns. Then remove the datname condition and run it again. Any extra rows are role-wide settings that follow the role into every database.

Cleaning up stale overrides

ALTER ROLE worker_bee RESET maintenance_work_mem; removes the role-specific setting, so the role inherits the system-wide default in new sessions. RESET ALL clears all of a role's settings, and ALTER DATABASE ... RESET ALL clears all of a database's settings. Run the query again to confirm the setting is gone, then reconnect to see the new default take effect.

Version Compatibility

PostgreSQL 9.0 added settings for database and role combinations. Before that, only per-database and per-role settings were possible. The same release moved all role and database settings into pg_db_role_setting and added \drds, and removed the old datconfig and rolconfig columns. Before 9.0 you could not set a parameter for one user only when they connect to a given database.

ALTER ROLE ALL SET needs 9.3 or later. On 9.0 to 9.2, ALTER ROLE takes only a role name, with an optional IN DATABASE, so the catalog holds no row with both setrole and setdatabase at 0, and the precedence list above has four levels instead of five.

The catalog's documentation page goes back to 9.0 and no further. The current docs list three columns: setdatabase, setrole and setconfig. This query does not work on 8.4 and earlier; those releases kept these settings in pg_authid.rolconfig (shown in pg_roles) and pg_database.datconfig instead.

pg_roles still has a rolconfig column for role-specific defaults. It doesn't show combination settings, so it isn't a complete inventory on any release from 9.0 on. Use pg_db_role_setting instead.

Best Practices

  • Scope with IN DATABASE — a role-in-database setting outranks every other level and affects only one database.
  • Test with a new login — SET ROLE doesn't apply another role's defaults, so connect as the role itself.
  • Check pg_settings.source — the catalog shows what is stored, and source shows which level the session used.
  • Use \drds for a quick look — \drds [ role-pattern [ database-pattern ] ] runs this query in psql; keep the SQL for saved inventories.
  • Remove with RESET — RESET lets the role inherit the default again instead of pinning a copy of today's value.
  • Limit CREATEROLE — from PostgreSQL 16 it needs ADMIN OPTION to change another role; before 16 it covered any non-superuser role.

References

Posts in this series