Thread: [GENERAL] Making subscribers read only in Postgres 10 logical replication
Hi I'm testing out logical replication on PostgreSQL 10. Is there a setting to make subscribers read-only slaves like with Slony. Currently I can insert into the Publisher and the Subscriber. If there is a conflict, i.e. same record exists in both, then all replication gets backed up (even to other tables) till that one record is resolved. Thanks RV -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making subscribers read only in Postgres 10 logicalreplication
From
"Joshua D. Drake"
Date:
On 10/11/2017 11:18 AM, rverghese wrote: > Hi > I'm testing out logical replication on PostgreSQL 10. Is there a setting to > make subscribers read-only slaves like with Slony. Currently I can insert > into the Publisher and the Subscriber. If there is a conflict, i.e. same > record exists in both, then all replication gets backed up (even to other > tables) till that one record is resolved. GRANT? JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://pgconf.us ***** Unless otherwise stated, opinions are my own. ***** -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
You mean at the user permissions level? Yes, I could, but would mean doing so table by table, which is not our current structure. I guess there is nothing at the database level. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication
From
Feike Steenbergen
Date:
On 11 October 2017 at 20:38, rverghese <riyav@hotmail.com> wrote:
> I guess there is nothing at the database level.
Although not safe (as the user can reset this parameter), you could set
default_transaction_read_only for the application user.
postgres=# ALTER USER jdoe IN DATABASE postgres SET default_transaction_read_only TO true;
ALTER ROLE
postgres=# \c postgres jdoe
You are now connected to database "postgres" as user "jdoe".
postgres=> CREATE TABLE t1(i int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
regards,
Feike
Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication
From
Thomas Kellerer
Date:
rverghese schrieb am 11.10.2017 um 20:38: > You mean at the user permissions level? Yes, I could, but would mean doing so > table by table, which is not our current structure. I guess there is nothing > at the database level. Not at the database level, but at the schema level: You can revoke those privileges for all tables in a schema: revoke insert,update,delete on all tables in schema public from the_user; You can do that for all future tables as well: alter default privileges in schema public revoke insert,update,delete on tables from the_user; Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making subscribers read only in Postgres 10 logicalreplication
From
Peter Eisentraut
Date:
On 10/11/17 14:18, rverghese wrote: > I'm testing out logical replication on PostgreSQL 10. Is there a setting to > make subscribers read-only slaves like with Slony. Currently I can insert > into the Publisher and the Subscriber. If there is a conflict, i.e. same > record exists in both, then all replication gets backed up (even to other > tables) till that one record is resolved. Right now there is no direct way to do that. The other answers have suggested some workarounds. It might be a valuable feature to implement something like that. One would just have to think through exactly how to present this in the user interface. Another longer-term solution here is to implement conflict resolution mechanisms. So if you don't like local updates to break the incoming replication stream, a remote-update-wins policy would help. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general