< Back to blog home Fujitsu PostgreSQL blog

Logical replication permissions in PostgreSQL 15

Ajin Cherian | April 27, 2023

PostgreSQL 15 introduced safeguards to logical replication in order to ensure that the owner of the subscription has permissions to complete the required operation.

Introduction

Previously, the subscription's APPLY process would run with the privileges of a superuser but now with PostgreSQL 15, it will run with the privileges of the subscription owner.

Background

Currently, only superusers are allowed to create SUBSCRIPTIONS.

Prior to PostgreSQL 15, if a SUBSCRIPTION was created by a superuser who later was demoted to a non-superuser, the subscription apply workers and tablesync workers would continue to apply the logical replication changes with superuser force. However, continuing to apply changes assuming superuser permissions would be a security violation.

Feature summary

PostgreSQL 15 makes sure that as part of logical replication on the subscriber, each INSERT, UPDATE, DELETE, TRUNCATE, or COPY command on the apply worker or the tablesync worker checks that the owner of the subscription has permissions to complete the required operation. If the owner of the subscription does not have the necessary permissions, then the operation fails with an error.

This permission checking is done when each transaction is applied. If a worker is in the process of applying changes for a transaction when the ownership of the subscription is changed by a concurrent transaction, the application of the current transaction will continue under the old owner's privileges.

Tables can have row-level-security (RLS) policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. After these PostgreSQL 15 permission changes, subscription owners are no longer able to circumvent RLS by replicating into tables with RLS policies which the subscription owner is subject to. This restriction of replication into tables with RLS policies happens without regard to whether the policy would ordinarily allow the INSERT, UPDATE, DELETE, or TRUNCATE which is being replicated. Regardless of which particular operation is restricted by the RLS policy, all replication to the table with RLS policy on the owner of the subscription is forbidden. Superusers, roles with ‘bypassrls’, and target table owners are able to replicate despite RLS policies.

Examples

Here is an example of a subscription failing to apply changes if the superuser who created the SUBSCRIPTION was later demoted to a non-superuser.

Create roles "alice" and "bob" as SUPERUSER both on the subscriber and publisher and create some tables using those roles.

Publisher and Subscriber:

postgres=# CREATE ROLE alice SUPERUSER LOGIN; 
CREATE ROLE 
postgres=# CREATE ROLE bob SUPERUSER LOGIN; 
CREATE ROLE
postgres=#    
postgres=# GRANT CREATE ON DATABASE postgres TO alice; 
GRANT 
postgres=# GRANT CREATE ON DATABASE postgres TO bob; 
GRANT
postgres=# SET SESSION AUTHORIZATION alice; 
SET 
postgres=# CREATE TABLE alice_table (i INTEGER); 
CREATE TABLE 
postgres=# ALTER TABLE alice_table REPLICA IDENTITY FULL; 
ALTER TABLE 
postgres=# GRANT SELECT ON TABLE alice_table TO alice; 
GRANT 
postgres=# SET SESSION AUTHORIZATION bob; 
SET 
postgres=# CREATE TABLE bob_table (i INTEGER); 
CREATE TABLE 
postgres=# ALTER TABLE bob_table REPLICA IDENTITY FULL; 
ALTER TABLE 
postgres=# GRANT SELECT ON TABLE bob_table TO bob; 
GRANT

Create a PUBLICATION FOR ALL TABLES on the publisher using the role "alice".

Publisher:

postgres=# CREATE PUBLICATION alice_pub FOR ALL TABLES;
CREATE PUBLICATION

Create a SUBSCRIPTION on the subscriber using the role "alice".

Subscriber:

postgres=# SET SESSION AUTHORIZATION alice; 
SET 
postgres=# CREATE SUBSCRIPTION alice_sub CONNECTION 'dbname=postgres host=localhost port=6972' PUBLICATION alice_pub; 
NOTICE:  created replication slot "alice_sub" on publisher
CREATE SUBSCRIPTION

Insert data on the tables on the publisher and see that it is replicated on the subscriber

Publisher:

postgres=# INSERT INTO alice_table  VALUES (1);
INSERT 0 1
postgres=# SELECT * FROM alice_table ;
 i 
---
 1
(1 row)
postgres=# INSERT INTO bob_table VALUES (1);
INSERT 0 1
postgres=# SELECT * FROM bob_table ;
 i 
---
 1
(1 row)

Subscriber:

postgres=# SELECT * FROM alice_table;
 i 
---
 1
(1 row)
postgres=# SELECT * FROM bob_table ;
 i 
---
 1
(1 row)

Now remove superuser permissions for alice, who is the owner of the subscription, on the subscriber.

Subscriber:

postgres=# ALTER ROLE alice NOSUPERUSER;
ALTER ROLE

Insert data into bob_table on the publisher:

Publisher:

postgres=# INSERT INTO bob_table  VALUES (2);
INSERT 0 1
postgres=# SELECT * FROM bob_table;
 i 
---
 1
 2
(2 rows)

See that the data is not reflected on the subscriber and you see errors in logs saying permission denied:

Subscriber:

postgres=# SELECT * FROM bob_table ;
 i 
---
1
(1 row)
logs:
2023-03-27 06:21:43.759 EDT [4746] ERROR:  permission denied for table bob_table
2023-03-27 06:21:43.759 EDT [4746] CONTEXT:  processing remote data for replication origin "pg_16408" during message
type "INSERT" for replication target relation "public.bob_table" in transaction 759, finished at 0/1581580
2023-03-27 06:21:43.760 EDT [6690] LOG:  background worker "logical replication worker" (PID 4746)
exited with exit code 1

This error is because the role "alice" (who is the owner of the subscription) no longer has privileges to write to the table bob_table as the role no longer has superuser privileges.

For the future

The plan going ahead is to allow SUBSCRIPTION to be created by non-superuser roles and allow logical replication within the permissions of that role. This will allow more flexibility to users of PostgreSQL to avoid superuser roles. If all logical replication is done by superuser, then it is possible for a rogue user to create a trigger function on one of its tables and execute rogue code as superuser.

References

• Commit - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a2ab9c06ea15fbcb2bfde570986a06b37f52bcca

Fujitsu Enterprise Postgres
is an enhanced distribution of PostgreSQL, 100% compatible and with extended features.

Compare the list of features.

Compare >

We also have a series of technical articles for PostgreSQL enthusiasts of all stripes, with tips and how-to's.