Scenario:
We have a multi-tenant application in which each tenant has its own schema. There is a public schema in which a table exists containing the records of each tenant. So there is a super admin who can create tenants and assign admins to a newly created tenant.
In order to implement RBAC (Role-based access control), what I am planning to do is to place each role table into the tenant schema and implement some middleware to check for authorization. Is this a good architecture in an isolated schema environment?
According to this answer, PostgreSQL can handle large numbers of schemas, but can get slow for backups, administration, and upgrades.
You might also consider individual tables per tenant as you can also achieve RBAC at the table level.