Re: Issues with privileges carrying over after alter table owner

On 5/1/19 5:50 PM, Jorge Torralba wrote:
> If I create table "yyy" as a special user with a set of privileges, the
> grantee and gantor data from thopse privs are there.

> If I create table 'xxx' as a different user, then alter the table owner
> the grantor and grantee information is not carried over.

> How can we get all the privileges and grants inherited when a table
> owner is altered ?

That is not the behavior I see:

create table ownertest(id int);
alter table ownertest owner to joe;
grant select on table ownertest to alice;
select * from information_schema.table_privileges where table_name =
'ownertest';
grantor | grantee | table_catalog | table_schema | table_name |
privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
joe | joe | deepdive | public | ownertest | INSERT
| YES | NO
joe | joe | deepdive | public | ownertest | SELECT
| YES | YES
joe | joe | deepdive | public | ownertest | UPDATE
| YES | NO
joe | joe | deepdive | public | ownertest | DELETE
| YES | NO
joe | joe | deepdive | public | ownertest |
TRUNCATE | YES | NO
joe | joe | deepdive | public | ownertest |
REFERENCES | YES | NO
joe | joe | deepdive | public | ownertest | TRIGGER
| YES | NO
joe | alice | deepdive | public | ownertest | SELECT
| NO | YES
(8 rows)

alter table ownertest owner to mary;
select * from information_schema.table_privileges where table_name =
'ownertest';
grantor | grantee | table_catalog | table_schema | table_name |
privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
mary | mary | deepdive | public | ownertest | INSERT
| YES | NO
mary | mary | deepdive | public | ownertest | SELECT
| YES | YES
mary | mary | deepdive | public | ownertest | UPDATE
| YES | NO
mary | mary | deepdive | public | ownertest | DELETE
| YES | NO
mary | mary | deepdive | public | ownertest |
TRUNCATE | YES | NO
mary | mary | deepdive | public | ownertest |
REFERENCES | YES | NO
mary | mary | deepdive | public | ownertest | TRIGGER
| YES | NO
mary | alice | deepdive | public | ownertest | SELECT
| NO | YES
(8 rows)

You will need to tell us exactly what version of postgres you are
running and show us precisely what commands are being run (as I have
above - a self contained test case) if you want us to be able to help.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development