Feedback-ID: i4271461c:Fastmail
Message-ID: <efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com>
Date: Tue, 29 Aug 2023 10:43:45 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
From: Stuart McGraw <smcg4191@mtneva.com>
Subject: Re: Restoring default privileges on objects
To: pgsql-general@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Erik Wienhold <ewie@ewie.name>
References: <96d6885a-5e25-9ae8-4a1a-d7e557a5fe9c@mtneva.com>
 <1166048641.281962.1693308151542@office.mailbox.org>
 <1006397.1693318485@sss.pgh.pa.us>
Content-Language: en-US
In-Reply-To: <1006397.1693318485@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/efdd465d-a795-6188-7f71-7cdb4b2be031%40mtneva.com>
Precedence: bulk

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... :-)