Re: Proposal to sync SET ROLE and pg_stat_activity

Hi Bernd,

Bernd Helmle wrote:
> --On Mittwoch, August 27, 2008 09:35:03 +0200 Grant Finnemore 
> <grant@guruhut.com> wrote:
> 
>> I have a session pool, where all connections to the database are
>> obtained as a superuser. On issuing connections to the client, we
>> invoke either SET ROLE or SET SESSION AUTHORIZATION and switch to
>> a role with less permissions. This means that we don't have to
>> reserve a connection per user, and we can still use the database
>> access restrictions.
> 
> 
> But you have to ensure that your session pool is smaller than 
> max_connections, since this will eat up superuser_reserved_connections 
> and would make administrator intervention  impossible under certain 
> circumstances.
> 
Yes, but that's the easy part. Any reasonable pooling software allows
you to set max connections.

> And why do you need to hack pg_stat_activity, isn't it possible to plug 
> your own view in?
> 
Well, pg_stat_activity isn't really the problem here, because as you
point out, it's just a view, and I could certainly redefine the view.
The limiting factor is that the backend doesn't push the role name
changes to the stats subsystem for either SET ROLE or SET SESSION
AUTH.

An alternative to changing the current behaviour would be to introduce
new variables in the backend structures that are sent to the stats
subsystem, and which could be read by as yet undefined functions. This
would keep existing behaviour, but allow others to obtain the
alternative behaviour through the creation of a separate view.

Regards,
Grant