Hot standby writable?

From: Les <nagylzs(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Hot standby writable?
Date: 2022-04-28 09:52:10
Message-ID: CAKXe9UBc2MOSvB0ySoc_v7xw6h=dKAEv19Ai5CX=gRZ+J8J3TQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I have a cluster with a streaming replication primary, and two hot standbys
(PostgreSQL 12.8). Today I connected to one of the standbys, and
accidentally executed a CREATE TABLE statement. To my surprise, it created
the table, and the change was visible on the primary and also on both
standbys. I examined this behaviour, and found the following. If I connect
to the standby using psql cli then the database is read-only, as expected:

test=> create table test(id int8 not null primary key);
ERROR: cannot execute CREATE TABLE in a read-only transaction

But if I connect to the standby using dbeaver, then somehow the database
becomes writable. Dbeaver runs on my computer, the standby runs on a
separate network and the connection is established through an ssh tunnel.
The primary instance is located inside another network (actually in a
different datacenter), and it is behind a VPN. In other words, it is
technically impossible for dbeaver to connect to the primary. And yet
somehow it can make changes in the database.

Am I missing something? Is there a possibility that the hot standby
forwards SQL statements to the primary? I was not aware of such
functionality.

Right now, it imposes a security risk in our environment, so I would really
like to know how this works.

Thanks,

Laszlo

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message linux.il 2022-04-28 09:57:28 Weird GSSAPI error - trying to connect via SSH tunnel
Previous Message William Sescu (Suva) 2022-04-28 09:47:00 SELECT has_database_privilege('user01', 'db01', 'connect');