This is super similar to something I built internally at MemSQL. Our customer portal system (https://portal.memsql.com) which also powers MemSQL Helios uses Postgres RBAC to enforce permissions for all database actions. My original solution was very similar to your external table approach along with four custom policies per table for each type of operation (select, insert, update, delete). After going to production and seeing some real-world performance (and being correspondingly disappointed) I altered the any condition to be something like
itemid = any(select object from QUERY_OBJECTS(MY_ROLES(), 'Read'))
QUERY_OBJECTS(subjects, action)is defined as effectively
select object from permissions as p where p.subject = any(subjects) and p.action = action
This allows the optimizer to build a set of the object ids for the current array of subjects and leverage that during query execution. I found this approach improved performance for our specific data distribution.
Other interesting things to consider are: table wide permissions, “superuser” roles, and implementing subject computation from role/group expansion (bringing more role-BAC features into the story).
The biggest issue with implementing RBAC in the database is that you often need permissions in the application as well. This is mainly true for actions taken by users which don’t affect database state, but other kinds of state in the system. Something we did to reduce the impact of this decision was to build a job-queue inside of Postgres in order to transactionally take advantage of our in-db RBAC. This works quite nicely and can drastically simplify API logic.
Nice to see someone else thinking about this. I would love to open source MemSQL’s solution if I can find the time. :)