Hacker News Reader

That's only true for postgres itself. The postgres tooling and library ecosystem deals less well with 1000s of roles, as it assumes it can load all rules in memory and show them in a list. It gets annoying quickly.

This is a common theme with even mature tech. If you walk off the betrotten paths, the core tech deals well enough, but you limit your ecosystem choice. I'd advise against it, there are surprising hidden costs.

> The postgres tooling and library ecosystem deals less well with 1000s of roles

Do you have an example of such tool?

> as it assumes it can load all rules in memory and show them in a list

Which tool has trouble loading millions of users in memory? This should be a few megabytes of RAM usage at most

It is more the UI/list aspect. take e.g pgadmin and click open the list of users. I have a db with maybe 1000 users and even with that low number it gets annoying to navigate. Compare with e.g. LDAP tools that allow to organize users in a tree.

Now 1000 is still survivable, but the experience made it clear to me that one doesn't want ones whole corporation in 1 long list.

To be clear: This is not a problem of the postgres ecosystem, which has very high quality standards and deserves much praise. But you get a feel that it wasn't intended to be used that way.

Author here. This page was actually just some notes I threw together in a couple of hours combining some research on RLS with my own existing knowledge, so it's quite possible there are some inaccuracies.

My "source" for thousands of users not scaling well was simply a few comments I found scattered around; there wasn't really anything empirical to back up that line. I'll remove it. Thanks for the note!

best way to learn is to get knowledgeable people from HN reviewing your doc :)

> conflating the well known scalability issues with the number of concurrent connections to PostgreSQL with the number of defined roles.

They are related in some way, since a connection is tied to a user, you will need to open a connection for every active user.

That means solutions like pgbouncer that pools connections might not work as well than your typical app where most connections are interchangeable.

There's no need to create a new connection per role.

Simply use `SET ROLE`/`RESET ROLE`.

https://www.postgresql.org/docs/15/sql-set-role.html

It does seem like that adds two round trips (SET ROLE, RESET ROLE) to each database interaction, though.

"They are related in some way, since a connection is tied to a user, you will need to open a connection for every active user."

This isn't exactly correct. While every connection will be made with a role with permission to connect, it is not necessary for every role taking action on the database to have its own individual connection. As @paulddraper points out, there are commands which appropriately privileged database roles can use to switch their role context.

For example, if I wanted to take advantage of row level security with many thousands of users, I could (not saying you should, just a hypothetical for this discussion) do the following. 1) For each application user, create a unique database role and assign privileges as desired; 2) open up a connection pool with a reasonable number of PostgreSQL connections using a special role designated for just establishing connections and with privileges enough to change its role (otherwise as unprivileged as possible); 3) as transactions for users come in and get assigned to a PostgreSQL connection from the pool, the first thing that happens at the beginning of the transaction is that the connection switches the current role to that of the user requesting the transaction. 5) at the end of the transaction the role is reset back to the role owning the connection. This role switch then continues for all transactions to be processed.

Such a process as above may have its own issues, but is technically feasible and allows for a small set of connections to serve any user that shows up using that user's database role for transaction processing. The number of connections can be much, much smaller than the number of database roles that might need to process something. Of course, having a million database roles might have implications for how many database connections you really need to service them, but it's not one to one for each active application user.

It's not that postgres can't handle large tables, it's more that many of the ways it uses system tables aren't particularly optimized for them being large, so you're operating outside the territory the table/query design was probably built for.

Like most things postgres it'll probably still work better than it has any right to for a while, but it'd be extremely unsurprising if it caused you unexpected problems at some point. (And if it does, they'll likely excitingly novel and you'll probably have to dig pretty deep to understand what's going on.)

so is a good rule of thumb "put your users in postgres first until 100-500k, then revisit if you need to move to a dedicated solution"?

Probably not unless you like rearchitecting your entire security system when you already have 500k users!

That's probably easier than if you have only 2k. Although the special cases in the 500k might squeal a bit, they're more ignorable.