Thread: psql : \dn+ to show default schema privileges

Hello PostgreSQL Hackers,

I propose an enhancement to psql \dn+ to display default schema privileges when nspacl is NULL, by using COALESCE with pg_catalog.acldefault('n', n.nspowner).

Currently, \dn+ shows NULL for "Access privileges" if a schema's ACLs haven't been explicitly altered. This can be misleading after a pg_dump/pg_restore operation, as pg_dump correctly omits GRANT statements for inherent owner privileges. On the new cluster, \dn+ then displays NULL, suggesting to operators that owner privileges might have been lost.

SELECT

    n.nspname AS "Name",

    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",

    COALESCE(

        pg_catalog.array_to_string(n.nspacl, E'\n'),

        pg_catalog.array_to_string(pg_catalog.acldefault('n', n.nspowner), E'\n')

    ) AS "Access privileges",

    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"

FROM

    pg_catalog.pg_namespace n

WHERE

    n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'

ORDER BY

    1;

This change would offer a more intuitive view of the owner's actual (default) privileges. While an ideal long-term solution might involve CREATE SCHEMA populating nspacl with default owner rights, modifying \dn+ is a simpler immediate improvement.

Separately, adding a note to the pg_dump documentation clarifying that owner's inherent privileges are not explicitly dumped could also be beneficial for users.

If there's any misunderstanding on my part about how pg_dump or the pg_namespace catalog works in this regard, I would welcome an explanation.

Thank you for your consideration.

Best regards,

Myoungseok Noh