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:
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,
StephenThe 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
