pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
7+ messages / 4 participants
[nested] [flat]
* PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 17:54 Bawol, Brian <brian.bawol@freightverify.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bawol, Brian @ 2019-02-07 17:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
My co-worker and I are both receiving the following error message when
attempting to connect to AWS Postgres 10.4 (and 10.6) database instances
using PgAdmin 4.2:
"ERROR: cannot execute SELECT in a read-only transaction"
I was previously able to connect to these instances until this morning when
I installed PgAdmin 4.2 (upgraded from 4.1.)
I uninstalled PgAdmin 4.2 and re-installed PgAdmin 4.1 and I'm now able to
connect to the read replica database instances again. The same goes for my
co-worker.
Steps to reproduce:
1. Open PgAdmin 4.2 from the Windows Program Menu
2. Select an existing server connection to a read-replica database
instance
3. Receive error "ERROR: cannot execute SELECT in a read-only
transaction"
4. Prompted to enter password with message "Please enter the password
for the user 'xxxxxx' to connect the server - "xxxxxx"
5. Enter the password and I still receive the error "ERROR: cannot
execute SELECT in a read-only transaction"
I've also tried re-creating the connection to the instance from scratch and
receive the same error. I've tried using our admin account as well as a
read-only account and both result in the same error message.
Info for version 4.1 (can connect):
Version 4.1
Python Version: 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 16:07:46) [MSC
v.1900 32 bit (Intel)]
Flask Version: 0.12.4
Application Mode: Desktop
Current User: pgadmin4@pgadmin.org
Info for version 4.2 (can not connect):
Version 4.2
Python Version: 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 16:07:46) [MSC
v.1900 32 bit (Intel)]
Flask Version: 0.12.4
Application Mode: Desktop
Current User: pgadmin4@pgadmin.org
Thanks,
Brian
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 18:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bawol, Brian <brian.bawol@freightverify.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-02-07 18:47 UTC (permalink / raw)
To: Bawol, Brian <brian.bawol@freightverify.com>; +Cc: pgsql-admin@lists.postgresql.org
"Bawol, Brian" <brian.bawol@freightverify.com> writes:
> My co-worker and I are both receiving the following error message when
> attempting to connect to AWS Postgres 10.4 (and 10.6) database instances
> using PgAdmin 4.2:
> "ERROR: cannot execute SELECT in a read-only transaction"
That seems pretty strange. What's the actual underlying query that's
failing? (The server log should show that, as a appendage to the
occurrence of this error, even if PgAdmin won't tell you.)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 19:01 Bawol, Brian <brian.bawol@freightverify.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Bawol, Brian @ 2019-02-07 19:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
It looks like this is the query that occurs at the same time:
STATEMENT: SET DateStyle=ISO; SET client_min_messages=notice;UPDATE
pg_settings SET setting = 'escape' WHERE name = 'bytea_output';SET
client_encoding='UNICODE';
Does this help?
On Thu, Feb 7, 2019 at 1:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Bawol, Brian" <brian.bawol@freightverify.com> writes:
> > My co-worker and I are both receiving the following error message when
> > attempting to connect to AWS Postgres 10.4 (and 10.6) database instances
> > using PgAdmin 4.2:
>
> > "ERROR: cannot execute SELECT in a read-only transaction"
>
> That seems pretty strange. What's the actual underlying query that's
> failing? (The server log should show that, as a appendage to the
> occurrence of this error, even if PgAdmin won't tell you.)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 19:56 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Bawol, Brian <brian.bawol@freightverify.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2019-02-07 19:56 UTC (permalink / raw)
To: Bawol, Brian <brian.bawol@freightverify.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org; pgadmin-support@lists.postgresql.org
On 2019-Feb-07, Bawol, Brian wrote:
> It looks like this is the query that occurs at the same time:
>
> STATEMENT: SET DateStyle=ISO; SET client_min_messages=notice;UPDATE
> pg_settings SET setting = 'escape' WHERE name = 'bytea_output';SET
> client_encoding='UNICODE';
>
> Does this help?
Ah, so pgadmin 4.2 is trying to set bytea_output, which is disallowed;
the reason it says "cannot execute SELECT" is that the UPDATE for the
view is turned into a "SELECT set_config()" by a view rule.
That makes sense. This looks like a pgadmin bug ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 20:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-02-07 20:23 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Bawol, Brian <brian.bawol@freightverify.com>; pgsql-admin@lists.postgresql.org; pgadmin-support@lists.postgresql.org
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Feb-07, Bawol, Brian wrote:
>> It looks like this is the query that occurs at the same time:
>> STATEMENT: SET DateStyle=ISO; SET client_min_messages=notice;UPDATE
>> pg_settings SET setting = 'escape' WHERE name = 'bytea_output';SET
>> client_encoding='UNICODE';
> Ah, so pgadmin 4.2 is trying to set bytea_output, which is disallowed;
> the reason it says "cannot execute SELECT" is that the UPDATE for the
> view is turned into a "SELECT set_config()" by a view rule.
Right, and the transformed query still specifies that pg_settings
should be checked for UPDATE privilege, which is what is making
ExecCheckXactReadOnly spit up on it.
In principle you could argue that ExecCheckXactReadOnly could be smarter
about this type of situation. A probably more significant issue is that a
permissions check is a really strange, and not at all bulletproof-seeming,
way of deciding whether a query is read-only. But I don't have any
burning desire to redesign that right now.
> This looks like a pgadmin bug ...
Looking at it, I'm betting somebody thought this was a cute way to
set bytea_output if it exists, without getting a failure on servers
too old to have it. We added that in 9.0, so maybe it's not quite
old enough to make it a moot point; but failing on RO servers is
not nice either. Anyway, yeah, it's pgadmin's problem to fix.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-07 20:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-02-07 20:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Bawol, Brian <brian.bawol@freightverify.com>; pgsql-admin@lists.postgresql.org; pgadmin-support@lists.postgresql.org
I wrote:
>> This looks like a pgadmin bug ...
> Looking at it, I'm betting somebody thought this was a cute way to
> set bytea_output if it exists, without getting a failure on servers
> too old to have it. We added that in 9.0, so maybe it's not quite
> old enough to make it a moot point; but failing on RO servers is
> not nice either. Anyway, yeah, it's pgadmin's problem to fix.
Better idea:
select set_config('bytea_output','escape',false) from pg_settings where name = 'bytea_output';
This is depressingly lacking in safe schema-qualification, btw.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
@ 2019-02-08 04:24 Ashesh Vashi <ashesh.vashi@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Ashesh Vashi @ 2019-02-08 04:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Bawol, Brian <brian.bawol@freightverify.com>; pgsql-admin@lists.postgresql.org; pgadmin-support@lists.postgresql.org
On Fri, Feb 8, 2019 at 1:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> >> This looks like a pgadmin bug ...
>
> > Looking at it, I'm betting somebody thought this was a cute way to
> > set bytea_output if it exists, without getting a failure on servers
> > too old to have it. We added that in 9.0, so maybe it's not quite
> > old enough to make it a moot point; but failing on RO servers is
> > not nice either. Anyway, yeah, it's pgadmin's problem to fix.
>
> Better idea:
>
> select set_config('bytea_output','escape',false) from pg_settings where
> name = 'bytea_output';
>
Thanks for the solution - Tom.
We will fix that in that in pgAdmin.
-- Thanks, Ashesh
>
> This is depressingly lacking in safe schema-qualification, btw.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-02-08 04:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-07 17:54 PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances Bawol, Brian <brian.bawol@freightverify.com>
2019-02-07 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-02-07 19:01 ` Bawol, Brian <brian.bawol@freightverify.com>
2019-02-07 19:56 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-02-07 20:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-02-07 20:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-02-08 04:24 ` Ashesh Vashi <ashesh.vashi@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox