Re: Show dropped users' backends in pg_stat_activity

Hi,

At Tue, 22 Mar 2016 22:47:16 -0500, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote in <56F211C4(dot)6010103(at)BlueTreble(dot)com>
> On 3/22/16 10:35 PM, Kyotaro HORIGUCHI wrote:
> >> Even if we maintained some interlock for a backend's login role
> >> identity,
> >> >I hardly think it would be practical to e.g. lock during transient SET
> >> >ROLE or security-definer-function-call operations. So it's not like
> >> >we
> >> >can let the permissions system assume that a role OID being inquired
> >> >about
> >> >always matches a live entry in pg_authid.
> > Even if blocking DROPs is not perfect for all cases,
> > unconditionally allowing to DROP a role still doesn't seem proper
> > behavior, especially for replication roles. And session logins
> > seem to me to have enough reason to be treated differently than
> > disguising as another role using SET ROLE or sec-definer.
>
> There's probably a way this could be handled, since DROP ROLE is
> presumably a very uncommon operation. Perhaps something as simple as
> keeping a single OID in shared memory for the role about to be
> dropped. That would serialize role drops, but I doubt that matters.

The OID in shared memory has the same role with a tuple with the
OID in pg_authid in this patch. So it seems need a lock or a
retry mechanism, or we see a message something like this:p

| DROP ROLE: Another role is concurrently being dropped.

> > The attached patch blocks DROP ROLE for roles that own active
> > sessions, and on the other hand prevents a session from being
> > activated if the login role is concurrently dropped.
>
> I think this is fine for now, but... what happens if you drop a role
> that's in use on a streaming replica? Does replay stall or do we just
> ignore it?

It behaves as the same to the ordinary backends. DROP ROLE fails
for any active walsender's session(?) role, or a new walsender
rejects login attempts by the role under being dropped.

> There should probably be some doc changes to go with the patch too,
> no?

Yes, this is a PoC. I'll provide documentation if this is
acceptable, and necessary. "20.4 Dropping Roles" would be
appropriate?

http://www.postgresql.org/docs/9.5/static/role-removal.html

Treating a session as an object dependent on the role could be
cleaner but may be too complex and fragile..

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center