Postgres Pro
Downloads
Home   >   mailing lists

Thread: Security Definer functions no longer works in PG14+

Security Definer functions no longer works in PG14+

From
Jobin Augustine
Date:
Hello Community and Hackers,

A function like:

CREATE OR REPLACE  FUNCTION fn_sql_refresh() RETURNS void AS $$
    ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
$$ LANGUAGE SQL SECURITY DEFINER;

Works with PostgreSQL 13 and older versions.
But gives error on PostgreSQL 14 as follows:

postgres=> select fn_sql_refresh();
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL function "fn_sql_refresh" statement 1


But it is hurting good operational use cases of logical replication.

Regards,
Jobin.

Re: Security Definer functions no longer works in PG14+

From
Jan Katins
Date:
Hi,

The aiven-extras repo has a workaround for that, using dblink: https://github.com/aiven/aiven-extras/commit/eb8c1107ca91a7da5ecb0c8127c94ce42762881d

Jan

On Thu, 5 May 2022, 17:49 Jobin Augustine, <jobinau@gmail.com> wrote:
Hello Community and Hackers,

A function like:

CREATE OR REPLACE  FUNCTION fn_sql_refresh() RETURNS void AS $$
    ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
$$ LANGUAGE SQL SECURITY DEFINER;

Works with PostgreSQL 13 and older versions.
But gives error on PostgreSQL 14 as follows:

postgres=> select fn_sql_refresh();
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL function "fn_sql_refresh" statement 1


But it is hurting good operational use cases of logical replication.

Regards,
Jobin.

Re: Security Definer functions no longer works in PG14+

From
Jobin Augustine
Date:
Thanks, Jan,
Usage of dblink is a good workaround. Thanks for that.

On Fri, May 6, 2022 at 12:02 AM Jan Katins <jasc@gmx.net> wrote:
Hi,

The aiven-extras repo has a workaround for that, using dblink: https://github.com/aiven/aiven-extras/commit/eb8c1107ca91a7da5ecb0c8127c94ce42762881d

Jan

On Thu, 5 May 2022, 17:49 Jobin Augustine, <jobinau@gmail.com> wrote:
Hello Community and Hackers,

A function like:

CREATE OR REPLACE  FUNCTION fn_sql_refresh() RETURNS void AS $$
    ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
$$ LANGUAGE SQL SECURITY DEFINER;

Works with PostgreSQL 13 and older versions.
But gives error on PostgreSQL 14 as follows:

postgres=> select fn_sql_refresh();
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL function "fn_sql_refresh" statement 1


But it is hurting good operational use cases of logical replication.

Regards,
Jobin.


--
Thanks and Regards,
Jobin Augustine
PostgreSQL Escalation Specialist

Re: Security Definer functions no longer works in PG14+

From
Andrew Borodin
Date:
On Thu, May 5, 2022 at 11:32 PM Jan Katins <jasc@gmx.net> wrote:
>
> The aiven-extras repo has a workaround for that, using dblink:
https://github.com/aiven/aiven-extras/commit/eb8c1107ca91a7da5ecb0c8127c94ce42762881d

> SECURITY DEFINER
> pg_catalog.format('ALTER SUBSCRIPTION %I REFRESH PUBLICATION WITH (copy_data=%s)', arg_subscription_name,
arg_copy_data::TEXT)

Doesn't this constitute Bobby-tables SQL injection?

Best regards, Andrey Borodin.

Re: Security Definer functions no longer works in PG14+

From
"David G. Johnston"
Date:


On Thursday, May 5, 2022, Andrew Borodin <amborodin86@gmail.com> wrote:
On Thu, May 5, 2022 at 11:32 PM Jan Katins <jasc@gmx.net> wrote:
>
> The aiven-extras repo has a workaround for that, using dblink: https://github.com/aiven/aiven-extras/commit/eb8c1107ca91a7da5ecb0c8127c94ce42762881d

> SECURITY DEFINER
> pg_catalog.format('ALTER SUBSCRIPTION %I REFRESH PUBLICATION WITH (copy_data=%s)', arg_subscription_name, arg_copy_data::TEXT)

Doesn't this constitute Bobby-tables SQL injection?


How do you suppose the caller of the function gets the passed in boolean, when cast to text, to print anything other than “t” or “f” (null might bork things but still not unsafe)?

The %I handles the name.

David J.

Re: Security Definer functions no longer works in PG14+

From
Andrew Borodin
Date:
On Fri, May 6, 2022 at 11:32 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> The %I handles the name.
>

You are right. I didn't know that %I makes special checking for quotes.

Best regards, Andrey Borodin.

Re: Security Definer functions no longer works in PG14+

From
Pavel Stehule
Date:


pá 6. 5. 2022 v 8:51 odesílatel Andrew Borodin <amborodin86@gmail.com> napsal:
On Fri, May 6, 2022 at 11:32 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> The %I handles the name.
>

You are right. I didn't know that %I makes special checking for quotes.

it provides sanitization - just it is shortcut for qoute_ident

Regards

Pavel
 

Best regards, Andrey Borodin.


Re: Security Definer functions no longer works in PG14+

From
Amit Kapila
Date:
On Fri, May 6, 2022 at 11:07 AM Jobin Augustine <jobin.augustine@percona.com> wrote:
Thanks, Jan,
Usage of dblink is a good workaround. Thanks for that.


Good to know that workaround helps your use case. But I am curious to know why you want to use Alter Subscription .. Refresh via function? The same restriction holds for Create/Drop Subscription as well but you don't seem to be using those via function.

--
With Regards,
Amit Kapila.

Re: Security Definer functions no longer works in PG14+

From
Jobin Augustine
Date:

Hi Amit,

Good to know that workaround helps your use case. But I am curious to know why you want to use Alter Subscription .. Refresh via function? The same restriction holds for Create/Drop Subscription as well but you don't seem to be using those via function.

Yes, the workaround really helps to continue the operations as it was.
let me explain.

In a typical operations case, owner / someone with superuser privilege sets up everything as part of the deployment and then hands over the day-to-day operations of different teams who do 24x7 coverage.
Those teams in regular operations support won't be given superuser privilege or owner account because of obvious reasons.
A function with "SECURITY DEFINER" is generally used as a method to hand over only the required privilege just to refresh the subscription.

Thanks and Regards,
Jobin.

Re: Security Definer functions no longer works in PG14+

From
Amit Kapila
Date:
On Sun, May 8, 2022 at 12:33 PM Jobin Augustine
<jobin.augustine@percona.com> wrote:
>
>
>> Good to know that workaround helps your use case. But I am curious to know why you want to use Alter Subscription ..
Refreshvia function? The same restriction holds for Create/Drop Subscription as well but you don't seem to be using
thosevia function.
 
>>
> Yes, the workaround really helps to continue the operations as it was.
> let me explain.
>
> In a typical operations case, owner / someone with superuser privilege sets up everything as part of the deployment
andthen hands over the day-to-day operations of different teams who do 24x7 coverage.
 
> Those teams in regular operations support won't be given superuser privilege or owner account because of obvious
reasons.
> A function with "SECURITY DEFINER" is generally used as a method to hand over only the required privilege just to
refreshthe subscription.
 
>

Okay, thanks for the clarification.

-- 
With Regards,
Amit Kapila.