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?