I would like to build an authorization system to handle role-based permission within the database itself.
Basically, I want my application user to have different access. The problem of basing this on Postgresql users is that if my application has millions of users, then I must have million of Postgresql users with potentially different connection parameters every time, making unable to use any kind of connection pooling (stop me if I am wrong).
So instead I thought I should base this on roles, but since roles don't have much effect on row security (which is my primary purpose here), I can just go on and create a session variable containing the user identification upon querying. Meaning if I want to do a select on some table. My application (actually a REST API) would do the following:
SET SESSION app.user = 'my user' SELECT * from table
It would add the
SET SESSION for every request to the database. If table has the proper row security, this would get the rows from table that belongs to 'my user'
I want to know if this solution makes sense and if I understood the concept of SQL session properly, any big performance impact to expect?
Edit: The question How to implement business logic permissions in PostgreSQL (or SQL in general)? describes the details implementation on how to go about implementing a full authorization system. I want to know if I can use a session variable, to identify my user and reuse it somewhere later in my query, instead of just using role to identify a specific user. I changed the title to make this clearer.