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
Attachment:
signature.asc
Description: OpenPGP digital signature