Skip site navigation (1) Skip section navigation (2)

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

From: "psql \dp showing empty Access privileges column for {}" <gszpetkowski(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6021: There is no difference between default and empty access privileges with \dp
Date: 2011-05-11 07:10:21
Message-ID: 201105110710.p4B7ALQ5021179@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6021
Logged by:          psql \dp showing empty Access privileges column for {}
Email address:      gszpetkowski(at)gmail(dot)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

Responses

pgsql-bugs by date

Next:From: 李紅兵Date: 2011-05-11 07:13:04
Subject: Re: BUG #5994: Can't excute DBI->connect to oracle by client site
Previous:From: Maxo EtDirlothDate: 2011-05-11 06:21:59
Subject: Re: BUG #6019: invalid cached plan on inherited table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group