PostgreSQL – Order of Object Permissions Check

permissionspostgresqlview

Given a database role, user1, a function something() defined as a stored procedure and a view created like:

CREATE VIEW view1 AS select * from something()

And, given this permissions:

REVOKE ALL ON FUNCTION something FROM user1
REVOKE SELECT ON view1 FROM user1

When I run SELECT * FROM view1, I get an error permission denied for function something().

My question is, if I revoke the select permissions on the view, why the function is called? I was expecting to receive something like:

permission denied for relation view1

Thank you!

Best Answer

The problem in that case is not exactly about permission order, but execution order.

In resume, for PostgreSQL :

1- Views that are acessing tables will override the tables permission

2- Views acessing functions, will need to evaluate all the functions, before being checked - so the functions must be executed before acessing the view, even if the view does not has select permissions...

How can we prove that?

In postgresql, views are able to give you permissions for doing a select in a table, even if the user don't have this permissions.

For example:

create view view2 as select * from table1;
revoke all on table1 from user1;
grant select on view2 to user1;

Log in as user 1: 

select * from table1 (permission denied) 
select * from view2 (sucess - the query executes)

In the case, the user will be able to select view2 even don't having permission to select the table.

But what if we do the same thing with a function? The behavior are NOT the same. Lets create a function that wait 5 seconds before returning 1 (so we can debug if postgresql is running the function everytime that we call the view)

CREATE OR REPLACE FUNCTION something() RETURNS integer
AS 'select 1 from pg_sleep(5);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT; --this function will delay 5 seconds

create view view1 as select * from something();
revoke all on function something() from user1;
grant select on view1 to user1;

Log in as user 1: 

select * from something(); (permission denied for something) 
select * from view1 (permission denied for something )

The permission to do select on the view do not overrides the function permission, and even worst if we revoke the permissions from view1, the message still shows that postgresql stopped our query because of the function, no matter what the permission of the view.. (that is exactly what is happening in the question)

But is the function really being checked first? If we give the 'all' permissions to the function, but revoke the view permission...

grant all on function something to user1; 
revoke all on view1 from user1; 
select * from view1;
Delayed 5 seconds... (the function executed!) 
Permission denied for select on view1

As you see postgresql WAITED 5 SECONDS before saying that we dont have permission to output the view, showing that the "something()" function are executed. So the function data return must exist before the check of the view.

So now with this tests, we now know that PostgreSQL needed to first evaluate all the functions before continuing our query, is just like the query still does not exist until all the functions envolved are fully completed, so the view cant be solved for postgresql to know if we have or not permission to select it.

I think this answer your question in the terms of "permission order", but why does postgresql needs to evaluate all the functions before continuing, thats is another question...

Related Question