Thread: Question on table inheritance and privileges

On 08/02/2016 09:35 PM, 윤기태 wrote:
> Dear pgsql-general,
>
> I found something strange with DROP TABLE CASCADE.

Not really, explanation inline below.

>
> create role a login;
> grant all on database mydb to a;
>
> create role b inherit role a login;
> grant all on database mydb to b;

The above leads to this from \du(edited):

  Role name   Member of
  a          | {b}
  b          | {}

If I am following what you are after is:

create role b inherit in role a login;

Role name   Member of
  a          | {}
  b          | {a}

Though that leads to this further down:

mydb=> \c - a
You are now connected to database "mydb" as user "a".

mydb=> create table c() inherits(a.a, b.b);
ERROR:  permission denied for schema b


So I think the overriding issue is what think grant all on database
is doing versus what it is actually doing. The database grant does not
confer permissions on all objects within database to the deisginated
role. The best place to see what the various GRANTs do is here:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"The possible privileges are:"

......


A good bit of the above is me assuming what you want to happen. A better
answer from the list would require an outline of what you want to achieve.

>
>
> (session of role a)
> psql -U a -d mydb;
> create schema a;
> create table a(i1 int);
>
>
> (session of role b)
> psql -U b -d mydb;
> create schema b;
> create table b(c1 char);
>
>
> (session of role a)
> create table c() inherits(a.a, b.b);
>
> (session of role b)
> drop table a.c;
> -->> ERROR: permission denied for schema a
> drop table b cascade
> NOTICE: drop cascades to table a.c
> DROP TABLE
>
>
> Is it normal?
> role b is not owner of table c but role b can drop it with cascade.
> If I 'grant all on schema a to b', role b still cannot drop table c.
> Because role b is not owner of table c.
>
> Sorry for poor English.
>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com