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:
This must of course be run on the logical replication primary server.