Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

[Roman Šindelář <roman.sindelar@xxxxxxxxx>]

Hello,
we prepared a test case to demonstrate our case.

TASK

Update replicated db schema (add new table) without superuser privileges.

PROBLEM

Our steps work in PostgreSQL ver 12 but we get the following error in 15
---
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL statement "alter subscription test_sub REFRESH PUBLICATION"
PL/pgSQL function test.refresh_subscription(character varying) line 4 at EXECUTE
---

QUESTIONS
- ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all PGSQL versions 14+, is it desired behavior?
- Is there any other possibility or a recommendation to solve our case/task?

TEST CASE

Creates a db environment with two databases, two db users and one replicated table. Then we try to add a new table to the publication and without superuser privileges refresh replicated tables.

--
-- ALTER SUBSCRIPTION REFRESH TEST CASE
--
-- DATABASES
-- db1 - source db
-- db2 - destination db (replica)
--
-- USERS
-- postgres - superuser
-- usr_db_repl - user for replication
-- usr_db_deploy - deploy user
--
-- PG_HBA.CONF
-- host    replication     usr_db_repl      localhost     trust
-- host    db1, db2        usr_db_repl      localhost     trust
-- host    db1, db2        usr_db_deploy    localhost     trust
-- local   db1, db2        usr_db_deploy                  trust
--

--
-- PREPARATION TEST ENVIRONMENT
--

-- create users
create user usr_db_repl replication;
create user usr_db_deploy;

-- create databases
create database db1;
grant create on database db1 to usr_db_deploy;
create database db2;
grant create on database db2 to usr_db_deploy;

-- create source table
\c db1 usr_db_deploy
create schema test;
create table test.tab1 (id int primary key, num int);
grant usage on schema test to usr_db_repl;
grant select on table test.tab1 to usr_db_repl;
insert into test.tab1 values (1, 10);
select * from test.tab1;

-- create publication on source side
create publication test_pub for table test.tab1;
select * from pg_publication_tables;

-- create replication slot on source side
\c db1 postgres
select pg_create_logical_replication_slot('test_sub', 'pgoutput');
select * from pg_replication_slots;

-- create table on destination side
\c db2 usr_db_deploy
create schema test;
grant usage on schema test to usr_db_repl;
create table test.tab1 (id int primary key, num int);

-- create subscription on destination side using pre-prepared replication slot
\c db2 postgres
create subscription test_sub connection 'host=localhost port=5432 dbname=db1 user=usr_db_repl' publication test_pub with (slot_name=test_sub, create_slot=false);
select * from pg_subscription;

-- check replicated table
\c db2 usr_db_deploy
select * from test.tab1;

-- create functions with security definer on destination side
\c db2 postgres

create procedure test.disable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' disable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.enable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' enable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.refresh_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' REFRESH PUBLICATION';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

--
-- TEST CASE
-- Note: All steps is run under usr_db_deploy (non-superuser)
-- Task: Add a new table into publisher on source side and replicate the table to destination side
--

-- 1. disable subscrition [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.disable_subscription('test_sub');

-- 2. change: create new table [SOURCE DATABASE]
\c db1 usr_db_deploy
create table test.tab2 (id int primary key, num int);
grant select on table test.tab2 to usr_db_repl;
insert into test.tab2 values (2, 20);
select * from test.tab2;

-- 3. add table into publication [SOURCE DATABASE]
\c db1 usr_db_deploy
alter publication test_pub add table test.tab2;
select * from pg_publication_tables;

-- 4. create new table on destination side [DESTINATION DATABASE]
\c db2 usr_db_deploy
create table test.tab2 (id int primary key, num int);

-- 5. enable subscription [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.enable_subscription('test_sub');

-- 6. check new table [DESTINATION DATABASE]
-- Note: no rows, we need REFRESH PUBLICATION - this will start replication of tables that were added to the subscribed-to publications
\c db2 usr_db_deploy
select * from test.tab2;

-- 7. refresh subscription [DESTINATION DATABASE]
-- Note: must be run as owner of subscription (superuser) but we need run as non-superuser (usr_db_deploy)
\c db2 usr_db_deploy
call test.refresh_subscription('test_sub');

ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL statement "alter subscription test_sub REFRESH PUBLICATION"
PL/pgSQL function test.refresh_subscription(character varying) line 4 at EXECUTE

-- 8. manual fix [DESTINATION DATABASE]
-- Note: run refresh as superuser
\c db2 postgres
alter subscription test_sub refresh publication;
select * from test.tab2;

Thanks for you help,

Roman

On Tue, Nov 28, 2023 at 5:01 PM Roman Šindelář <roman.sindelar@xxxxxxxxx> wrote:

Hello,

we test database migration to new db servers from version 12 to 15 and a problem with logical replication stopped us.

In the current code (PGSQL ver 12), we use a function with SECURITY DEFINER for refreshing subscriptions:
---
DECLARE
BEGIN
   execute 'alter subscription ' || sSubName || 'REFRESH PUBLICATION';
   raise notice 'Subscription % refreshed', sSubName;
END
--- 

The function is called during a deployment of a new version of our application when a deploy service account (NON-SUPERUSER) ENABLE and REFRESH subscriptions at the end of the deploy. 

In version 15, unfortunately, we get the following error:
ERROR: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

I found only one discussion about this problem, where as a workaround is suggested using of dblink (https://postgrespro.com/list/id/CANaTPsphRF+7k+YANMv8goGu3oQLY9XtACpkec8Ju=mr59GHGA@xxxxxxxxxxxxxx#head)

Is there any other possibility or a recommendation to solve this case?

ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all PGSQL versions 14+?

Thanks in advance for your help.
Roman Sindelar