Postgresql - Create role to connect to database and revoke all other permissions

Question

I have an application which is configured with the multitenancy strategy of "single database multi schema".

Now I want to create a role which can have only specific permission that is to :

1. connect to the database
2. should be able to switch role.

Please help/guide me to create the as per the above requirement.

No other object (like other schema, tables those schema) in the database should be visible for this role.

So that I can use this role to login and based on the tenant I can switch to another role which can have access to a particular schema only .

Answer 1

You should create that role with NOINHERIT and make it a member of all the other roles. Then the role does not inherit any privileges (so it has no permissions other than those that were granted to PUBLIC), but it can use SET ROLE to become any of the roles it is a member of.

Note that you can always run RESET ROLE to revert to the original role, then SET ROLE to become any of the other roles, so it is not safe to let a user issue arbitrary SQL statements after SET ROLE.