Selecting single role for Postgresql user

Question

Is it possible in Postgresql (v15.7) for a user with multiple roles to specify which role(s) should apply when logging in? For instance, if I were granted the roles l3_support_engineer and reporting_user then when I log in my understanding is that the permissions would be additive and I could do everything that either role could do, which potentially means that e.g. the l3_support_engineer role would give me permissions to delete stuff that the reporting_user would have access to.

In other words, when I am logging in to run reports, can I specify that I want only the reporting_user role to apply so there is zero chance of me running DDL scripts?

The only alternative I can think of at the moment is to have different user accounts for each role (e.g. david_keaveny_reporting), but that adds quite a bit to the admin overhead, as I now have to manage passwords etc for each account.

Answer 1

What you can do is to make the user a member of the roles without inheritance:

GRANT l3_support_engineer, reporting_user TO david_keaveny_reporting
 WITH INHERIT FALSE;

With PostgreSQL versions older than v16, you would instead make david_keaveny_reporting a NOINHERIT role:

ALTER ROLE david_keaveny_reporting NOINHERIT;

Then the user does not automatically inherit the privileges from the roles. In order to execute those privileges, the user would have to assume the identity of the respective role:

SET ROLE reporting_user;
/* now do some reporting */
/* return to being david_keaveny_reporting */
RESET ROLE;

• Bergi: And to answer the question "can I specify that I want only the reporting_user role to apply so there is zero chance of me running DDL scripts?", actually only the second part (SET ROLE) is necessary, you don't need to disable inheritance for that. NOINHERIT is only to force the login user to explicitly choose which role they want to utilise.
• Laurenz Albe: @Bergi I don't understand. If I inherit from a role, I also inherit the CREATE privilege on a schema and am allowed to create objects. So I have to make sure that the user does not inherit, right?
• Bergi: I'm assuming the OP does login as david_keaveny. Now they will have any privileges of that role, as well as the privileges of the l3_support_engineer and reporting_user roles they're a member of, if those are inherited. But as soon as they execute SET ROLE reporting_user;, they can not (or no longer) exercise the privileges granted to david_keaveny or l3_support_engineer (even if they were inherited before), only those granted directly to reporting_user. At least that is what I gather from postgresql.org/docs/current/role-membership.html.
• Laurenz Albe: @Bergi Now I understand what you mean, and you are right. But if the user forgets to use SET ROLE, it can do both, which (I believe) is what OP wants to avoid.
• David Keaveny: Thanks everyone, that's been very helpful.