Re: Users, Roles and Connection Pooling

Greetings,

(we don't top-post on these lists, fyi, please reply in-line and trim)

* Matt Andrews (mattandrews@massey.com.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.

I agree that there are some drawbacks to it.

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

Yeah, it would be nice to have an answer to the FK issue when it comes
to roles (and possibly other things..).  The limit on length is annoying
but I'm not sure that it's show-stopper.  I don't think using UUIDs is a
good idea, at all...

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

Oh, absolutely, but with compromises, particularly around FKs and such.

Thanks,

Stephen