Query regarding permission on table_column%type access

From: Neha Sharma <neha(dot)sharma(at)enterprisedb(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Query regarding permission on table_column%type access
Date: 2017-10-31 10:33:16
Message-ID: CANiYTQtKptMZqdqB5LESv6xQwkRLw8+3pZvk3oJvCK8zNhjBBQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Geoff Winkless 2017-10-31 11:06:27 Re: MERGE SQL Statement for PG11
Previous Message tushar 2017-10-31 10:28:17 Re: parallelize queries containing initplans