Обсуждение: 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 thebest way to roll this out to production is given that dropping and re-creating functions could potentially cause upstreamclient 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
ALTER FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTION
regards
Pavel
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 FUNCTION > > regards > > 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 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 FUNCTIONpostgres=# alter function foo (int) security invoker;ALTER FUNCTIONregardsPavelThanks 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 FUNCTION > > > > regards > > > > 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