public inbox for [email protected]  

help / color / mirror / Atom feed

From: Stuart McGraw <[email protected]>
To: [email protected]
Cc: Tom Lane <[email protected]>
Cc: Erik Wienhold <[email protected]>
Subject: Re: Restoring default privileges on objects
Date: Tue, 29 Aug 2023 10:43:45 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On 8/29/23 08:14, Tom Lane wrote:
> Erik Wienhold <[email protected]> writes:
>> On 29/08/2023 03:23 CEST Stuart McGraw <[email protected]> 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... :-)

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Restoring default privileges on objects
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox