pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17487: Parallel execution fails when original user is removed
3+ messages / 3 participants
[nested] [flat]
* BUG #17487: Parallel execution fails when original user is removed
@ 2022-05-19 18:46 PG Bug reporting form <noreply@postgresql.org>
2022-05-19 20:47 ` Re: BUG #17487: Parallel execution fails when original user is removed Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2022-05-19 18:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kravtsov.k@gmail.com
The following bug has been logged on the website:
Bug reference: 17487
Logged by: Kirill Kravtsov
Email address: kravtsov.k@gmail.com
PostgreSQL version: 14.3
Operating system: Centos 7
Description:
We're using temporary credentials to access the DB. The temporary
credentials are granted a role and automatically become that role on
connect. As soon as temporary credential is removed while the connection is
still alive, any regular query would continue to work, however, a parallel
query would cause an error:
ERROR: role with OID XXXXXX does not exist
CONTEXT: parallel worker
Reproduction steps:
Session 1 (postgres):
$ psql -U postgres
create role testparallelrole;
create role testparalleluser with login password '1';
grant testparallelrole to testparalleluser;
alter role testparalleluser set role testparallelrole;
Session 2 (testparalleluser):
$ psql -U testparalleluser -d postgres;
show role; -- shows testparallelrole
set force_parallel_mode TO 1;
select count(*) from pg_class ; -- ok
Session 1 (postgres):
drop role testparalleluser;
Session 2 (testparalleluser):
set force_parallel_mode TO 0;
select count(*) from pg_class ; -- ok
set force_parallel_mode TO 1;
select count(*) from pg_class ; -- fails
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17487: Parallel execution fails when original user is removed
2022-05-19 18:46 BUG #17487: Parallel execution fails when original user is removed PG Bug reporting form <noreply@postgresql.org>
@ 2022-05-19 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-05-19 23:08 ` Re: BUG #17487: Parallel execution fails when original user is removed Kirill Kravtsov <kravtsov.k@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2022-05-19 20:47 UTC (permalink / raw)
To: kravtsov.k@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> We're using temporary credentials to access the DB. The temporary
> credentials are granted a role and automatically become that role on
> connect. As soon as temporary credential is removed while the connection is
> still alive, any regular query would continue to work, however, a parallel
> query would cause an error:
> ERROR: role with OID XXXXXX does not exist
> CONTEXT: parallel worker
I don't think this is particularly a bug. You are relying on an
undocumented and un-guaranteed implementation artifact that it's
possible to drop a role at all while it's still in use in some
session. If we were to make any effort in this area, the end result
would certainly be that the DROP ROLE would be rejected, not that the
parallel query case would start to work. One reason why we'd want
to go in that direction is that there are almost certainly many other
cases where such an "orphaned" session misbehaves.
(Doing that has in fact been discussed, but nothing's been done
about it so far AFAIK.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17487: Parallel execution fails when original user is removed
2022-05-19 18:46 BUG #17487: Parallel execution fails when original user is removed PG Bug reporting form <noreply@postgresql.org>
2022-05-19 20:47 ` Re: BUG #17487: Parallel execution fails when original user is removed Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-05-19 23:08 ` Kirill Kravtsov <kravtsov.k@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Kirill Kravtsov @ 2022-05-19 23:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hey Tom,
Thanks for the response. I agree that this implementation looks shady, and
I would rather have postgres to disallow role drops in such situations.
Currently, the same behaviour can be observed even without the "proxy" role
that I used in my example. I think that would be consistent with the way
SQLServer/Oracle deals with it - not allowing the context to be removed
while in use.
Kirill.
On Thu, May 19, 2022 at 1:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > We're using temporary credentials to access the DB. The temporary
> > credentials are granted a role and automatically become that role on
> > connect. As soon as temporary credential is removed while the connection
> is
> > still alive, any regular query would continue to work, however, a
> parallel
> > query would cause an error:
> > ERROR: role with OID XXXXXX does not exist
> > CONTEXT: parallel worker
>
> I don't think this is particularly a bug. You are relying on an
> undocumented and un-guaranteed implementation artifact that it's
> possible to drop a role at all while it's still in use in some
> session. If we were to make any effort in this area, the end result
> would certainly be that the DROP ROLE would be rejected, not that the
> parallel query case would start to work. One reason why we'd want
> to go in that direction is that there are almost certainly many other
> cases where such an "orphaned" session misbehaves.
>
> (Doing that has in fact been discussed, but nothing's been done
> about it so far AFAIK.)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-05-19 23:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-19 18:46 BUG #17487: Parallel execution fails when original user is removed PG Bug reporting form <noreply@postgresql.org>
2022-05-19 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-05-19 23:08 ` Kirill Kravtsov <kravtsov.k@gmail.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