Re: Issues with privileges carrying over after alter table owner - Mailing list pgsql-admin

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