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.
Greetings,
* Laurenz Albe (laurenz.albe@cybertec.at) 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.