Thread: BUG #6021: There is no difference between default and empty access privileges with \dp

The following bug has been logged online:

Bug reference:      6021
Logged by:          psql \dp showing empty Access privileges column for {}
Email address:      gszpetkowski@gmail.com
PostgreSQL version: 9.0.4
Operating system:   Debian Squeeze
Description:        There is no difference between default and empty access
privileges with \dp
Details:

uname -a
Linux debian 2.6.32-5-686 #1 SMP Tue Mar 8 21:36:00 UTC 2011 i686 GNU/Linux

psql -tc "SELECT version()" postgres
 PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian
4.4.5-8) 4.4.5, 32-bit

Reproducing:

1) Log as any role (for this myuser) to psql
2) CREATE TABLE testing (value int);;
3) \dp testing

                            Access privileges
 Schema |  Name   | Type  | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
 public | testing | table |                   |
(1 row)

It is ok, because reading documentation I found that empty Access privileges
column means default privileges (in this example full privileges for myuser
and no privileges for PUBLIC).

4) SELECT relacl FROM pg_class WHERE relname = 'testing';
 relacl
--------

(1 row)

5) REVOKE ALL ON TABLE testing FROM myuser;
REVOKE

6) SELECT relacl FROM pg_class WHERE relname = 'testing';
 relacl
--------
 {}
(1 row)

7) \dp testing
                            Access privileges
 Schema |  Name   | Type  | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
 public | testing | table |                   |
(1 row)

As you see "Access privileges" column is still blank. I except that this
means that object has still default privileges, but it is not.

8) TABLE testing;
ERROR:  permission denied for relation testing

Using psql -E I noticed that \dp (\z) effectively invokes:

SELECT array_to_string(c.relacl, E'\n') FROM pg_class c WHERE c.relname =
'testing';
 array_to_string
-----------------

(1 row)

I am not familiar with "Column access privileges", but I see that
pg_catalog.array_to_string(attacl, E'\n  '), so probably works as same.

Regards,
Grzegorz Szpetkowski

"psql \dp showing empty Access privileges column for {}" <gszpetkowski@gmail.com> writes:
> Description:        There is no difference between default and empty access
> privileges with \dp

Yeah.  It's been like that since forever, and nobody's complained
before, possibly because revoking all privileges for everybody isn't
a particularly useful real-world case.

One possibility is to start showing "default" when the ACL is null,
which would be quite easy to implement:

    COALESCE(array_to_string(c.relacl, E'\n'), 'default')

But that might be too big a change.  Or we could take the opposite
tack of changing the display in the no-privileges case; but I don't
see a similarly compact way to do that.

            regards, tom lane

What about changing empty value in \dp (\z) to {} when priviliges are
really empty and leave column empty for default priviliges as it works
now. I mean the same behaviour as in relacl column in pg_class catalog
? It sound simplest for me:

empty string - default privileges
{} - no privileges
{postgres=3Darwdxt/postgres} - some privileges

"If the "Access privileges" column is empty for a given object, it
means the object has default privileges (that is, its privileges
column is null)."

from: http://www.postgresql.org/docs/9.0/static/sql-grant.html

I agree that "default" sounds more descriptive than "", but empty
string works for me too (especially with clear documentation on that).

Regards,
Grzegorz Szpetkowski

2011/5/13 Tom Lane <tgl@sss.pgh.pa.us>:
> "psql \dp showing empty Access privileges column for {}" <gszpetkowski@gm=
ail.com> writes:
>> Description: =A0 =A0 =A0 =A0There is no difference between default and e=
mpty access
>> privileges with \dp
>
> Yeah. =A0It's been like that since forever, and nobody's complained
> before, possibly because revoking all privileges for everybody isn't
> a particularly useful real-world case.
>
> One possibility is to start showing "default" when the ACL is null,
> which would be quite easy to implement:
>
> =A0 =A0 =A0 =A0COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>
> But that might be too big a change. =A0Or we could take the opposite
> tack of changing the display in the no-privileges case; but I don't
> see a similarly compact way to do that.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>

I see that I just confused old PostgreSQL 8.3 curly bracket behaviour
with new 8.4/9.0 (I am using 8.3 all the time):

http://www.postgresql.org/docs/8.3/static/sql-grant.html
http://www.postgresql.org/docs/9.0/static/sql-grant.html

That's why I felt some misunderstanding with my previous post.

Regards,
Grzegorz Szpetkowski

2011/5/13 Grzegorz Szpetkowski <gszpetkowski@gmail.com>:
> What about changing empty value in \dp (\z) to {} when priviliges are
> really empty and leave column empty for default priviliges as it works
> now. I mean the same behaviour as in relacl column in pg_class catalog
> ? It sound simplest for me:
>
> empty string - default privileges
> {} - no privileges
> {postgres=3Darwdxt/postgres} - some privileges
>
> "If the "Access privileges" column is empty for a given object, it
> means the object has default privileges (that is, its privileges
> column is null)."
>
> from: http://www.postgresql.org/docs/9.0/static/sql-grant.html
>
> I agree that "default" sounds more descriptive than "", but empty
> string works for me too (especially with clear documentation on that).
>
> Regards,
> Grzegorz Szpetkowski
>
> 2011/5/13 Tom Lane <tgl@sss.pgh.pa.us>:
>> "psql \dp showing empty Access privileges column for {}" <gszpetkowski@g=
mail.com> writes:
>>> Description: =A0 =A0 =A0 =A0There is no difference between default and =
empty access
>>> privileges with \dp
>>
>> Yeah. =A0It's been like that since forever, and nobody's complained
>> before, possibly because revoking all privileges for everybody isn't
>> a particularly useful real-world case.
>>
>> One possibility is to start showing "default" when the ACL is null,
>> which would be quite easy to implement:
>>
>> =A0 =A0 =A0 =A0COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>>
>> But that might be too big a change. =A0Or we could take the opposite
>> tack of changing the display in the no-privileges case; but I don't
>> see a similarly compact way to do that.
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>>
>

Grzegorz Szpetkowski <gszpetkowski@gmail.com> writes:
> I see that I just confused old PostgreSQL 8.3 curly bracket behaviour
> with new 8.4/9.0 (I am using 8.3 all the time):

Oh, you're right --- so I was mistaken to claim it had always been like
that.  Before we started using array_to_string here, you *could* tell
the difference between default privileges and no privileges.

The precedent of previous versions makes it more plausible that we
should print '{}' for no privileges, but I'm still not quite convinced.
Anybody else have an opinion?

            regards, tom lane

2011/5/13 Tom Lane <tgl@sss.pgh.pa.us>:
> Grzegorz Szpetkowski <gszpetkowski@gmail.com> writes:
>> I see that I just confused old PostgreSQL 8.3 curly bracket behaviour
>> with new 8.4/9.0 (I am using 8.3 all the time):
>
> Oh, you're right --- so I was mistaken to claim it had always been like
> that. =A0Before we started using array_to_string here, you *could* tell
> the difference between default privileges and no privileges.
>
> The precedent of previous versions makes it more plausible that we
> should print '{}' for no privileges, but I'm still not quite convinced.
> Anybody else have an opinion?
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>

I think that in such case it seems to be best solution (if any of
course) to provide "null" if object has no privileges:

"" - default ACL
"miriam=3DarwdDxt/miriam" - some ACL
"null" - no ACL

Regards,
Grzegorz Spetkowski

On Thu, May 12, 2011 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "psql \dp showing empty Access privileges column for {}" <gszpetkowski@gm=
ail.com> writes:
>> Description: =A0 =A0 =A0 =A0There is no difference between default and e=
mpty access
>> privileges with \dp
>
> Yeah. =A0It's been like that since forever, and nobody's complained
> before, possibly because revoking all privileges for everybody isn't
> a particularly useful real-world case.
>
> One possibility is to start showing "default" when the ACL is null,
> which would be quite easy to implement:
>
> =A0 =A0 =A0 =A0COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>
> But that might be too big a change.

I don't think that's too big a change.  ISTM we ought to change
something.  Another idea would be to always show the permissions, even
if nothing has been changed from the defaults.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 12, 2011 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> One possibility is to start showing "default" when the ACL is null,
>> which would be quite easy to implement:
>>
>>        COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>>
>> But that might be too big a change.

> I don't think that's too big a change.  ISTM we ought to change
> something.  Another idea would be to always show the permissions, even
> if nothing has been changed from the defaults.

That would require psql to have local knowledge about what the defaults
are, which is someplace I'd rather not go ...

            regards, tom lane

On Sun, May 15, 2011 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, May 12, 2011 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> One possibility is to start showing "default" when the ACL is null,
>>> which would be quite easy to implement:
>>>
>>> =A0 =A0 =A0 =A0COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>>>
>>> But that might be too big a change.
>
>> I don't think that's too big a change. =A0ISTM we ought to change
>> something. =A0Another idea would be to always show the permissions, even
>> if nothing has been changed from the defaults.
>
> That would require psql to have local knowledge about what the defaults
> are, which is someplace I'd rather not go ...

Ugh.  It's too bad the server doesn't expose that.  But given that it
doesn't, your idea sounds good to me.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, May 15, 2011 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> On Thu, May 12, 2011 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> One possibility is to start showing "default" when the ACL is null,
>>>> which would be quite easy to implement:
>>>>
>>>>        COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>>>>
>>>> But that might be too big a change.

>>> I don't think that's too big a change.  ISTM we ought to change
>>> something.  Another idea would be to always show the permissions, even
>>> if nothing has been changed from the defaults.

>> That would require psql to have local knowledge about what the defaults
>> are, which is someplace I'd rather not go ...

> Ugh.  It's too bad the server doesn't expose that.  But given that it
> doesn't, your idea sounds good to me.

So is this something we should slip into 9.1, or is it 9.2 material?
I've got no strong opinion about that myself.

            regards, tom lane

I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Sun, May 15, 2011 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Robert Haas <robertmhaas@gmail.com> writes:
>>>> On Thu, May 12, 2011 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>> One possibility is to start showing "default" when the ACL is null,
>>>>> which would be quite easy to implement:
>>>>> COALESCE(array_to_string(c.relacl, E'\n'), 'default')
>>>>> But that might be too big a change.

>>>> I don't think that's too big a change.  ISTM we ought to change
>>>> something.  Another idea would be to always show the permissions, even
>>>> if nothing has been changed from the defaults.

>>> That would require psql to have local knowledge about what the defaults
>>> are, which is someplace I'd rather not go ...

>> Ugh.  It's too bad the server doesn't expose that.  But given that it
>> doesn't, your idea sounds good to me.

> So is this something we should slip into 9.1, or is it 9.2 material?
> I've got no strong opinion about that myself.

Given the complete lack of responses, it's apparent that not too many
people care about this issue.  So I've stuck it on the TODO list;
it doesn't seem like something we should change post-beta1.

            regards, tom lane