Search Postgresql Archives

Re: Users, Roles and Connection Pooling

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

 



Yes, I’ll be more clear with the terminology. When I say user, I mean an individual application user, which most likely is a person. 

I’m also asking about this in a general sense, being concerned more with implementation details.

The Postgres role system is really powerful and versatile, why should it be a problem to create privilege hierarchies and provide individuals with privileges from any branch of the hierarchy?

Obviously, designing privileges should be done carefully, but granting roles to users should be easy. I can easily imagine an organisation that would require only a few privileges for many people, but many different privileges for a few people. 

Does it come down to performance issues when there are many roles to users?

On Wed, 2 Oct 2019 at 21:03, Rob Sargent <robjsargent@xxxxxxxxx> wrote:


On Oct 2, 2019, at 3:41 AM, Matt Andrews <mattandrews@xxxxxxxxxxxxx> wrote:

I have little experience in this area, but it seems like having a Postgres role for every application user is the right way to do things. It’s just that it also seems really inconvenient.

For example how to map an application’s users/people table to Postgres roles? The pg_role name field is limited to 64 bytes, you can’t create a foreign key to pg_role. What’s the answer? Use UUIDs as usernames or something?

There’s very little out there on this topic, but surely this has been done before. 

On Wed, 2 Oct 2019 at 17:43, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:
Greetings,

* Laurenz Albe (laurenz.albe@xxxxxxxxxxx) wrote:
> A couple of pointers:

I generally agree with these comments.

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

While this seems plausible- I'd love to hear about exactly what you've
seen start to be a problem when getting up to that many users.  Are you
just referring to things like \du?  Or..?

Thanks,

Stephen
The terminology gets a little wonky here since “user” equals “role” in postgres terms but I’ll apply user to the person using your app. 
What are your expected numbers of total distinct users? 
Ratio of users to roles (as permissions set) or is every user unique in access needs?
Do any users need to be in more than one role/group? 
When/how will you assign role to user?
I feel these issues will affect your choice of design. 
--
Matt Andrews

0400 990 131





[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