Thread: Restricting Direct Access to a C Function in PostgreSQL

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?

Best regards,
Ayush Vatsa
AWS

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

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