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

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