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

On Mon, May 31, 2021 at 12:19 AM <mzj1996(at)mail(dot)ustc(dot)edu(dot)cn> wrote:

> 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?*
>
Hi,
In your example, the second 'CREATE TABLE sales_1' should be 'CREATE TABLE
sales_2'.

What is the expected behavior if sales_2 is created after the 'GRANT SELECT
ON sales TO test' statement ?
Should permission on sales_2 be granted to test ?

Cheers