Обсуждение: [HACKERS] Query regarding permission on table_column%type access
Hi,
I have observed that even if the user does not have permission on a table(created in by some other user),the function parameter still can have a parameter of that table_column%type.Scenario:
postgres=# create user u1 with login ;
CREATE ROLE
postgres=# create user u2 with login ;
CREATE ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> create table t1(a int);
CREATE TABLE
postgres=> revoke ALL on t1 from u2;
REVOKE
postgres=> \c - u2
You are now connected to database "postgres" as user "u2".
postgres=> create table t2(a int);
CREATE TABLE
postgres=> create or replace function foo(x t1.a%type) returns int as $$
BEGIN
return x + 1;
END;
$$ LANGUAGE plpgsql;
NOTICE: type reference t1.a%TYPE converted to integer
CREATE FUNCTION
postgres=> select foo(1);
foo
-----
2
(1 row)
postgres=> select * from t1;
ERROR: permission denied for relation t1
Is this an expected behaviour? What if the user does not wants the object type to be accessed across?
Thanks.
--
Regards,
Neha Sharma
Neha Sharma
Greetings, * Neha Sharma (neha.sharma@enterprisedb.com) wrote: > I have observed that even if the user does not have permission on a > table(created in by some other user),the function parameter still can have > a parameter of that table_column%type. This is because the creation of the table also creates a type of the same name and the type's permissions are independent of the table's. I imagine that you could REVOKE USAGE ON TYPE from the type and deny access to that type if you wanted to. I'm not sure that we should change the REVOKE on the table-level to also mean to REVOKE access to the type automatically (and what happens if you GRANT the access back for the table..? Would we need to track that dependency?) considering that's been the behavior for a very long time. Thanks! Stephen
Stephen Frost <sfrost@snowman.net> writes:
> * Neha Sharma (neha.sharma@enterprisedb.com) wrote:
>> I have observed that even if the user does not have permission on a
>> table(created in by some other user),the function parameter still can have
>> a parameter of that table_column%type.
> This is because the creation of the table also creates a type of the
> same name and the type's permissions are independent of the table's. I
> imagine that you could REVOKE USAGE ON TYPE from the type and deny
> access to that type if you wanted to.
Right. (I checked, seems to work as expected.)
> I'm not sure that we should change the REVOKE on the table-level to also
> mean to REVOKE access to the type automatically (and what happens if you
> GRANT the access back for the table..?
It seems pretty silly for privileges on table rowtypes to behave
differently from those on other rowtypes.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers