Re: Show dropped users' backends in pg_stat_activity

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 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?

There should probably be some doc changes to go with the patch too, no?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com