Where are database specific settings stored?

Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf.

I am curious about where the alter database set configuration parameter details are stored.

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there.

As often in similar cases, psql can be of tremendous help here. The key is \set ECHO_HIDDEN on, which then shows the queries behind the different meta-commands.

In one of my sandbox DBs I see the following:

test=# \drds
                       List of settings
    Role     │ Database │              Settings               
─────────────┼──────────┼─────────────────────────────────────
 alice       │          │ log_statement=none                 ↵
             │          │ work_mem=12MB
 bob         │          │ log_statement=all                  ↵
             │          │ work_mem=64MB
 luser       │          │ log_statement=all
 tom         │          │ log_statement=all
             │ test     │ search_path=test, public
             │ wacky    │ default_transaction_read_only=true

We are interested in the rows where there is no user specified - these are exactly the settings you were asking about. With ECHO_HIDDEN on, you also get the following query (the formatting is mine):

SELECT rolname AS "Role", 
       datname AS "Database",
       pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
  FROM pg_catalog.pg_db_role_setting s
  LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
  LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
 ORDER BY 1, 2;

One can simplify this to get only the DB-specific settings:

SELECT datname AS database,
pg_catalog.array_to_string(setconfig, E'\n') AS settings
FROM pg_catalog.pg_db_role_setting s
JOIN pg_catalog.pg_database d ON d.oid = setdatabase ORDER BY 1, 2;

 database │              settings               
──────────┼─────────────────────────────────────
 test     │ search_path=test, public
 wacky    │ default_transaction_read_only=true

StackExchange Database Administrators Q#242858, answer score: 13