Re: Restoring default privileges on objects

On 8/29/23 08:14, Tom Lane wrote:
> Erik Wienhold <ewie(at)ewie(dot)name> writes:
>> On 29/08/2023 03:23 CEST Stuart McGraw <smcgraw(at)mtneva(dot)com> wrote:
>>> If I've done a GRANT or REVOKE on some of the tables, how do I restore
>>> the default privileges so that the “Access privileges” appears empty
>>> again? I re-granted what I think are the default privileges but the
>>> "Access privileges" column for that table contains "user1=arwdDxt/user1"
>>> rather than being blank. This is Postgresql-14.
>
>> Yes, "user1=arwdDxt/user1" matches the default privileges if user1 is the table
>> owner.
>
> Right. There is no (supported) way to cause the ACL entry to go back
> to null. It starts life that way as an ancient hack to save a step
> during object creation. But the moment you do anything to the object's
> privileges, the NULL is replaced by an explicit representation of the
> default privileges, which is then modified per whatever command you
> are giving. After that the privileges will always be explicit.
>
> There's been occasional discussion of changing this behavior, but
> it'd take work and it'd likely add about as much surprise as it
> removes. People have been used to this quirk for a long time.

Thank you Erik and Tom for the explanations. I guess it's a it-is-
what-it-is situation :-). But while trying to figure it out myself
I found the following:

test=# CREATE ROLE user1;
test=# SET ROLE user1;
test=> CREATE TABLE t1(x int);
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t1 | table | | |

test=> SELECT FROM t1;
(0 rows)

test=> SET ROLE postgres;
test=# REVOKE ALL ON t1 FROM user1;
test=# SET ROLE user1;
test=> \dp
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t1 | table | | |

test=> SELECT FROM t1;
ERROR: permission denied for table t1

How does one distinguish between (blank)=(default privileges)
and (blank)=(no privileges)?

Shouldn't psql put *something* (like "(default)" or "-") in the
"Access privileges" column to indicate that? Or conversely,
something (like "(none)"?) in the revoked case?

It doesn't seem like a good idea to use the same visual
representation for two nearly opposite conditions. It confused
the heck out of me anyway... :-)