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

2022-02-15 Thread Bryn Llewellyn 
> b...@yugabyte.com wrote:
> 
>> ddevie...@gmail.com wrote:
>> 
>>> b...@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.

*Listing publicly executable subprograms*

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

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

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

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

2022-02-14 Thread Bruce Momjian 
On Fri, Feb 11, 2022 at 05:05:20PM -0500, Tom Lane wrote:
> Bryn Llewellyn  writes:
> > 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'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  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"

2022-02-14 Thread Bryn Llewellyn 
> ddevie...@gmail.com wrote:
> 
>> b...@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"

2022-02-14 Thread Dominique Devienne 
On Sat, Feb 12, 2022 at 8:43 PM Bryn Llewellyn  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"

2022-02-12 Thread Bryn Llewellyn 
> rjuju...@gmail.com wrote:
> 
>> adrian.kla...@aklaver.com wrote:
>> 
>>> b...@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
>> https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE
> 
> You might also be interested in aclexplode() function, see:
> 9.26. System Information Functions and Operators
> https://www.postgresql.org/docs/current/functions-info.html

Thanks, Adrian. I'm embarrassed that I didn't notice Table 5.1.

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

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)

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"

2022-02-11 Thread Julien Rouhaud 
On Fri, Feb 11, 2022 at 09:07:16PM -0800, Adrian Klaver wrote:
> On 2/11/22 17:24, Bryn Llewellyn wrote:
> > > > /b...@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"

2022-02-11 Thread Adrian Klaver 

On 2/11/22 17:24, Bryn Llewellyn wrote:

/b...@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.kla...@aklaver.com

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

2022-02-11 Thread Bryn Llewellyn 
>> b...@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.kla...@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

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

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

grant execute on function s.f(int) to public;
\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"

2022-02-11 Thread Adrian Klaver 

On 2/11/22 15:48, Adrian Klaver wrote:

On 2/11/22 15:14, Bryn Llewellyn wrote:

/david.g.johns...@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.kla...@aklaver.com

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

2022-02-11 Thread Adrian Klaver 

On 2/11/22 15:14, Bryn Llewellyn wrote:

/david.g.johns...@gmail.com  wrote:/



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?


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.kla...@aklaver.com

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

2022-02-11 Thread Bryn Llewellyn 
> david.g.johns...@gmail.com wrote:
> 
>> t...@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"

2022-02-11 Thread David G. Johnston 
On Fri, Feb 11, 2022 at 3:05 PM Tom Lane  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"

2022-02-11 Thread David G. Johnston 
On Fri, Feb 11, 2022 at 2:44 PM Bryn Llewellyn  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"

2022-02-11 Thread Tom Lane 
Bryn Llewellyn  writes:
> 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'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"

2022-02-11 Thread Bryn Llewellyn 
> david.g.johns...@gmail.com wrote:
> 
>> b...@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"

2022-02-11 Thread David G. Johnston 
On Fri, Feb 11, 2022 at 1:46 PM Bryn Llewellyn  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

"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"

2022-02-11 Thread Tom Lane 
Bryn Llewellyn  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

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

2022-02-11 Thread Bryn Llewellyn 
*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
  pronameas "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$;