Thread: [ADMIN] phantom rights

On Tue, Aug 1, 2017 at 7:53 AM, Thomas Poty <thomas.poty@gmail.com> wrote:
> Hello world,
>
> I wonder if the problem of "phantom rights" exists in Postgresql 9.5 as it
> exists in MySQL.
>
> What happens with privileges if :
> - I create a table t1
> - I grant to user u1 some privileges for the table t1
> - I drop table t1;
> - and I recreate de same table t1
>
> Are these privileges still existing or completely revoked/deleted with the
> drop table statement?

Easy enough to test:

create table test (a int, b text);
CREATE TABLE
smarlowe=# \z test
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | test | table |                   |

grant select on test to stan;
GRANT
smarlowe=# \z test
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges
| Policies
--------+------+-------+---------------------------+-------------------+----------
 public | test | table | smarlowe=arwdDxt/smarlowe+|                   |
        |      |       | stan=r/smarlowe           |                   |

smarlowe=# drop table test;
DROP TABLE
smarlowe=# create table test (a int, b text);
CREATE TABLE
smarlowe=# \z test
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | test | table |                   |                   |

So no, no phantom permissions.

Further:

smarlowe=# \c smarlowe stan
You are now connected to database "smarlowe" as user "stan".
smarlowe=> \z test
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | test | table |                   |                   |
(1 row)

smarlowe=> select * from test;
ERROR:  permission denied for relation test

Thomas Poty wrote:
> I checked the columns aclitems and relname in pg_class.
> I wanted to be sure there is no phantom rights problem.

Right.

The problem does not exist if you store the permissions
on the object itself, like PostgreSQL does, so that dropping
the object also removes the permissions.

Yours,
Laurenz Albe