Search Postgresql Archives

Re: Users, Roles and Connection Pooling

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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. 

A couple of pointers:

- The role that the application server connects with should be a
  member of each database role it wants to become through SET ROLE.

- Yes, you will have to start with SET ROLE.
  RESET ROLE should be executed by the pool when it gets a connection
  back.

- This is a good setup if you don't have too many users.  Metadata
  queries will start getting slow if you get into the tens of thousands
  of users, maybe earlier.

  The advantages are that you can make use of PostgreSQL's rich
  permission concept and row level security.

- The name of the user might as well be an e-mail address, as long
  as it does not exceed 63 bytes.

- Make use of user groups and grant privileges on that level rather
  than to the individual users.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux