Re: Restricting Direct Access to a C Function in PostgreSQL

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

> On 11/08/2024 12:41, Pavel Stehule wrote:
> > ne 11. 8. 2024 v 9:23 odesílatel Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com
> > <mailto:ayushvatsa1810(at)gmail(dot)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.
>

+1

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

Regards

Pavel

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