Users, Roles and Connection Pooling

Hey all,

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.

--
Matt Andrews

0400 990 131