Issues with privileges carrying over after alter table owner

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Issues with privileges carrying over after alter table owner

Jorge Torralba
we have added some default users to the template1 db so we can grant ro and rw privs as needed per owner.

For example

If I create table "yyy" as a special user with a set of privileges, the grantee and gantor data from thopse privs are there.

jorge_dev=# select * from information_schema.table_privileges where table_name = 'yyy';
  grantor  |  grantee   | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
-----------+------------+---------------+--------------+------------+----------------+--------------+----------------
 migration | read_write | jorge_dev     | public       | yyy        | INSERT         | NO           | NO
 migration | read_write | jorge_dev     | public       | yyy        | SELECT         | NO           | YES
 migration | read_write | jorge_dev     | public       | yyy        | UPDATE         | NO           | NO
 migration | read_write | jorge_dev     | public       | yyy        | DELETE         | NO           | NO
 migration | read_only  | jorge_dev     | public       | yyy        | SELECT         | NO           | YES
 migration | migration  | jorge_dev     | public       | yyy        | INSERT         | YES          | NO
 migration | migration  | jorge_dev     | public       | yyy        | SELECT         | YES          | YES
 migration | migration  | jorge_dev     | public       | yyy        | UPDATE         | YES          | NO
 migration | migration  | jorge_dev     | public       | yyy        | DELETE         | YES          | NO
 migration | migration  | jorge_dev     | public       | yyy        | TRUNCATE       | YES          | NO
 migration | migration  | jorge_dev     | public       | yyy        | REFERENCES     | YES          | NO
 migration | migration  | jorge_dev     | public       | yyy        | TRIGGER        | YES          | NO
(12 rows)

jorge_dev=#


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

For example, this is a table created under a different user than altered to owner migration

jorge_dev=# select * from information_schema.table_privileges where table_name = 'xxx';
  grantor  |  grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
-----------+-----------+---------------+--------------+------------+----------------+--------------+----------------
 migration | migration | jorge_dev     | public       | xxx        | INSERT         | YES          | NO
 migration | migration | jorge_dev     | public       | xxx        | SELECT         | YES          | YES
 migration | migration | jorge_dev     | public       | xxx        | UPDATE         | YES          | NO
 migration | migration | jorge_dev     | public       | xxx        | DELETE         | YES          | NO
 migration | migration | jorge_dev     | public       | xxx        | TRUNCATE       | YES          | NO
 migration | migration | jorge_dev     | public       | xxx        | REFERENCES     | YES          | NO
 migration | migration | jorge_dev     | public       | xxx        | TRIGGER        | YES          | NO
(7 rows)

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


-- 
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Reply | Threaded
Open this post in threaded view
|

Re: Issues with privileges carrying over after alter table owner

Joe Conway
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


signature.asc (849 bytes) Download Attachment