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