From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Bawol, Brian <brian.bawol@freightverify.com>
Cc: pgsql-admin@lists.postgresql.org
Cc: pgadmin-support@lists.postgresql.org
Subject: Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
Date: Thu, 07 Feb 2019 15:23:21 -0500
Message-ID: <25216.1549571001@sss.pgh.pa.us> (raw)
In-Reply-To: <20190207195605.GA14960@alvherre.pgsql>
References: <20190207195605.GA14960@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, brian.bawol@freightverify.com, pgsql-admin@lists.postgresql.org, pgadmin-support@lists.postgresql.org
Subject: Re: PgAdmin 4.2 unable to connect to AWS Postgres read-replica instances
In-Reply-To: <25216.1549571001@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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