Re: Restoring default privileges on objects

On 8/29/23 08:14, Tom Lane wrote:
> Erik Wienhold <ewie@ewie.name> writes:
>> On 29/08/2023 03:23 CEST Stuart McGraw <smcgraw@mtneva.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... :-)