PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances

Bawol, Brian
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: [hidden email]

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: [hidden email]

Thanks,
Brian


Reply | Threaded
Open this post in threaded view
|

Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances

Tom Lane-2
"Bawol, Brian" <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances

Bawol, Brian
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 <[hidden email]> wrote:
"Bawol, Brian" <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances

Ashesh Vashi
In reply to this post by Bawol, Brian
On Fri, Feb 8, 2019 at 1:58 AM Tom Lane <[hidden email]> 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