Thread: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
"grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
*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$;
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Tom Lane
Date:
Bryn Llewellyn <bryn@yugabyte.com> writes:
> 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*
It shouldn't. Per the docs, the default permissions on a function
include GRANT EXECUTE TO PUBLIC. Revoking the never-granted-in-the-
first-place permission to z doesn't remove the PUBLIC permission.
So, if you want to be selective about who can use your functions,
you should revoke the PUBLIC permission and then grant out
permissions to individual roles.
regards, tom lane
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
"David G. Johnston"
Date:
On Fri, Feb 11, 2022 at 1:46 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
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...»
Is the design of my test-case faulty? Have I found a bug? Or is the doc wrong?
WADaD
"No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; ***EXECUTE privilege for functions and procedures***; and USAGE privilege for languages and data types (including domains)."
David J.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
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...
»
Is the design of my test-case faulty? Have I found a bug? Or is the doc wrong?
WADaD
https://www.postgresql.org/docs/current/ddl-priv.html
"PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command."
Ah... so that's it. Thanks to David J, Tom Lane, and Jeremy Smith who all told me the same thing. And thanks especially for the super-fast responses. I'm pleased to learn that the design of my test-case was faulty. It's corrected now.
I confess that I'm surprised by the choice of the default behavior. It seems to be at odds with the principle of least privilege that insists that you actively opt in to any relevant privilege.
I'll read up on ALTER DEFAULT PRIVILEGES and test it.
p.s., What's "WADaD", David. Internet search doesn't find me the translation except for, maybe, the Muslim name "Wadad" meaning "Love, friendship.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Tom Lane
Date:
Bryn Llewellyn <bryn@yugabyte.com> writes:
> I confess that I'm surprised by the choice of the default behavior. It seems to be at odds with the principle of
leastprivilege that insists that you actively opt in to any relevant privilege.
I'd be the first to agree that this behavior sacrifices security
principles for convenience. However, it's not that big a deal
in practice, because functions that aren't SECURITY DEFINER can't
do anything that the caller couldn't do anyway. You do need to
be careful about the default PUBLIC grant if you're making a
SECURITY DEFINER function, but that's a minority use-case.
(I wonder if it'd be practical or useful to emit a warning when
granting permissions on an object that already has a grant of
the same permissions to PUBLIC. That would at least cue people
who don't understand about this behavior that they ought to look
more closely.)
regards, tom lane
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
"David G. Johnston"
Date:
On Fri, Feb 11, 2022 at 2:44 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
p.s., What's "WADaD", David. Internet search doesn't find me the translation except for, maybe, the Muslim name "Wadad" meaning "Love, friendship.
Working as designed, and documented.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
"David G. Johnston"
Date:
On Fri, Feb 11, 2022 at 3:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
(I wonder if it'd be practical or useful to emit a warning when
granting permissions on an object that already has a grant of
the same permissions to PUBLIC. That would at least cue people
who don't understand about this behavior that they ought to look
more closely.)
We did something similar a while ago where we now warn if you try to revoke a privilege on a role that is actually inherited from PUBLIC and so the revoke on the role doesn't actually do anything. The inverse seems reasonable, and consistent that, at first blush.
David J.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:tgl@sss.pgh.pa.us wrote:
(I wonder if it'd be practical or useful to emit a warning when granting permissions on an object that already has a grant of
the same permissions to PUBLIC. That would at least cue people who don't understand about this behavior that they ought to look more closely.)
We did something similar a while ago where we now warn if you try to revoke a privilege on a role that is actually inherited from PUBLIC and so the revoke on the role doesn't actually do anything. The inverse seems reasonable, and consistent that, at first blush.
I'll wait with interest to see what might get implemented.
There's another common approach in this general space—when a fear arises that privileges have been granted too liberally so that vulnerabilities might have (presumably unintentionally) been exposed. It's to write various report generators—sometimes extended to become recommendation generators.
I looked at Chapter 52, "System Catalogs" at https://www.postgresql.org/docs/current/catalogs.html. It lists 97 relations. I'll have to defer reading about every one of these to another day. I searched the page for likely names looking for ones with "priv" and "rol". There's just a small number of hits. I drilled down on these. But none seemed to help finding out which objects, of which kinds, have which privileges (or roles) granted to which grantees.
Which catalog relations are sufficient to support a query that lists out, for example, every user-defined function and procedure with its (at least first-level) grantees?
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Adrian Klaver
Date:
On 2/11/22 15:14, Bryn Llewellyn wrote: >> /david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:/ >> > I looked at Chapter 52, "System Catalogs" at > https://www.postgresql.org/docs/current/catalogs.html > <https://www.postgresql.org/docs/current/catalogs.html>. It lists 97 > relations. I'll have to defer reading about every one of these to > another day. I searched the page for likely names looking for ones with > "priv" and "rol". There's just a small number of hits. I drilled down on > these. But none seemed to help finding out which objects, of which > kinds, have which privileges (or roles) granted to which grantees. > > Which catalog relations are sufficient to support a query that lists > out, for example, every user-defined function and procedure with its (at > least first-level) grantees? Tip if you do: psql -d test -U postgres -h localhost -E the -E will get you the queries for the \ meta-commands. So: \df+ tag_changeset_fnc Yields: SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END as "Type", CASE WHEN p.provolatile = 'i' THEN 'immutable' WHEN p.provolatile = 's' THEN 'stable' WHEN p.provolatile = 'v' THEN 'volatile' END as "Volatility", CASE WHEN p.proparallel = 'r' THEN 'restricted' WHEN p.proparallel = 's' THEN 'safe' WHEN p.proparallel = 'u' THEN 'unsafe' END as "Parallel", pg_catalog.pg_get_userbyid(p.proowner) as "Owner", CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security", pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges", l.lanname as "Language", p.prosrc as "Source code", pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE p.proname OPERATOR(pg_catalog.~) '^(tag_changeset_fnc)$' COLLATE pg_catalog.default AND pg_catalog.pg_function_is_visible(p.oid) ORDER BY 1, 2, 4; The parts you would be interested in are "Owner", "Security" and "Access privileges". You could modify the query to slim the results down some. -- Adrian Klaver adrian.klaver@aklaver.com
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Adrian Klaver
Date:
On 2/11/22 15:48, Adrian Klaver wrote: > On 2/11/22 15:14, Bryn Llewellyn wrote: >>> /david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:/ >>> > > > Tip if you do: > > psql -d test -U postgres -h localhost -E > > the -E will get you the queries for the \ meta-commands. > > So: > > \df+ tag_changeset_fnc > Should have mentioned, if you want to include system functions then it would be: \dfS+ -- Adrian Klaver adrian.klaver@aklaver.com
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
bryn@yugabyte.com wrote:
Which catalog relations are sufficient to support a query that lists out, for example, every user-defined function and procedure with its (at least first-level) grantees?
adrian.klaver@aklaver.com wrote:
Tip if you do:
psql -d test -U postgres -h localhost -E
the -E will get you the queries for the \ meta-commands. So:
\df+ my_function
Should have mentioned, if you want to include system functions then it would be:
\dfS+
Thanks, Adrian. Ah, yes… I have used that technique before. The query that's shown is quite a mouthful. I could prune it down to what I need, of course. But, for now, looking at what the \df+ metacommand outputs will do. I made a new small test-case and copied it at the end.
The "Access privileges" column in the \df+ output for "s.f()", at its first use, is empty. I read this in the section "5.7 Privileges" that we've already mentioned:
«
If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then modify them per the specified request.
»
The \df+ output at its second use shows this:
=X/x
x=X/x
z=X/x
x=X/x
z=X/x
The \df+ output at its third use shows this:
x=X/x
z=X/x
And finally, the \df+ output at its fourth use shows this (again):
=X/x
x=X/x
z=X/x
x=X/x
z=X/x
I s'pose that I can interpret this output in the light of the "miriam" example by guessing than an empty LHS means "public" and that the initial "X" means "execute". It looks like what follows the slash is the owner of the object (a denormalization of what the "Owner" column shows.)
Where is this notation, "miriam=arwdDxt/miriam", explained?
I does seem, then, that with enough effort, what I've learned here would be enough to allow writing (say) a table function that reports owner, schema, name, and arg signature for every user defined function and procedure that has "execute" never revoked from, or re-granted to, "public".
I wonder if such a thing, if written and reviewed carefully, could find its way into a future PG release.
--------------------------------------------------------------------------------
\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;
\df+ s.f
grant execute on function s.f(int) to z;
\df+ s.f
revoke execute on function s.f(int) from public;
\df+ s.f
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;
\df+ s.f
grant execute on function s.f(int) to z;
\df+ s.f
revoke execute on function s.f(int) from public;
\df+ s.f
grant execute on function s.f(int) to public;
\df+ s.f
\df+ s.f
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Adrian Klaver
Date:
On 2/11/22 17:24, Bryn Llewellyn wrote: >>> /bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:/ > I s'pose that I can interpret this output in the light of the "miriam" > example by guessing than an empty LHS means "public" and that the > initial "X" means "execute". It looks like what follows the slash is the > owner of the object (a denormalization of what the "Owner" column shows.) > > *Where is this notation, "miriam=arwdDxt/miriam", explained?* Here: https://www.postgresql.org/docs/current/ddl-priv.html From: Table 5.1. ACL Privilege Abbreviations -- Adrian Klaver adrian.klaver@aklaver.com
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Julien Rouhaud
Date:
On Fri, Feb 11, 2022 at 09:07:16PM -0800, Adrian Klaver wrote: > On 2/11/22 17:24, Bryn Llewellyn wrote: > > > > /bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:/ > > > I s'pose that I can interpret this output in the light of the "miriam" > > example by guessing than an empty LHS means "public" and that the > > initial "X" means "execute". It looks like what follows the slash is the > > owner of the object (a denormalization of what the "Owner" column > > shows.) > > > > *Where is this notation, "miriam=arwdDxt/miriam", explained?* > > Here: > https://www.postgresql.org/docs/current/ddl-priv.html > > From: > > Table 5.1. ACL Privilege Abbreviations You might also be interested in aclexplode() function, see https://www.postgresql.org/docs/current/functions-info.html.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
rjuju123@gmail.com wrote:adrian.klaver@aklaver.com wrote:bryn@yugabyte.com wrote:I s'pose that I can interpret this output in the light of the "miriam" example by guessing than an empty LHS means "public" and that the initial "X" means "execute". It looks like what follows the slash is the owner of the object (a denormalization of what the "Owner" column shows.) Where is this notation, "miriam=arwdDxt/miriam", explained?
Here:Table 5.1. ACL Privilege Abbreviations
You might also be interested in aclexplode() function, see:9.26. System Information Functions and Operators
Thanks, Julien. I tried a little test. I created a function "s.q()" with owner "x" and then granted "execute" on it to user "z". But I didn't yet revoke "execute" on "s.q()" from "public".
This is whet the "Access privilege" column in the \df+ report for "s.q()" shows:
=X/x
x=X/x
z=X/x
x=X/x
z=X/x
I.e. three facts per row: grantee, privilege, and grantee. Then I did this:
select
proname::text as name,
pronamespace::regnamespace::text as schema,
aclexplode(proacl) as "aclexplode(proacl)"
from pg_catalog.pg_proc)
select "aclexplode(proacl)" from c
where name = 'q' and schema = 's';
This is the result:
aclexplode(proacl)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)
This is consistent with the doc that says the array is exploded to records with this signature:
(grantor oid, grantee oid, privilege_type text, is_grantable boolean )
This is the perfect starting point for the table function that I was after that would list all user-defined functions and procedures that have "execute" granted to "public". A little bit of perfectly manageable effort will be needed for the special case that when "proacl" is "null", it means that "public" has "execute"—and also to translate the "oid" values" to text.
I'll try this presently and report back.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Dominique Devienne
Date:
On Sat, Feb 12, 2022 at 8:43 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> I.e. three facts per row: grantee, privilege, and grantee. Then I did this:
> with c as (
> select
> proname::text as name,
> pronamespace::regnamespace::text as schema,
> aclexplode(proacl) as "aclexplode(proacl)"
> from pg_catalog.pg_proc)
> select "aclexplode(proacl)" from c
> where name = 'q' and schema = 's';
>
> This is the result:
> aclexplode(proacl)
> -----------------------------
> (1494148,0,EXECUTE,f)
> (1494148,1494148,EXECUTE,f)
> (1494148,1494150,EXECUTE,f)
`aclexplode` is a table-valued function, so you normally use it in the
FROM clause.
Here's how I use it on schemas for example:
```
select nspname as name,
nspowner::regrole::text as owner,
grantor::regrole::text,
grantee::regrole::text,
privilege_type, is_grantable
from pg_namespace
left join lateral aclexplode(nspacl) on true
where ...
order by nspname
```
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
ddevienne@gmail.com wrote:bryn@yugabyte.com wrote:…Then I did this:
with c as (
select
proname::text as name,
pronamespace::regnamespace::text as schema,
aclexplode(proacl) as "aclexplode(proacl)"
from pg_catalog.pg_proc)
select "aclexplode(proacl)" from c
where name = 'q' and schema = 's';
This is the result:
aclexplode(proacl)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)
`aclexplode` is a table-valued function, so you normally use it in the
FROM clause.
Here's how I use it on schemas for example:
```
select nspname as name,
nspowner::regrole::text as owner,
grantor::regrole::text,
grantee::regrole::text,
privilege_type, is_grantable
from pg_namespace
left join lateral aclexplode(nspacl) on true
where ...
order by nspname
```
Thank you very much for the tip and for the code example, Dominique. Yes, my SQL was poorly written. I wanted just a simple proof of concept that "aclexplode()" lets me access the individual values that the "proacl" column represents as an array of "aclitem" records without needing to parse text strings like "z=X/x". I'd started to picture writing my own function to do what "aclexplode()" does. But Julien Rouhaud told me about the built-in for the purpose I needed before I'd had time to give my own function any thought.
I should have at least moved my invocation of "aclexplode()" out of the CTE. But, of course, for an approach that finds many "pg_proc" rows, I'll need a proper, robust approach like you showed.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bruce Momjian
Date:
On Fri, Feb 11, 2022 at 05:05:20PM -0500, Tom Lane wrote: > Bryn Llewellyn <bryn@yugabyte.com> writes: > > I confess that I'm surprised by the choice of the default behavior. It seems to be at odds with the principle of leastprivilege that insists that you actively opt in to any relevant privilege. > > I'd be the first to agree that this behavior sacrifices security > principles for convenience. However, it's not that big a deal > in practice, because functions that aren't SECURITY DEFINER can't > do anything that the caller couldn't do anyway. You do need to > be careful about the default PUBLIC grant if you're making a > SECURITY DEFINER function, but that's a minority use-case. How would you do that securely? Create the function and set its permissions in a transaction block? > (I wonder if it'd be practical or useful to emit a warning when > granting permissions on an object that already has a grant of > the same permissions to PUBLIC. That would at least cue people > who don't understand about this behavior that they ought to look > more closely.) Agreed. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
From
Bryn Llewellyn
Date:
bryn@yugabyte.com wrote:ddevienne@gmail.com wrote:bryn@yugabyte.com wrote:
…Then I did this:
with c as (
select
proname::text as name,
pronamespace::regnamespace::text as schema,
aclexplode(proacl) as "aclexplode(proacl)"
from pg_catalog.pg_proc)
select "aclexplode(proacl)" from c
where name = 'q' and schema = 's';
This is the result:
aclexplode(proacl)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)
`aclexplode` is a table-valued function, so you normally use it in the
FROM clause.
Here's how I use it on schemas for example:
```
select nspname as name,
nspowner::regrole::text as owner,
grantor::regrole::text,
grantee::regrole::text,
privilege_type, is_grantable
from pg_namespace
left join lateral aclexplode(nspacl) on true
where ...
order by nspname
```
Thank you very much for the tip and for the code example, Dominique. Yes, my SQL was poorly written. I wanted just a simple proof of concept that "aclexplode()" lets me access the individual values that the "proacl" column represents as an array of "aclitem" records without needing to parse text strings like "z=X/x". I'd started to picture writing my own function to do what "aclexplode()" does. But Julien Rouhaud told me about the built-in for the purpose I needed before I'd had time to give my own function any thought.
I should have at least moved my invocation of "aclexplode()" out of the CTE. But, of course, for an approach that finds many "pg_proc" rows, I'll need a proper, robust approach like you showed.
I mentioned earlier in this thread that I thought that it would be useful to be able to list all the user-defined functions and procedures in a database which "public" is authorized to execute. I think that I mentioned "table function" as a possible useful encapsulation. Forget this. It was just a metaphor for "reusable". I wrote this short "language sql" function. The idea is the it would be installed with a dedicated owner in a dedicated schema so that all users in the database could execute it. Here's the DDL to create it:
create function pg.public_has_execute(proacl in aclitem[])
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;
Have I understood right that because this is "language sql", its defining subquery is inlined into the statement that uses it early enough in the compilation that it ends up the same as if its text has been included directly in the using statement—in other words, that the encapsulation as a function brings no performance considerations?
I've copied a self-contained script below that creates and tests it. The few tests that I did show that it works as I wanted it to. This is the result that it produces. It's what I expect:
owner | schema | name | type | language | public_has_execute
----------+--------+--------------------+------+----------+--------------------
postgres | utils | public_has_execute | func | sql | true
x | s | f | func | plpgsql | true
x | s | f | func | sql | false
x | s | p | proc | plpgsql | true
y | s | g | func | plpgsql | false
----------+--------+--------------------+------+----------+--------------------
postgres | utils | public_has_execute | func | sql | true
x | s | f | func | plpgsql | true
x | s | f | func | sql | false
x | s | p | proc | plpgsql | true
y | s | g | func | plpgsql | false
I did mention that it might be good if such a function could ship as part of a future PG Version. Forget that I said this, too. It's so short that anyone who wants it could write it. Moreover, somebody might want to list subprograms that, say, "mary" and "john" can execute. It seems that it would be far better just to implement this explicitly than to lard up a generic function with an elaborate parameterization and implementation.
So, unless anybody has comments, it's "case closed" from me.
--------------------------------------------------------------------------------
-- Setup
\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;
-- A more realistic example would have a dedicated user, say "utl"
-- to own utility subprograms.
create schema utils authorization postgres;
create function utils.public_has_execute(proacl in aclitem[])
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;
-- Example use.
create view utils.publicly_executable_subprograms(owner, schema, name, type, language, public_has_execute) as
select distinct
pg_catalog.pg_get_userbyid(p.proowner) as "owner",
p.pronamespace::regnamespace::text,
p.proname::text,
case p.prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
end,
l.lanname,
utils.public_has_execute(p.proacl)
from
pg_catalog.pg_proc p
left join pg_catalog.pg_language l
on l.oid = p.prolang
where l.lanname in ('plpgsql', 'sql');
--------------------------------------------------------------------------------
-- Create some example subprograms.
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 procedure s.p(i in int)
language plpgsql
as $body$
begin
assert (i between 1 and 10);
end;
$body$;
alter procedure s.p(int) owner to x;
grant execute on procedure s.p(int) to z;
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;
grant execute on function s.f(int) to z;
create function s.f(i in text)
returns text
language sql
as $body$
select i||'*'::text;
$body$;
alter function s.f(text) owner to x;
grant execute on function s.f(text) to z;
revoke execute on function s.f(text) from public;
create function s.g(i in int)
returns int
language plpgsql
as $body$
begin
return i*2;
end;
$body$;
alter function s.g(int) owner to y;
grant execute on function s.g(int) to z;
revoke execute on function s.g(int) from public;
--------------------------------------------------------------------------------
-- Test the scheme.
select owner, schema, name, type, language, public_has_execute::text
from utils.publicly_executable_subprograms
where schema not in ('information_schema', 'pg_catalog')
order by 1, 2, 3, 4;
-- Setup
\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;
-- A more realistic example would have a dedicated user, say "utl"
-- to own utility subprograms.
create schema utils authorization postgres;
create function utils.public_has_execute(proacl in aclitem[])
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;
-- Example use.
create view utils.publicly_executable_subprograms(owner, schema, name, type, language, public_has_execute) as
select distinct
pg_catalog.pg_get_userbyid(p.proowner) as "owner",
p.pronamespace::regnamespace::text,
p.proname::text,
case p.prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
end,
l.lanname,
utils.public_has_execute(p.proacl)
from
pg_catalog.pg_proc p
left join pg_catalog.pg_language l
on l.oid = p.prolang
where l.lanname in ('plpgsql', 'sql');
--------------------------------------------------------------------------------
-- Create some example subprograms.
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 procedure s.p(i in int)
language plpgsql
as $body$
begin
assert (i between 1 and 10);
end;
$body$;
alter procedure s.p(int) owner to x;
grant execute on procedure s.p(int) to z;
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;
grant execute on function s.f(int) to z;
create function s.f(i in text)
returns text
language sql
as $body$
select i||'*'::text;
$body$;
alter function s.f(text) owner to x;
grant execute on function s.f(text) to z;
revoke execute on function s.f(text) from public;
create function s.g(i in int)
returns int
language plpgsql
as $body$
begin
return i*2;
end;
$body$;
alter function s.g(int) owner to y;
grant execute on function s.g(int) to z;
revoke execute on function s.g(int) from public;
--------------------------------------------------------------------------------
-- Test the scheme.
select owner, schema, name, type, language, public_has_execute::text
from utils.publicly_executable_subprograms
where schema not in ('information_schema', 'pg_catalog')
order by 1, 2, 3, 4;