ERROR: could not get table list: ERROR: permission denied for view tables

[dbaparesh]

I am trying to setup schema level replication. All steps went through without any problem, however, when setting up subscriber on replica getting below error -

2021-12-06 17:05:49.218 PST [21888] LOG: manager worker [21888] at slot 1 generation 71 detaching cleanly 2021-12-06 17:05:49.284 PST [21886] ERROR: could not get table list: ERROR: permission denied for view tables

2021-12-06 17:05:49.284 PST [21886] LOG: apply worker [21886] at slot 2 generation 19 exiting with error 2021-12-06 17:05:49.286 PST [20224] LOG: background worker "pglogical apply 16906:51082904" (PID 21886) exited with exit code 1

I tried granting all privileges and usage on public to replication user which is created as superuser on source and target but doesn't seem to help.

Appreciate if anyone can point me to right direction. Thank you!

[sandeepkalra]

I am not an expert on pglogical, but I think that pglogical does not support DDL, views and few other things.

[dbaparesh]

@sandeepkalra The schemas I am trying to replicate don't have anything other 1 table in each. To provide more context on setup, replication I am trying to setup is from user created database and not from default db "postgres".

[sjuls]

@dbaparesh we've encountered the same issue - our problem was that the pglogical extension wasn't added to the database we wanted to replicate, we added it to the postgres database, but wanted to replicate tables in another database.

In dsn the dbname also needs to point to the database with the pglogical extension that you wish to replicate.

[rorycl]

I'm having the same issue on PostgreSQL 14 unless I give the replication user SUPERUSER privileges.

The error is encountered on setting up a subscription, and reads:

could not get table list: ERROR: permission denied for view tables

I have given the replication user all privileges on the target schema and tables. However there seems to be a requirement to view all tables, perhaps across the entire source or target database?

Presently access is defined on both publisher and subscriber databases as follows:

GRANT ALL ON SCHEMA example TO replication_user;
GRANT ALL ON ALL TABLES IN SCHEMA example TO replication_user;
GRANT USAGE ON SCHEMA pglogical TO replication_user;

(Note that we have explicitly made the pglogical schema before creating the extension.)

I'd be grateful to know what view permissions the replication user needs in addition to the ones I've defined above.

Full log at point of error:

# postgres@publisher LOG:  duration: 31.940 ms  statement: SELECT pglogical.create_subscription(
#   subscription_name => 'db1_sub',
#   provider_dsn => 'host=127.0.0.1 port=5433 dbname=db1 user=logirep password=APASSWORD',
#   replication_sets => ARRAY['db1_pub']
# 
# @publisher LOG:  starting apply for subscription db1_sub
# @postgres LOG:  manager worker [1170447] at slot 1 generation 2 detaching cleanly
# @template1 LOG:  manager worker [1170450] at slot 1 generation 3 detaching cleanly
# @publisher ERROR:  could not get table list: ERROR:  permission denied for view tables
# 
# @publisher LOG:  apply worker [1170446] at slot 2 generation 1 exiting with error
#            LOG:  background worker "pglogical apply 16386:41832884" (PID 1170446) exited with exit code 1
# postgres@publisher LOG:  duration: 0.493 ms  statement: SELECT pglogical.show_subscription_status()
# @publisher LOG:  starting apply for subscription db1_sub
# @publisher ERROR:  subscriber db1_sub initialization failed during nonrecoverable step (d), please try the setup again

[td-gonzales]

I fixed this by connecting to the publisher and dropping the extension then re adding it with the replication user that the subscriber uses to access the node.

I'm not sure this is a bug.