"grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
| От | Bryn Llewellyn |
|---|---|
| Тема | "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute" |
| Дата | |
| Msg-id | 0994D6C4-EDF0-4854-B528-88DDE429172E@yugabyte.com обсуждение исходный текст |
| Ответы |
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute" ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-general |
*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!
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$;
\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$;
В списке pgsql-general по дате отправления:
Предыдущее
От: Raymond BrinzerДата:
Сообщение: Re: Can we go beyond the standard to make Postgres radically better?
Следующее
От: "Peter J. Holzer"Дата:
Сообщение: Re: Can we go beyond the standard to make Postgres radically better?