pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart McGraw <smcg4191@mtneva.com>
To: pgsql-general@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Erik Wienhold <ewie@ewie.name>
Subject: Re: Restoring default privileges on objects
Date: Tue, 29 Aug 2023 10:43:45 -0600
Message-ID: <efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com> (raw)
In-Reply-To: <1006397.1693318485@sss.pgh.pa.us>
References: <96d6885a-5e25-9ae8-4a1a-d7e557a5fe9c@mtneva.com>
	<1166048641.281962.1693308151542@office.mailbox.org>
	<1006397.1693318485@sss.pgh.pa.us>

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






view thread (14+ messages)  latest in thread

Message-ID: <efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com>
Permalink:  ../efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com/
Also on:    postgresql.org/message-id/efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: smcg4191@mtneva.com, tgl@sss.pgh.pa.us, ewie@ewie.name
  Subject: Re: Restoring default privileges on objects
  In-Reply-To: <efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com>
  References: <96d6885a-5e25-9ae8-4a1a-d7e557a5fe9c@mtneva.com> <1166048641.281962.1693308151542@office.mailbox.org> <1006397.1693318485@sss.pgh.pa.us> <efdd465d-a795-6188-7f71-7cdb4b2be031@mtneva.com>

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

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