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
