Thread: why is the permission granted in a non-recursive way and what are the benefits
why is the permission granted in a non-recursive way and what are the benefits
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?
Re: why is the permission granted in a non-recursive way and what are the benefits
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?
Re: why is the permission granted in a non-recursive way and what are the benefits
mzj1996@mail.ustc.edu.cn writes:
> In most scenarios, we want to assign permissions to a table and partition table to a user, but in postgresql,
permissionsare not recursive, so we need to spend extra energy to do this. So let's ask the postgresql team, why is the
permissiongranted in a non-recursive way and what are the benefits?
It's intentional, because you might not wish to allow users of the
partitioned table to mess with the partitions directly. Since only
the table directly named in the query is permission-checked, it's
not necessary for users of the partitioned table to have such child
permissions.
regards, tom lane