Re: Hot standby writable?

Before executing the write command through Dbeaver, run this query first:

select pg_is_in_recovery()

Les wrote on 4/28/2022 5:52 AM:
> 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
>

Regards,

Michael Vitale

Michaeldba(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343