Changing from security definer to security invoker without dropping ?
Hi,
Just curious if there is a way to switch a function from definer to invoker without dropping ?
We're working on improving the security posture by changing functions from definer to invoker, but I'm wondering what the best way to roll this out to production is given that dropping and re-creating functions could potentially cause upstream client hassles ?
Laura
Hi
čt 11. 6. 2020 v 9:29 odesílatel Laura Smith <
n5d9xq3ti233xiyif2vp@protonmail.ch> napsal:
Hi,
Just curious if there is a way to switch a function from definer to
invoker without dropping ?
create function foo(a int) returns int as $$ begin return $1; end $$
language plpgsql;
postgres=# alter function foo (int) security definer;
ALTER FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTION
regards
Pavel
Show quoted text
We're working on improving the security posture by changing functions from
definer to invoker, but I'm wondering what the best way to roll this out to
production is given that dropping and re-creating functions could
potentially cause upstream client hassles ?Laura
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, 11 June 2020 08:39, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
čt 11. 6. 2020 v 9:29 odesílatel Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> napsal:
Hi,
Just curious if there is a way to switch a function from definer to invoker without dropping ?
create function foo(a int) returns int as $$ begin return $1; end $$ language plpgsql;
postgres=# alter function foo (int) security definer;
ALTER FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTIONregards
Pavel
Thanks Pavel ! Didn't realise it was that easy.
čt 11. 6. 2020 v 9:51 odesílatel Laura Smith <
n5d9xq3ti233xiyif2vp@protonmail.ch> napsal:
Sent with ProtonMail <https://protonmail.com> Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, 11 June 2020 08:39, Pavel Stehule <pavel.stehule@gmail.com>
wrote:Hi
čt 11. 6. 2020 v 9:29 odesílatel Laura Smith <
n5d9xq3ti233xiyif2vp@protonmail.ch> napsal:Hi,
Just curious if there is a way to switch a function from definer to
invoker without dropping ?create function foo(a int) returns int as $$ begin return $1; end $$
language plpgsql;postgres=# alter function foo (int) security definer;
ALTER FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTIONregards
Pavel
Thanks Pavel ! Didn't realise it was that easy.
It is postgres - things are easy (almost time) or impossible :)
Pavel
Laura Smith wrote:
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, 11 June 2020 08:39, Pavel Stehule <pavel.stehule@gmail.com> wrote:Hi
čt 11. 6. 2020 v 9:29 odesílatel Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> napsal:
Hi,
Just curious if there is a way to switch a function from definer to invoker without dropping ?
create function foo(a int) returns int as $$ begin return $1; end $$ language plpgsql;
postgres=# alter function foo (int) security definer;
ALTER FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTIONregards
Pavel
Thanks Pavel ! Didn't realise it was that easy.
"create or replace function..." with "security invoker"
(or without "security definer" since security invoker
is the default) is probably another way to do it, but
it would be slower than "alter function" since it needs
to parse the code again. That might matter if you have
many functions to change.
Bear in mind that things might break with such a change.
There might be a reason that the functions needed to be
created as security definers. I'd recommend checking
each function's need to be a security definer before
changing it (or just test it thoroughly somewhere).
cheers,
raf