Re: Users, Roles and Connection Pooling

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(at)gmail(dot)com> wrote:

>
>
> On Oct 2, 2019, at 3:41 AM, Matt Andrews <mattandrews(at)massey(dot)com(dot)au>
> 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(at)snowman(dot)net> wrote:
>
>> Greetings,
>>
>> * Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)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.
>
--
Matt Andrews

0400 990 131