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
