Someone asked me a question about view and function permissions. I
assumed all object access done by a view would be based on the
permissions on the view, and not the permissions of the objects.
While table access done in a view follows this outline, function call
access does not. In my tests below, I create a function ftest(), and
allow only 'postgres' to execute it. I then create a view and put the
function call in the view, and grant permissions on the view to others.
However, in the test, you will see that the function call in the view
fails.
Is this a bug?
---------------------------------------------------------------------------
CREATE USER dummyuser;CREATE USERSET SESSION AUTHORIZATION postgres;SETCREATE OR REPLACE FUNCTION ftest() RETURNS
INTEGEREXTERNALSECURITY DEFINERAS 'SELECT 1' LANGUAGE 'sql';CREATE FUNCTIONREVOKE EXECUTE ON FUNCTION ftest() FROM
PUBLIC;REVOKESELECTftest(); ftest------- 1(1 row)SET SESSION AUTHORIZATION dummyuser;SETSELECT ftest();ERROR:
permissiondenied for function ftestSET SESSION AUTHORIZATION postgres;SETCREATE VIEW vv AS SELECT ftest();ERROR:
relation"vv" already existsSELECT * FROM vv; ftest------- 1(1 row)GRANT ALL ON vv TO PUBLIC;GRANTSET SESSION
AUTHORIZATIONdummyuser;SETSELECT * FROM vv;ERROR: permission denied for function ftest
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
