PostgreSQL - Role Membership

In PostgreSQL, roles are a powerful way to manage database access and capabilities. A role can be thought of as either a user or a group, depending on how it's used. One of the powerful features of PostgreSQL roles is the ability to have role membership, which allows a role to inherit the rights and privileges of another role.

Role Membership:

When a role (let's call it roleA) is a member of another role (roleB), roleA inherits all the privileges of roleB. This way, you can easily group sets of privileges together and grant them to roles or users by simply making them a member of a particular role.

SET ROLE:

You can temporarily adopt the privileges of another role using the SET ROLE command. If roleA is a member of roleB, then roleA can execute SET ROLE roleB to temporarily obtain all of roleB's privileges.

GRANT and REVOKE Role Membership:

You can grant or revoke role membership using the GRANT and REVOKE commands, respectively.

Granting Role Membership:

GRANT roleB TO roleA;

With this command, roleA becomes a member of roleB and inherits its privileges.

Revoking Role Membership:

REVOKE roleB FROM roleA;

With this command, roleA is removed from roleB and no longer inherits its privileges.

Viewing Role Memberships:

You can view role memberships by querying the pg_roles and pg_auth_members system catalog tables.

A simple query to view role memberships:

SELECT 
    role_rol.rolname AS member,
    parent_rol.rolname AS role
FROM 
    pg_auth_members
JOIN 
    pg_roles role_rol ON role_rol.oid = pg_auth_members.member
JOIN 
    pg_roles parent_rol ON parent_rol.oid = pg_auth_members.roleid;

This query will show which roles are members of other roles.

Usage Notes:

Role memberships are a convenient way to manage complex sets of permissions. Instead of granting or revoking a large set of privileges to each user, you can grant/revoke them to a role, then simply add users to that role.
Role membership can be nested, meaning a role can be a member of another role, which in turn can be a member of yet another role. This allows for a flexible hierarchy of permissions.
Always be cautious when managing role memberships, especially in production environments, to avoid unintentionally granting or revoking important privileges.

Role memberships are a key part of the PostgreSQL role-based access control system, allowing for flexible and manageable privilege distribution.

How to manage role membership in PostgreSQL: Role membership in PostgreSQL involves adding or removing users from roles to control access and permissions.
```
-- Add user to role
GRANT your_role TO your_user;

-- Remove user from role
REVOKE your_role FROM your_user;
```
Adding a user to a role in PostgreSQL:
```
GRANT your_role TO your_user;
```
Removing a user from a role in PostgreSQL:
```
REVOKE your_role FROM your_user;
```

Checking role membership in PostgreSQL:

-- Check if user is a member of a role
SELECT * FROM pg_user_roles WHERE roleid = 'your_role' AND usesysid = 'your_user';

Granting and revoking membership in PostgreSQL:

-- Grant membership to a role
GRANT your_role TO your_user;

-- Revoke membership from a role
REVOKE your_role FROM your_user;

Role inheritance in PostgreSQL: Role inheritance allows roles to inherit privileges from other roles. Child roles inherit the privileges of their parent roles.
```
-- Create parent role
CREATE ROLE parent_role;

-- Create child role inheriting from parent
CREATE ROLE child_role INHERIT LOGIN;
```

Nested role membership in PostgreSQL: Nested role membership refers to roles being members of other roles, creating a hierarchy.

-- Create parent role
CREATE ROLE parent_role;

-- Create child role
CREATE ROLE child_role;

-- Grant membership
GRANT parent_role TO child_role;

Default role membership in PostgreSQL:

-- Set default role for a user
ALTER USER your_user SET ROLE your_role;

Listing role members in PostgreSQL:

-- List members of a role
\du+ your_role

Checking current role in PostgreSQL:

-- Check current role
SELECT current_user;

Managing role membership with psql commands in PostgreSQL:

-- Add user to role
\du+ your_role your_user

-- Remove user from role
\du- your_role your_user

Managing role membership in a specific schema in PostgreSQL:

-- Grant membership to a role in a specific schema
GRANT your_role TO your_user IN SCHEMA your_schema;