Re: Changing from security definer to security invoker without dropping ?

Laura Smith wrote:

> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On Thursday, 11 June 2020 08:39, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> > Hi
> >
> > čt 11. 6. 2020 v 9:29 odesílatel Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)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