why is the permission granted in a non-recursive way and what are the benefits

Поиск
Список
Период
Сортировка
От mzj1996@mail.ustc.edu.cn
Тема why is the permission granted in a non-recursive way and what are the benefits
Дата
Msg-id 385c4d9a.139b8.179c14a7e06.Coremail.mzj1996@mail.ustc.edu.cn
обсуждение исходный текст
Ответы Re: why is the permission granted in a non-recursive way and what are the benefits  (Zhihong Yu <zyu@yugabyte.com>)
Re: why is the permission granted in a non-recursive way and what are the benefits  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers

Our team uses postgresql as the database, but we have some problem on grant and revoke.

imagine the following sequence of operations:

create user test;
CREATE TABLE sales (trans_id int, date date, amount int)
PARTITION BY RANGE (date);
CREATE TABLE sales_1 PARTITION OF sales
   FOR VALUES FROM ('2001-01-01') TO ('2002-01-01')
   PARTITION BY RANGE (amount);
CREATE TABLE sales_1 PARTITION OF sales
   FOR VALUES FROM ('2002-01-01') TO ('2003-01-01')
   PARTITION BY RANGE (amount);
   
GRANT SELECT ON sales TO test;

set role test;

SELECT * FROM sales;
-- error, because test don't have select authority on sales_1
SELECT * FROM sales_1;

In this example, the role test only has the select permission for sales and cannot access sales_1, which is very inconvenient.

In most scenarios, we want to assign permissions to a table and partition table to a user, but in postgresql, permissions are not recursive, so we need to spend extra energy to do this. So let's ask the postgresql team, why is the permission granted in a non-recursive way and what are the benefits?

If it is in a recursive way, when I grant select on parent table to user, the user also have permission on child table. It is very convenient.

In postgresql, we already have the Inheritance. If the table child inherits the table parent, every query command to the parent will recurse to the child. If the user does not want to recurse, you can use only keyword to do this, then why the partition is not consistent with the inheritite feature?

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Masahiko Sawada
Дата:
Сообщение: Re: Skipping logical replication transactions on subscriber side
Следующее
От: Esteban Zimanyi
Дата:
Сообщение: How to disable the autovacuum ?