Further to my attempts to enlighten application developers about what they might better do in the database:
it occurs to me to wonder whether it is practical to use PG’s own roles and security model in lieu of using an application-level one.
It seems that the role system in PG is sufficient for most general purposes. One could presumably also have a table with role names and associated metainformation (email address etc) as needed.
If I have a system with many thousands of users, is it practical to manage these users’ authentication and authorization using *just* Postgres?
It occurs to me that some client frameworks might have issues with their connection pools if those connections keep switching users, assuming they even can, but let’s set that aside for now. Or perhaps every connection could immediately do a SET USER before executing its connection?
This seems an attractive proposition from a security standpoint: if I use row-level security pervasively, I can have a security system that’s nestled nice and close to the data and presumably tricky to work around from a hacker given direct access only to the client application.
Is this practical? Has anyone here done it? What might the caveats be?
TIA
