Security Definer functions no longer works in PG14+

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:

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

I believe, this is a consequence of :

https://git.postgresql.org/gitweb/?p=postgresql.git;h=ce0fdbfe9722867b7fad4d3ede9b6a6bfc51fb4e

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

Regards,
Jobin.

--
Thanks and Regards,
Jobin Augustine

*PostgreSQL Escalation Specialist*

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.

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.

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.

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

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.

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.

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

Okay, thanks for the clarification.

--
With Regards,
Amit Kapila.