Re: proposal: schema private functions

Hi

I would to introduce new flag for routines - PRIVATE. Routines with this
flag can be called only from other routines assigned with same schema.
Because these routines are not available for top queries, we can hide these
functions from some outputs like \df ..

Example:

CREATE SCHEMA s1;
CREATE SCHEMA s2;

CREATE OR REPLACE FUNCTION s1.nested()
RETURNS int AS $$
BEGIN
RETURN random()*1000;
END;
$$ PRIVATE;

SELECT s1.nested(); -- fails - it is top query

CREATE OR REPLACE FUNCTION s1.fx()
RETURNS int AS $$
BEGIN
RETURN s1.nested();
END;
$$ LANGUAGE plpgsql;

SELECT s1.fx(); -- will work

CREATE OR REPLACE FUNCTION s2.fx()
RETURNS int AS $$
BEGIN
RETURN s1.nested();
END;
$$ LANGUAGE plpgsql;

SELECT s2.fx(); -- fails - it call private function from other schema.

This proposal is simple, and strong enough to separate functions that can
be directly callable and auxiliary functions, that can be called from other
functions.

I wrote PoC implementation, and it is not hard, and it should not to impact
performance. I introduced query_owner_nspid into query environment. When
any functions has flag private, then query_owner_nspid should be same like
function namespace id.

Comments, notes?

Regards

Pavel

2018-08-14 13:56 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> I would to introduce new flag for routines - PRIVATE. Routines with this
> flag can be called only from other routines assigned with same schema.
> Because these routines are not available for top queries, we can hide these
> functions from some outputs like \df ..
>

It can be used for code organization purposes, and can be used for
implementation some security patterns.

The private functions cannot be directly called by user who had not CREATE
right on schema. But these functions can be evaluated under any user who
has a access to schema.

Regards

Pavel

> Example:
>
> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
>
> CREATE OR REPLACE FUNCTION s1.nested()
> RETURNS int AS $$
> BEGIN
> RETURN random()*1000;
> END;
> $$ PRIVATE;
>
> SELECT s1.nested(); -- fails - it is top query
>
> CREATE OR REPLACE FUNCTION s1.fx()
> RETURNS int AS $$
> BEGIN
> RETURN s1.nested();
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT s1.fx(); -- will work
>
> CREATE OR REPLACE FUNCTION s2.fx()
> RETURNS int AS $$
> BEGIN
> RETURN s1.nested();
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT s2.fx(); -- fails - it call private function from other schema.
>
> This proposal is simple, and strong enough to separate functions that can
> be directly callable and auxiliary functions, that can be called from other
> functions.
>
> I wrote PoC implementation, and it is not hard, and it should not to
> impact performance. I introduced query_owner_nspid into query environment.
> When any functions has flag private, then query_owner_nspid should be same
> like function namespace id.
>
> Comments, notes?
>
> Regards
>
> Pavel
>
>

2018-08-14 14:00 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2018-08-14 13:56 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>> Hi
>>
>> I would to introduce new flag for routines - PRIVATE. Routines with this
>> flag can be called only from other routines assigned with same schema.
>> Because these routines are not available for top queries, we can hide these
>> functions from some outputs like \df ..
>>
>
> It can be used for code organization purposes, and can be used for
> implementation some security patterns.
>
> The private functions cannot be directly called by user who had not CREATE
> right on schema. But these functions can be evaluated under any user who
> has a access to schema.
>

Same mechanism can be used for SCHEMA VARIABLES - so we can have private
schema variables, that can be used only from functions from same schema.
This can be interesting security mechanism how to run more code without
security definer flag.

Regards

Pavel

> Regards
>
> Pavel
>
>
>> Example:
>>
>> CREATE SCHEMA s1;
>> CREATE SCHEMA s2;
>>
>> CREATE OR REPLACE FUNCTION s1.nested()
>> RETURNS int AS $$
>> BEGIN
>> RETURN random()*1000;
>> END;
>> $$ PRIVATE;
>>
>> SELECT s1.nested(); -- fails - it is top query
>>
>> CREATE OR REPLACE FUNCTION s1.fx()
>> RETURNS int AS $$
>> BEGIN
>> RETURN s1.nested();
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> SELECT s1.fx(); -- will work
>>
>> CREATE OR REPLACE FUNCTION s2.fx()
>> RETURNS int AS $$
>> BEGIN
>> RETURN s1.nested();
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> SELECT s2.fx(); -- fails - it call private function from other schema.
>>
>> This proposal is simple, and strong enough to separate functions that can
>> be directly callable and auxiliary functions, that can be called from other
>> functions.
>>
>> I wrote PoC implementation, and it is not hard, and it should not to
>> impact performance. I introduced query_owner_nspid into query environment.
>> When any functions has flag private, then query_owner_nspid should be same
>> like function namespace id.
>>
>> Comments, notes?
>>
>> Regards
>>
>> Pavel
>>
>>
>

Couldn't this be handled by having a new permission on FUNCTIONs
("CALL"?) to distinguish EXECUTE?

This would have to be made backwards-compatible, possibly by
automatically granting CALL ON ALL FUNCTIONS to public at schema create
time and/or PG upgrade time, which the schema owner could then REVOKE,
or by requiring CALL permission only functions marked PRIVATE.

Because initially a schema has no functions, the owner can revoke this
grant to public before creating any functions, thus there would be no
race contition. A race condition could be made less likely still by
having CALL not imply EXECUTE (users would have to have both to
successfully call a given function).

I would agree that a PRIVATE keyword would be a syntactically convenient
way to say that in its absence then public gets granted CALL on the
given function. But IMO it shouldn't be necessary, and either way
permissions machinery should be involved.

What do other SQL databases do? Does any have a PRIVATE keyword for
FUNCTIONs?

Using permissions has the net effect of making visibility more
fine-grained.

Regarding \df, I'm not sure that hiding function names one cannot call
is worthwhile, but if it were, then there are several options depending
on whether confidentiality of function names is to be a security
feature: RLS on the pg_catalog.pg_proc table (provides confidentiality),
or having a system view on it or filtering in psql (no real
confidentiality).

All that said, being able to have PRIVATE schemas, tables, views,
functions, FDWs, variables, maybe even roles, is definitly appealing,
mainly for code organization reasons.

I didn't understand how PRIVATE would help reduce the need for SECURITY
DEFINER. Can you explain?

Nico
--

2018-08-23 17:18 GMT+02:00 Nico Williams <nico(at)cryptonector(dot)com>:

> Couldn't this be handled by having a new permission on FUNCTIONs
> ("CALL"?) to distinguish EXECUTE?
>

I don't understand to this questions. Private functions will be functions
still - there is not necessity to call these functions differently.

> This would have to be made backwards-compatible, possibly by
> automatically granting CALL ON ALL FUNCTIONS to public at schema create
> time and/or PG upgrade time, which the schema owner could then REVOKE,
> or by requiring CALL permission only functions marked PRIVATE.
>
> Because initially a schema has no functions, the owner can revoke this
> grant to public before creating any functions, thus there would be no
> race contition. A race condition could be made less likely still by
> having CALL not imply EXECUTE (users would have to have both to
> successfully call a given function).
>
> I would agree that a PRIVATE keyword would be a syntactically convenient
> way to say that in its absence then public gets granted CALL on the
> given function. But IMO it shouldn't be necessary, and either way
> permissions machinery should be involved.
>
> What do other SQL databases do? Does any have a PRIVATE keyword for
> FUNCTIONs?
>

The private flag, what I propose, has not relations to access rights - the
limiting factor is scope. The system of access rights is not touched by
this feature.

The other databases has not PRIVATE keyword, what I know - but have some
different mechanism how to hide some internal API

PL/SQL package functions are by default "private" if are not mentioned in
package header.

> Using permissions has the net effect of making visibility more
> fine-grained.
>
> Regarding \df, I'm not sure that hiding function names one cannot call
> is worthwhile, but if it were, then there are several options depending
> on whether confidentiality of function names is to be a security
> feature: RLS on the pg_catalog.pg_proc table (provides confidentiality),
> or having a system view on it or filtering in psql (no real
> confidentiality).
>

Probably private objects (functions) should not be visible in \df .. maybe
\dfP or some similar can be used.

>
> All that said, being able to have PRIVATE schemas, tables, views,
> functions, FDWs, variables, maybe even roles, is definitly appealing,
> mainly for code organization reasons.
>
> I didn't understand how PRIVATE would help reduce the need for SECURITY
> DEFINER. Can you explain?
>

sure. Any object marked as private - is not accessible directly .. You
want to protect these objects against access in wrong order, .. but
sometimes you should to use data (or features) of these objects. Now, you
should to use security definer functions and any user can call these
functions, and then the access to protected objects is controlled. But you
should to use security definer functions. When you use private flag, then
this object can be accessed only from function (procedure, ...) from same
schema. And if some user doesn't create right for schema, then he hasn't
free access to private objects. But this user can use any non private
objects from the schema, if has necessary rights, and then the access to
private objects is controlled and security definer functions are not
necessary.

> Nico
> --
>