Re: Users, Roles and Connection Pooling

> On Wed, 2019-10-02 at 08:46 +1000, Matt Andrews wrote:
> > Here’s a question I’ve been asking for a while and just can’t find an
> > answer to, so I thought I’d ask it here. The answer could be
> > subjective, but here goes...
> >
> > When a web app connects to Postgres via a connection pooler, what is
> > the best way to manage privileges for the connecting user? Especially
> > when their is a complex hierarchy of privileges?
> >
> > Should each signed up user have their own role which inherits from
> > whichever roles they are members of? This means that the connection
> > pool role should then be granted in every user?
> >
> > Does creating a Postgres role for every app user scale? Roles can
> > only have names, is an email address a good name for individual app
> > users?
> >
> > Are their any resources out there that anyone knows of that addresses
> > these questions?
> >
> > There’s plenty of stuff out there on roles and permissions, I’ve read
> > a lot of it, but not much on dealing with individual app users
> > through a connection pool.
> >
> > I’m thinking that any queries should start with SET ROLE ‘username’
> > and end with RESET ROLE. Is this how it could work?
> >
> > Any help would be greatly appreciated.

I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf