FAQ
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

Search Discussions

  • Tom Lane at May 12, 2011 at 10:20 pm

    "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
  • Grzegorz Szpetkowski at May 12, 2011 at 10:50 pm
    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=arwdxt/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@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
  • Grzegorz Szpetkowski at May 12, 2011 at 11:46 pm
    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=arwdxt/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@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
  • Tom Lane at May 12, 2011 at 11:57 pm

    Grzegorz Szpetkowski 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
  • Grzegorz Szpetkowski at May 13, 2011 at 1:34 am

    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.  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
    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=arwdDxt/miriam" - some ACL
    "null" - no ACL

    Regards,
    Grzegorz Spetkowski
  • Robert Haas at May 15, 2011 at 8:01 pm

    On Thu, May 12, 2011 at 6:20 PM, Tom Lane wrote:
    "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.
    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.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Tom Lane at May 15, 2011 at 9:03 pm

    Robert Haas writes:
    On Thu, May 12, 2011 at 6:20 PM, Tom Lane 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
  • Robert Haas at May 23, 2011 at 5:10 pm

    On Sun, May 15, 2011 at 5:02 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Thu, May 12, 2011 at 6:20 PM, Tom Lane 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.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Tom Lane at May 24, 2011 at 10:28 pm

    Robert Haas writes:
    On Sun, May 15, 2011 at 5:02 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Thu, May 12, 2011 at 6:20 PM, Tom Lane 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
  • Tom Lane at May 26, 2011 at 10:27 pm

    I wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Sun, May 15, 2011 at 5:02 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Thu, May 12, 2011 at 6:20 PM, Tom Lane 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedMay 11, '11 at 7:10a
activeMay 26, '11 at 10:27p
posts11
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase