Search Postgresql Archives

"grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



*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$;


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux