Right, let’s talk about one of the most simultaneously brilliant and maddening features in Postgres: role inheritance. It’s the system’s way of letting you build up privileges like building blocks, and SET ROLE is the secret handshake that lets you actually use them. But the designers, in their infinite wisdom, decided to make the default behavior a bit of a head-scratcher. I’ll explain it, and then we can collectively sigh about it.

Here’s the core concept: you can make one role a member of another. The member role is supposed to inherit the privileges of the parent role. This is fantastic for creating clean, logical permission structures. You don’t grant SELECT on a table to 50 individual users; you grant it to a group role (reporting_readers) and then make all your user roles members of that group. Simple, right? Well, mostly.

The Inheritance Switcheroo: It’s OFF By Default

Here’s the first “questionable choice.” When you create a login role, say alice, and make her a member of a group role, say readers, what privileges does alice have when she first logs in?

Almost none of the readers privileges. I’m serious.

Inheritance is not the default state for a new session. Your login role starts with the NOINHERIT property. This is a safety feature, ostensibly to prevent you from accidentally doing something with elevated privileges. To actually use the privileges of your parent roles, you must explicitly use SET ROLE.

-- Create a group role with some privileges
CREATE ROLE readers NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readers;

-- Create a login role and add it to the group
CREATE ROLE alice LOGIN PASSWORD 'supersecret' INHERIT; -- Note the INHERIT here
GRANT readers TO alice;

Wait, did you catch that? I defined alice with INHERIT. This means she can inherit privileges, but it doesn’t mean she actively is inheriting them upon login. Confused? Good, join the club. Let’s log in as alice and see what happens.

-- In a session connected as 'alice'
SELECT current_user, current_role; -- both are 'alice'
SELECT * FROM some_table; -- Permission denied. Why?
-- Because the current role is 'alice', who has no direct privileges.

-- Now we perform the magic incantation
SET ROLE readers;

SELECT current_user, current_role; -- user is 'alice', role is 'readers'
SELECT * FROM some_table; -- This now works!

current_user always remains your original login identity, which is crucial for auditing. current_role is the active set of privileges you’re using. SET ROLE readers essentially means “stop being just ‘alice’ and start using all the privileges of the ‘readers’ group.”

How to Actually Make This Useful

Having to manually SET ROLE every time is a pain. You have two main weapons against this annoyance.

Option 1: The SET ROLE in a Connection Hook. This is the pragmatic, “I don’t want to think about it” solution. Use a client library or connection pooler that can run a specific command upon connection. For psql, you could use .psqlrc, but that’s not scalable. For applications, this is often the best way.

Option 2: The Almighty INHERIT Attribute on the Group Role. This is the real solution. Flip the script. Instead of making your user INHERIT and then requiring SET ROLE, make the group role have the INHERIT attribute.

-- Drop and recreate the group role correctly
DROP ROLE readers;
CREATE ROLE readers INHERIT; -- Key difference!

-- Re-grant and re-add alice (who is still INHERIT)
GRANT readers TO alice;

Now, the magic happens. Because the readers role is defined with INHERIT, any member who can inherit (like alice) will automatically have its privileges upon login. No SET ROLE required.

-- In a new session as 'alice'
SELECT current_user, current_role; -- both are 'alice'
SELECT * FROM some_table; -- This just works now. Thank goodness.

This is almost always what you want. The default behavior of NOINHERIT on group roles is, in my opinion, backwards. You almost always create a group role to be inherited from. So get into the habit of always specifying INHERIT when you create them.

The Nuclear Option: SET ROLE NONE

You’ve elevated your privileges, you’ve done the needful. How do you drop back down to your original, less-powerful login self? That’s what SET ROLE NONE is for.

-- We are currently SET ROLE readers
SELECT current_role; -- 'readers'
SET ROLE NONE;
SELECT current_role; -- 'alice'

It’s a useful tool for when you need to deliberately execute something with your base-level permissions.

The Gotcha with SET ROLE and Authorization

Remember, SET ROLE requires you to already have that role granted to you. You can’t just decide to be a superuser. Also, a critical security note: SET ROLE does not require a password. It’s a permission check, not an authentication check. Once you’re in as alice, if you’re granted the readers role, you can become it. This is why protecting your database login credentials is so paramount; it’s the only gate.

So, to summarize: use INHERIT on your group roles, not your user roles, to get sane default behavior. Use SET ROLE for temporary, session-level elevation of privileges. And always know which role you’re currently wielding—it’s the difference between having the keys to the castle and just having a nice tour brochure.