Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: proposal: schema private functions

Re: proposal: schema private functions

From
Pavel Stehule
Date:


2018-08-14 13:56 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.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


Re: proposal: schema private functions

From
Pavel Stehule
Date:


2018-08-14 14:00 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:


2018-08-14 13:56 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.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