Users, Roles and Connection Pooling

On Wed, 2019-10-02 at 08:46 +1000, Matt Andrews wrote:

Here’s a question I’ve been asking for a while and just can’t find an
answer to, so I thought I’d ask it here. The answer could be
subjective, but here goes...

When a web app connects to Postgres via a connection pooler, what is
the best way to manage privileges for the connecting user? Especially
when their is a complex hierarchy of privileges?

Should each signed up user have their own role which inherits from
whichever roles they are members of? This means that the connection
pool role should then be granted in every user?

Does creating a Postgres role for every app user scale? Roles can
only have names, is an email address a good name for individual app
users?

Are their any resources out there that anyone knows of that addresses
these questions?

There’s plenty of stuff out there on roles and permissions, I’ve read
a lot of it, but not much on dealing with individual app users
through a connection pool.

I’m thinking that any queries should start with SET ROLE ‘username’
and end with RESET ROLE. Is this how it could work?

Any help would be greatly appreciated.

A couple of pointers:

- The role that the application server connects with should be a
member of each database role it wants to become through SET ROLE.

- Yes, you will have to start with SET ROLE.
RESET ROLE should be executed by the pool when it gets a connection
back.

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

The advantages are that you can make use of PostgreSQL's rich
permission concept and row level security.

- The name of the user might as well be an e-mail address, as long
as it does not exceed 63 bytes.

- Make use of user groups and grant privileges on that level rather
than to the individual users.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

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

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@snowman.net> wrote:

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

--
Matt Andrews

0400 990 131

On Oct 2, 2019, at 3:41 AM, 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.

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@snowman.net> wrote:
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.

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@gmail.com> wrote:

On Oct 2, 2019, at 3:41 AM, 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.

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@snowman.net> wrote:

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.

--
Matt Andrews

0400 990 131

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

On Wed, 2019-10-02 at 08:46 +1000, Matt Andrews wrote:

Here’s a question I’ve been asking for a while and just can’t find an
answer to, so I thought I’d ask it here. The answer could be
subjective, but here goes...

When a web app connects to Postgres via a connection pooler, what is
the best way to manage privileges for the connecting user? Especially
when their is a complex hierarchy of privileges?

Should each signed up user have their own role which inherits from
whichever roles they are members of? This means that the connection
pool role should then be granted in every user?

Does creating a Postgres role for every app user scale? Roles can
only have names, is an email address a good name for individual app
users?

Are their any resources out there that anyone knows of that addresses
these questions?

There’s plenty of stuff out there on roles and permissions, I’ve read
a lot of it, but not much on dealing with individual app users
through a connection pool.

I’m thinking that any queries should start with SET ROLE ‘username’
and end with RESET ROLE. Is this how it could work?

Any help would be greatly appreciated.

I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf

On 10/2/19 5:27 PM, raf wrote:

I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf

How easy is it to introduce an new function call all the way up to the
app user?  Does this approach preclude making use of any query
generation techniques available?

Rob Sargent wrote:

On 10/2/19 5:27 PM, raf wrote:

I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf

How easy is it to introduce an new function call all the way up to the app
user?

It's easy when you have the right tools to make it
easy: i.e. you write the stored function, then run a
tool to generate the python class for the result set
and the python function that the client applications
can then call to execute the stored function and return
its results.

Does this approach preclude making use of any query generation
techniques available?

Yes, it does. I'm happy to write my own plpgsql and
sql. I find that usually results in faster results
(mainly by reducing the temptation to process data
outside the database) as well as being more secure.

I once worked in a job were I couldn't go home until
some program had finished and it was sucking data out
of the database just to summarise it and insert the
summaries. It tooks hours. I replaced it with a stored
procedure that took two minutes and I started going
home much earlier. Rightly or wrongly, that made me
prefer prcessing data inside the database.

cheers,
raf