Thread: Restoring default privileges on objects
Hello, The Postgresql docs on object privileges, https://www.postgresql.org/docs/14/ddl-priv.html say this in regard to the output of the psql \dp command: | If the “Access privileges” column is empty for a given object, it | means the object has default privileges (that is, its privileges | entry in the relevant system catalog is null). [...] The first GRANT | or REVOKE on an object will instantiate the default privileges | (producing, for example, miriam=arwdDxt/miriam) and then modify them | per the specified request. 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. Thanks for any suggestions!
> 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. Function acldefault('r', 'user1'::regrole) [1] gives you the default
privileges for tables.
You could set pg_class.relacl to NULL to restore the default privileges, but
messing with pg_catalog is at your own risk. Besides that I don't know of any
way to restore the default privileges other than revoking all privileges before
granting whatever acldefault gives you. Changing the table owner will then
also change the grantee and grantor in pg_class.relacl to the new owner.
[1] https://www.postgresql.org/docs/14/functions-info.html#FUNCTIONS-ACLITEM-FN-TABLE
--
Erik
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.
regards, tom lane
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... :-)
> On 29/08/2023 18:43 CEST Stuart McGraw <smcg4191@mtneva.com> wrote:
>
> 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... :-)
Indeed, that's confusing. Command \dp always prints null as empty string [1].
So \pset null '(null)' has no effect.
The docs don't mention that edge case [2] (the second to last paragraph):
"If the “Access privileges” column is empty for a given object, it
means the object has default privileges (that is, its privileges
entry in the relevant system catalog is null)."
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/psql/describe.c;h=bac94a338cfbc497200f0cf960cbabce2dadaa33;hb=9b581c53418666205938311ef86047aa3c6b741f#l1149
[2] https://www.postgresql.org/docs/14/ddl-priv.html
--
Erik
Erik Wienhold <ewie@ewie.name> writes:
> On 29/08/2023 18:43 CEST Stuart McGraw <smcg4191@mtneva.com> wrote:
>> 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?
> Indeed, that's confusing. Command \dp always prints null as empty string [1].
> So \pset null '(null)' has no effect.
Yeah, perhaps. The reason it so seldom comes up is that a state of
zero privileges is extremely rare (because it's useless in practice).
That being the case, if we were to do something about this, I'd vote
for changing the display of zero-privileges to "(none)" or something
along that line, rather than changing the display of NULL, which
people are accustomed to.
Fixing \dp to honor "\pset null" for this might be a reasonable
thing to do too. I'm actually a bit surprised that that doesn't
work already.
regards, tom lane
> On 29/08/2023 21:27 CEST Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Yeah, perhaps. The reason it so seldom comes up is that a state of > zero privileges is extremely rare (because it's useless in practice). > > That being the case, if we were to do something about this, I'd vote > for changing the display of zero-privileges to "(none)" or something > along that line, rather than changing the display of NULL, which > people are accustomed to. +1 > Fixing \dp to honor "\pset null" for this might be a reasonable > thing to do too. I'm actually a bit surprised that that doesn't > work already. Looks like all commands in src/bin/psql/describe.c set nullPrint = NULL. Has been that way since at least 1999. -- Erik
On 8/29/23 13:27, Tom Lane wrote:
> Erik Wienhold <ewie@ewie.name> writes:
>> On 29/08/2023 18:43 CEST Stuart McGraw <smcg4191@mtneva.com> wrote:
>>> 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?
>
>> Indeed, that's confusing. Command \dp always prints null as empty string [1].
>> So \pset null '(null)' has no effect.
>
> Yeah, perhaps. The reason it so seldom comes up is that a state of
> zero privileges is extremely rare (because it's useless in practice).
>
> That being the case, if we were to do something about this, I'd vote
> for changing the display of zero-privileges to "(none)" or something
> along that line, rather than changing the display of NULL, which
> people are accustomed to.
>
> Fixing \dp to honor "\pset null" for this might be a reasonable
> thing to do too. I'm actually a bit surprised that that doesn't
> work already.
>
> regards, tom lane
That change would still require someone using \dp to realize that
the "Access privileges" value could be either '' or NULL (I guess
that could be pointed out more obviously in the psql doc), and then
do a '\pset null' before doing \dp? That seems a little inconvenient.
As a possible alternative, in the query that \dp sends, what about
replacing the line:
select ...,
pg_catalog.array_to_string(c.relacl, E'\n') as "Access privileges"
...
with something like:
CASE array_length(c.relacl,1) WHEN 0 THEN '(none)' ELSE pg_catalog.array_to_string(c.relacl, E'\n') END as "Access
privileges"
I realize that removes the ability to control with pset what is
displayed, but maybe a little more foolproof for naive users like
myself?
> On 29/08/2023 22:44 CEST Stuart McGraw <smcg4191@mtneva.com> wrote: > > That change would still require someone using \dp to realize that > the "Access privileges" value could be either '' or NULL (I guess > that could be pointed out more obviously in the psql doc), and then > do a '\pset null' before doing \dp? That seems a little inconvenient. Right. > As a possible alternative, in the query that \dp sends, what about > replacing the line: > > select ..., > pg_catalog.array_to_string(c.relacl, E'\n') as "Access privileges" > ... > > with something like: > > CASE array_length(c.relacl,1) WHEN 0 THEN '(none)' ELSE pg_catalog.array_to_string(c.relacl, E'\n') END as "Accessprivileges" > > I realize that removes the ability to control with pset what is > displayed, but maybe a little more foolproof for naive users like > myself? I think hardcoding '(none)' is what Tom meant (at least how I read it). Also '(none)' should probably be localizable like the table header. The \pset change would be separate. -- Erik
On 2023-08-29 14:44:48 -0600, Stuart McGraw wrote:
> On 8/29/23 13:27, Tom Lane wrote:
> > Fixing \dp to honor "\pset null" for this might be a reasonable
> > thing to do too. I'm actually a bit surprised that that doesn't
> > work already.
>
> That change would still require someone using \dp to realize that
> the "Access privileges" value could be either '' or NULL (I guess
> that could be pointed out more obviously in the psql doc), and then
> do a '\pset null' before doing \dp? That seems a little inconvenient.
Or just always do a \pset null. For me printing NULL the same as an
empty string is just as confusing in normal tables, so that's the first
line in my ~/.psqlrc. YMMV, of course.
But I guess the point is that people who do \pset null expect to be able
to distinguish '' and NULL visually and might be surprised if that
doesn't work everywhere, while people who don't \pset null know that ''
and NULL are visually indistinguishable and that they may need some
other way to distinguish them if the difference matters.
So +1 for me fixing \dp to honor "\pset null".
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"