| *Summary* If user "x" owns function "s.f()", and if you want user "z" to be able to execute it, then this alone is insufficient: grant execute on function s.f() to z; The attempt by "z" to execute "s.f()" this draws the 42501 error, "permission denied for schema s". But this _is_ sufficient: grant usage on schema s to z; revoke execute on function s.f() from z; -- Yes, really! *This surprises me* The PG doc on, in the "5.7. Privileges" section at https://www.postgresql.org/docs/current/ddl-priv.html (under "USAGE" following "The available privileges are"), says this: « For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema... » Notice « assuming that the objects' own privilege requirements are also met ». I read this to mean that in my use case I must _both_ grant "usage" on the schema in question _and_ grant "execute" on the function in question—and this would make sense as part of a sound functional spec for the privileges model. But my self-contained test-case, copied below, shows an outcome that's at odds with the doc. It does a bit more than what my summary describes because it creates two functions "s.f()" and "s.g()" owned, respectively, by "x" and "y". (It finishes silently because I use "assert" statements in PL/pgSQL to demonstrate the outcomes.) There seems, then, to be no fine-grained control. I didn't type up other legs to the test (for example, to test selecting from a table) but it looks on its face as if "grant usage on schema" confers the ability to operate on every single object in the schema no matter, what the owner and the object type are. Is the design of my test-case faulty? Have I found a bug? Or is the doc wrong? *Test-case* -------------------------------------------------------------------------------- \c postgres postgres set client_min_messages = warning; drop database if exists db; create database db owner postgres; \c db postgres set client_min_messages = warning; drop schema if exists public cascade; create schema s authorization postgres; drop user if exists x; create user x login password 'p'; drop user if exists y; create user y login password 'p'; drop user if exists z; create user z login password 'p'; create function s.f(i in int) returns int language plpgsql as $body$ begin return i*2; end; $body$; alter function s.f(int) owner to x; create function s.g(i in int) returns int language plpgsql as $body$ begin return i*3; end; $body$; alter function s.g(int) owner to y; select proname as "Name", pronamespace::regnamespace as "Schema", proowner::regrole as "Owner" from pg_catalog.pg_proc where pronamespace::regnamespace::text = 's'; /* Name | Schema | Owner ------+--------+------- f | s | x g | s | y */; -- Fom the PG doc: -- If a superuser issue a GRANT or REVOKE command, -- the command is performed as though it were issued -- by the owner of the affected object. grant execute on function s.f(int) to z; grant execute on function s.g(int) to z; -------------------------------------------------------------------------------- \c db z set client_min_messages = warning; do $body$ declare msg text not null := ''; begin begin assert s.f(17) = 34; assert false, 'Should not get here'; exception when insufficient_privilege then get stacked diagnostics msg = message_text; assert msg = 'permission denied for schema s', 'bad message'; end; begin assert s.g(17) = 51; assert false, 'Should not get here'; exception when insufficient_privilege then get stacked diagnostics msg = message_text; assert msg = 'permission denied for schema s', 'bad message'; end; end; $body$; -------------------------------------------------------------------------------- \c db postgres set client_min_messages = warning; grant usage on schema s to z; revoke execute on function s.f(int) from z; revoke execute on function s.g(int) from z; -------------------------------------------------------------------------------- \c db z set client_min_messages = warning; -- The "execute" privilege isn't needed. do $body$ begin assert s.f(17) = 34; end; $body$; do $body$ begin assert s.g(17) = 51; end; $body$; |
