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
On Wed, 2025-05-21 at 15:33 +0900, 노명석 wrote:
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.
I agree that showing the default privileges would reduce the
confusion for novice users, which is a good thing.
On the other hand, it would hide some information (namely, if there
is a NULL value in the ACL column or not), and it would constitute
a (small) compatibility break. So I am not sure what is better.
The current behavior is well documented:
If the “Access privileges” column is empty for a given object,
it means the object has default privileges (that is, its
privileges entry in the relevant system catalog is null).
Default privileges always include all privileges for the owner,
and can include some privileges for PUBLIC depending on the
object type, as explained above.
Yours,
Laurenz Albe
Dear Laurenz,
First of all, thank you for agreeing with my point about reducing confusion for users.
I hadn't noticed that the documentation already clarifies the meaning of an empty "Access privileges" column.
You've raised valid concerns about hiding information (the actual NULL status of nspacl) and the potential for a (small) compatibility break if psql were to display default ACLs when nspacl is NULL. This leads me to think: if there's an information discrepancy when the actual value is NULL but the display shows the default ACL, then wouldn't explicitly adding the default ACL to nspacl during CREATE SCHEMA itself (instead of leaving it NULL by default) pose an even greater risk of more significant compatibility issues? I agree that this isn't something that can be changed easily.
Separately, regarding my initial point in the first email about schema owner privileges not being included in the output of pg_dump – do you think it would be better to send a separate email to suggest adding an explanation for this to the documentation?
Yours,
Myeongseok Noh
-----------------------원본 메세지-----------------------
보낸사람: "Laurenz Albe"<laurenz.albe@cybertec.at>
받는사람: "노명석" <stan.num@kakaocorp.com>, pgsql-hackers@lists.postgresql.org
보낸날짜: 2025-05-21 16:59:24 GMT +0900 (Asia/Seoul)
제목: Re: psql : \dn+ to show default schema privileges
On Wed, 2025-05-21 at 15:33 +0900, 노명석 wrote:
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.
I agree that showing the default privileges would reduce the
confusion for novice users, which is a good thing.
On the other hand, it would hide some information (namely, if there
is a NULL value in the ACL column or not), and it would constitute
a (small) compatibility break. So I am not sure what is better.
The current behavior is well documented:
If the “Access privileges” column is empty for a given object,
it means the object has default privileges (that is, its
privileges entry in the relevant system catalog is null).
Default privileges always include all privileges for the owner,
and can include some privileges for PUBLIC depending on the
object type, as explained above.
Yours,
Laurenz Albe
On Wed, 2025-05-21 at 17:26 +0900, 노명석 wrote:
You've raised valid concerns about hiding information (the actual NULL
status of nspacl) and the potential for a (small) compatibility break
if psql were to display default ACLs when nspacl is NULL. This leads
me to think: if there's an information discrepancy when the actual
value is NULL but the display shows the default ACL, then wouldn't
explicitly adding the default ACL to nspacl during CREATE SCHEMA
itself (instead of leaving it NULL by default) pose an even greater
risk of more significant compatibility issues? I agree that this isn't
something that can be changed easily.
The behavior is the same if there is a NULL or the explicit default
value in "nspacl". So the information that you are missing if you
don't get to see the default value is marginal - essentially that
someone has granted or revoked privileges on that object.
The change in the "psql" output (that might surprise experienced
users) is the bigger concern in my opinion. But it is not a very big
concern either.
Separately, regarding my initial point in the first email about schema
owner privileges not being included in the output of pg_dump – do you
think it would be better to send a separate email to suggest adding an
explanation for this to the documentation?
I think that is unnecessary. "pg_dump" doesn't need to preserve
everything literally, as long as the behavior is not changed.
But that's just my opinion.
Yours,
Laurenz Albe
Hi,
While testing, I discovered an issue with the \dn+ command's output for schema privileges.
When \dn+ is executed:
For a schema in its initial state where nspacl is (null), the "Access privileges" field appears empty.
After executing REVOKE ALL ON SCHEMA schema_name FROM owner_role;, which changes the nspacl value in pg_namespace to {}, the "Access privileges" field in the \dn+ output is still similarly empty.
Here's an example illustrating this:
Output from SELECT tableoid, oid, nspname, nspowner::regrole, nspacl, acldefault FROM pg_namespace WHERE nspname IN ('schema1', 'stan1'); (assuming stan is the owner of schema1 and test is the owner of stan1 for this example):
tableoid | oid | nspname | nspowner | nspacl | acldefault
----------+-------+-----------+----------+-------------------------------------+------------------
2615 | 41813 | schema1 | stan | {} | {stan=UC/stan}
2615 | 41777 | stan1 | test | (null) | {test=UC/test}
Output from \dn+ schema1 stan1:
List of schemas
Name | Owner | Access privileges | Description
---------+-------+---------------------+--------------------
schema1 | stan | |
stan1 | test | |
As you can see, \dn+ shows an empty "Access privileges" field for schema1 (where nspacl is {}) and for stan1 (where nspacl is (null)).
This makes it difficult to distinguish between a schema with no explicit ACL entries (relying on defaults or owner privileges) and a schema where all privileges have been explicitly revoked.
For reasons like this, I believe the display method for schema privileges in \dn+ should be changed or improved to provide better clarity between these different states.
-----------------------원본 메세지-----------------------
보낸사람: "Laurenz Albe"<laurenz.albe@cybertec.at>
받는사람: "노명석" <stan.num@kakaocorp.com>, pgsql-hackers@lists.postgresql.org
보낸날짜: 2025-05-21 21:52:13 GMT +0900 (Asia/Seoul)
제목: Re: Re: psql : \dn+ to show default schema privileges
On Wed, 2025-05-21 at 17:26 +0900, 노명석 wrote:
You've raised valid concerns about hiding information (the actual NULL
status of nspacl) and the potential for a (small) compatibility break
if psql were to display default ACLs when nspacl is NULL. This leads
me to think: if there's an information discrepancy when the actual
value is NULL but the display shows the default ACL, then wouldn't
explicitly adding the default ACL to nspacl during CREATE SCHEMA
itself (instead of leaving it NULL by default) pose an even greater
risk of more significant compatibility issues? I agree that this isn't
something that can be changed easily.
The behavior is the same if there is a NULL or the explicit default
value in "nspacl". So the information that you are missing if you
don't get to see the default value is marginal - essentially that
someone has granted or revoked privileges on that object.
The change in the "psql" output (that might surprise experienced
users) is the bigger concern in my opinion. But it is not a very big
concern either.
Separately, regarding my initial point in the first email about schema
owner privileges not being included in the output of pg_dump – do you
think it would be better to send a separate email to suggest adding an
explanation for this to the documentation?
I think that is unnecessary. "pg_dump" doesn't need to preserve
everything literally, as long as the behavior is not changed.
But that's just my opinion.
Yours,
Laurenz Albe