Security Definer functions no longer works in PG14+

From: Jobin Augustine <jobinau(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Security Definer functions no longer works in PG14+
Date: 2022-05-05 15:49:03
Message-ID: CANaTPsphRF+7k+YANMv8goGu3oQLY9XtACpkec8Ju=mr59GHGA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jan Katins 2022-05-05 18:31:58 Re: Security Definer functions no longer works in PG14+
Previous Message Tom Lane 2022-05-05 15:12:36 Re: BUG #17474: Segmentation fault from INSERT ( JumbleExpr )