PostgreSQL – Required Grants for COPY TO STDOUT

postgresql

Context

I want to set up a logical replication between two postgresql instances.

I created a replication user on the master instance :

CREATE ROLE replication WITH REPLICATION PASSWORD '$REPLICATIONPASSWORD' LOGIN;

Then a publication / subscription :

-- on the master instance :
CREATE PUBLICATION mypub FOR TABLES table1, table2, ...

-- on the slave instance :
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=mydb host=localhost port=1234 user=replication password=...' PUBLICATION mypub; 

Issue

In postgres' logs, I see error messages indicating that the replication user can't copy the tables :

-- on the slave side : message repeated for each table
ERROR:  could not start initial contents copy for table "public.table1": ERROR:  permission denied for table table1

-- on the master side : message repeated for each table
ERROR:  permission denied for table table1
STATEMENT:  COPY public.table1 TO STDOUT

Connecting with psql to the master server with the replication user I do see that COPY table1 TO STDOUT is rejected.

Question

What other privileges should I add to the replication user to allow COPY ... TO STDOUT ?

Best Answer

The replication user needs to read the tables:

GRANT SELECT ON table1, table2, ... TO replication;

This must of course be run on the logical replication primary server.