3 ms·
You can use RLS with a web app. You can still use connection pooling. PostgreSQL is performant with hundreds of thousands of user accounts (though not pgAdmin
by elchief 11y ago
You can use RLS with a web app.
You can still use connection pooling.
PostgreSQL is performant with hundreds of thousands of user accounts (though not pgAdmin).
create role www noinherit login password 's3cr3t';
create role alice;
grant alice to www;
Connect to database as www (same connection string = you can use pooling)
After you get your connection from the pool
set role alice;
When you release you connection back to the pool
reset role;
Want per-database users instead of per-cluster users? See the db_user_namespace setting
Use LDAP / AD? Sync users/groups/roles to pg: https://github.com/larskanis/pg-ldap-sync https://github.com/larskanis/pg-ldap-sync
- rch 11y agoThe LDAP/AD setup is what I'm most looking forward to trying out. Do you know of any examples of where it's being used currently?
- mjevans 11y agoYou can actually use GSS authentication if you setup the correct Kerberos tickets and the correct service names so that Windows will try to communicate with the server via the user's tickets if a password isn't specified. It's kind of a pain that the configuration options for that have to go in to the host-based authentication file and that it just assumes you know this.
- elchief 11y agoUser www doesn't need to be a superuser for this to work btw
- sandGorgon 11y agoThe problem is that "set role Alice" is tricky to do with many orm. Which is why group roles are not very effective in postgres. There should be some way to set this on the database itself rather than client side.