Making subscribers read only in Postgres 10 logical replication

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

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

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

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