From: | Les <nagylzs(at)gmail(dot)com> |
---|---|
To: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Hot standby writable? |
Date: | 2022-04-28 11:06:49 |
Message-ID: | CAKXe9UAB_0o-+RM=UR7YGwqPo+SXfGPS1wourwQAAahUw5rfxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
It returns false. Does it mean that the standby is not streaming the WAL
from the primary?
MichaelDBA <MichaelDBA(at)sqlexec(dot)com> ezt írta (időpont: 2022. ápr. 28., Cs,
12:35):
> 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 <michaelvitale(at)sqlexec(dot)com>
>
> 703-600-9343
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-04-28 11:57:04 | Re: SELECT has_database_privilege('user01', 'db01', 'connect'); |
Previous Message | MichaelDBA | 2022-04-28 10:35:21 | Re: Hot standby writable? |