Re: [HACKERS] Query regarding permission on table_column%type access
Stephen Frost writes: > * Neha Sharma (neha.sha...@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
Re: [HACKERS] Query regarding permission on table_column%type access
Greetings, * Neha Sharma (neha.sha...@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 signature.asc Description: Digital signature
[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