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
