Restricting Direct Access to a C Function in PostgreSQL

Hi

ne 11. 8. 2024 v 9:23 odesílatel Ayush Vatsa <ayushvatsa1810@gmail.com>
napsal:

Hi PostgreSQL Community,

I have a scenario where I am working with two functions: one in SQL and
another in C, where the SQL function is a wrapper around C function. Here’s
an example:

CREATE OR REPLACE FUNCTION my_func(IN input text)RETURNS BIGINT AS $$DECLARE
result BIGINT;BEGIN
SELECT col2 INTO result FROM my_func_extended(input);
RETURN result;END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION my_func_extended(
IN input text,
OUT col1 text,
OUT col2 BIGINT
)RETURNS SETOF recordAS 'MODULE_PATHNAME', 'my_func_extended'LANGUAGE C STRICT PARALLEL SAFE;

I need to prevent direct execution of my_func_extended from psql while
still allowing it to be called from within the wrapper function my_func.

I’m considering the following options:

1. Using GRANT/REVOKE in SQL to manage permissions.
2. Adding a check in the C function to allow execution only if my_func
is in the call stack (previous parent or something), and otherwise throwing
an error.

Is there an existing approach to achieve this, or would you recommend a
specific solution?

You can use fmgr hook, and hold some variable as gate if your function
my_func_extended can be called

https://pgpedia.info/f/fmgr_hook.html

With this option, the execution of my_func_extended will be faster, but all
other execution will be little bit slower (due overhead of hook). But the
code probably will be more simpler than processing callback stack.

plpgsql_check uses fmgr hook, and it is working well - just there can be
some surprises, when the hook is activated in different order against
function's execution, and then the FHET_END can be executed without related
FHET_START.

Regards

Pavel

On 11/08/2024 12:41, Pavel Stehule wrote:

ne 11. 8. 2024 v 9:23 odesílatel Ayush Vatsa <ayushvatsa1810@gmail.com
<mailto:ayushvatsa1810@gmail.com>> napsal:

Hi PostgreSQL Community,

I have a scenario where I am working with two functions: one in SQL
and another in C, where the SQL function is a wrapper around C
function. Here’s an example:

|CREATE OR REPLACE FUNCTION my_func(IN input text) RETURNS BIGINT AS
$$ DECLARE result BIGINT; BEGIN SELECT col2 INTO result FROM
my_func_extended(input); RETURN result; END; $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION my_func_extended( IN input text, OUT col1
text, OUT col2 BIGINT ) RETURNS SETOF record AS 'MODULE_PATHNAME',
'my_func_extended' LANGUAGE C STRICT PARALLEL SAFE; |

I need to prevent direct execution of |my_func_extended| from psql
while still allowing it to be called from within the wrapper
function |my_func|.

I’m considering the following options:

1. Using GRANT/REVOKE in SQL to manage permissions.
2. Adding a check in the C function to allow execution only if
|my_func| is in the call stack (previous parent or something),
and otherwise throwing an error.

Is there an existing approach to achieve this, or would you
recommend a specific solution?

You can use fmgr hook, and hold some variable as gate if your function
my_func_extended can be called

https://pgpedia.info/f/fmgr_hook.html
<https://pgpedia.info/f/fmgr_hook.html&gt;

With this option, the execution of my_func_extended will be faster, but
all other execution will be little bit slower (due overhead of hook).
But the code probably will be more simpler than processing callback stack.

plpgsql_check uses fmgr hook, and it is working well - just there can be
some surprises, when the hook is activated in different order against
function's execution, and then the FHET_END can be executed without
related FHET_START.

Sounds complicated. I would go with the GRANT approach. Make my_func() a
SECURITY DEFINER function, and revoke access to my_func_extended() for
all other roles.

Another option to consider is to not expose my_func_extended() at the
SQL level in the first place, and rewrite my_func() in C. Dunno how
complicated the logic in my_func() is, if that makes sense.

--
Heikki Linnakangas
Neon (https://neon.tech)

ne 11. 8. 2024 v 14:08 odesílatel Heikki Linnakangas <hlinnaka@iki.fi>
napsal:

On 11/08/2024 12:41, Pavel Stehule wrote:

ne 11. 8. 2024 v 9:23 odesílatel Ayush Vatsa <ayushvatsa1810@gmail.com
<mailto:ayushvatsa1810@gmail.com>> napsal:

Hi PostgreSQL Community,

I have a scenario where I am working with two functions: one in SQL
and another in C, where the SQL function is a wrapper around C
function. Here’s an example:

|CREATE OR REPLACE FUNCTION my_func(IN input text) RETURNS BIGINT AS
$$ DECLARE result BIGINT; BEGIN SELECT col2 INTO result FROM
my_func_extended(input); RETURN result; END; $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION my_func_extended( IN input text, OUT col1
text, OUT col2 BIGINT ) RETURNS SETOF record AS 'MODULE_PATHNAME',
'my_func_extended' LANGUAGE C STRICT PARALLEL SAFE; |

I need to prevent direct execution of |my_func_extended| from psql
while still allowing it to be called from within the wrapper
function |my_func|.

I’m considering the following options:

1. Using GRANT/REVOKE in SQL to manage permissions.
2. Adding a check in the C function to allow execution only if
|my_func| is in the call stack (previous parent or something),
and otherwise throwing an error.

Is there an existing approach to achieve this, or would you
recommend a specific solution?

You can use fmgr hook, and hold some variable as gate if your function
my_func_extended can be called

https://pgpedia.info/f/fmgr_hook.html
<https://pgpedia.info/f/fmgr_hook.html&gt;

With this option, the execution of my_func_extended will be faster, but
all other execution will be little bit slower (due overhead of hook).
But the code probably will be more simpler than processing callback

stack.

plpgsql_check uses fmgr hook, and it is working well - just there can be
some surprises, when the hook is activated in different order against
function's execution, and then the FHET_END can be executed without
related FHET_START.

Sounds complicated. I would go with the GRANT approach. Make my_func() a
SECURITY DEFINER function, and revoke access to my_func_extended() for
all other roles.

Another option to consider is to not expose my_func_extended() at the
SQL level in the first place, and rewrite my_func() in C. Dunno how
complicated the logic in my_func() is, if that makes sense.

+1

The SPI API is not difficult, and this looks like best option

Regards

Pavel

Thanks for the responses.

I would go with the GRANT approach. Make my_func() a

SECURITY DEFINER function, and revoke access to my_func_extended() for
all other roles.
This sounds reasonable, and can be one of the options.

Dunno how

complicated the logic in my_func() is, if that makes sense.
Actually my_func_extended already exists hence I don't want
to touch its C definition, nor wanted to duplicate the logic.

The SPI API is not difficult, and this looks like best option

Sorry didn't understand this part, are you suggesting I can have called
my_func_extended() through SPI inside my_func(), but didnt that also
required
my_func_extended() declaration present in SQL ? And If that is present then
anyone can call my_func_extended() directly.

Regards
Ayush
AWS

ne 11. 8. 2024 v 15:34 odesílatel Ayush Vatsa <ayushvatsa1810@gmail.com>
napsal:

Thanks for the responses.

I would go with the GRANT approach. Make my_func() a

SECURITY DEFINER function, and revoke access to my_func_extended() for
all other roles.
This sounds reasonable, and can be one of the options.

Dunno how

complicated the logic in my_func() is, if that makes sense.
Actually my_func_extended already exists hence I don't want
to touch its C definition, nor wanted to duplicate the logic.

The SPI API is not difficult, and this looks like best option

Sorry didn't understand this part, are you suggesting I can have called
my_func_extended() through SPI inside my_func(), but didnt that also
required
my_func_extended() declaration present in SQL ? And If that is present then
anyone can call my_func_extended() directly.

no, my proposal is write your my_func in C - like Heikki proposes, then
my_func_extended should not be visible from SQL, and then you don't need to
solve this issue.

Understood, Thanks for the help.

Regards
Ayush

Heikki Linnakangas <hlinnaka@iki.fi> writes:

Sounds complicated. I would go with the GRANT approach. Make my_func() a
SECURITY DEFINER function, and revoke access to my_func_extended() for
all other roles.

+1

Another option to consider is to not expose my_func_extended() at the
SQL level in the first place, and rewrite my_func() in C. Dunno how
complicated the logic in my_func() is, if that makes sense.

Another way to think about that is "push down into C the part of
my_func() that you feel is necessary to make my_func_extended()
safely callable". Personally I'd probably change my_func_extended()
itself to do that, but if you feel a need to leave it alone, you
could write a C wrapper function. Anyway my point is you might
not have to move *all* of my_func()'s functionality into C. Think
about what it is exactly that makes you feel it's unsafe to call
my_func_extended() directly.

regards, tom lane