ne 11. 8. 2024 v 14:08 odesílatel Heikki Linnakangas <hlinn...@iki.fi> napsal:
> On 11/08/2024 12:41, Pavel Stehule wrote: > > ne 11. 8. 2024 v 9:23 odesílatel Ayush Vatsa <ayushvatsa1...@gmail.com > > <mailto:ayushvatsa1...@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. > +1 The SPI API is not difficult, and this looks like best option Regards Pavel > -- > Heikki Linnakangas > Neon (https://neon.tech) > >
