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

Changing from security definer to security invoker without dropping ?

From
Laura Smith
Date:
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



Re: Changing from security definer to security invoker withoutdropping ?

From
Pavel Stehule
Date:
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

 

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


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

From
Laura Smith
Date:


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.





Re: Changing from security definer to security invoker withoutdropping ?

From
Pavel Stehule
Date:


č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 FUNCTION
postgres=# alter function foo (int) security invoker;
ALTER FUNCTION

regards

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 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