Re: Security Definer functions no longer works in PG14+

Thanks, Jan,
Usage of dblink is a good workaround. Thanks for that.

On Fri, May 6, 2022 at 12:02 AM Jan Katins <jasc(at)gmx(dot)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(at)gmail(dot)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*