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
