Login vs group roles in PostgreSQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference between Login and Group Roles in PostgreSQL

Which statement correctly describes the difference between a login role and a group role in PostgreSQL?

A A login role can connect to the database, while a group role cannot connect but can own privileges and be a member of other roles.

B A group role can connect to the database, while a login role cannot connect but can own privileges.

C Both login and group roles can connect to the database, but only login roles can own tables.

D Login roles are used only for authentication, and group roles are used only for schema ownership.

💡 Hint

Think about which roles have the ability to log in and which are used for grouping privileges.

In PostgreSQL, a login role is a role with the LOGIN attribute, allowing it to connect to the database. A group role does not have LOGIN and is used to group privileges and manage permissions collectively.

❓ query_result

intermediate

Query to List All Login Roles

Given the following query, what will be the output?

PostgreSQL

SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;

A A list of all role names that have login privileges, sorted alphabetically.

B A list of all group roles that cannot login, sorted alphabetically.

C An error because rolcanlogin is not a valid column.

D A list of all roles regardless of login capability.

💡 Hint

Check the meaning of rolcanlogin column in pg_roles.

The column rolcanlogin in pg_roles indicates if a role can log in. Filtering by rolcanlogin = true returns only login roles.

📝 Syntax

advanced

Create a Group Role and Assign Login Roles

Which SQL command sequence correctly creates a group role named 'dev_team' and assigns two existing login roles 'alice' and 'bob' as members?

A CREATE ROLE dev_team NOLOGIN; GRANT dev_team TO alice, bob;

B CREATE ROLE dev_team; GRANT alice, bob TO dev_team;

C CREATE ROLE dev_team NOLOGIN; GRANT alice, bob TO dev_team;

D CREATE ROLE dev_team LOGIN; GRANT dev_team TO alice, bob;

💡 Hint

Remember group roles should not have LOGIN and members are granted to the group role.

Group roles are created with NOLOGIN. To assign login roles as members, you grant the login roles TO the group role, not the other way around.

❓ optimization

advanced

Efficient Role Membership Check

You want to efficiently check if a login role 'charlie' is a member of the group role 'managers'. Which query is the most efficient and correct?

A SELECT * FROM pg_roles WHERE rolname = 'charlie' AND rolname IN (SELECT rolname FROM pg_roles WHERE rolname = 'managers');

B SELECT 1 FROM pg_auth_members m JOIN pg_roles r ON m.roleid = r.oid JOIN pg_roles u ON m.member = u.oid WHERE r.rolname = 'managers' AND u.rolname = 'charlie';

C SELECT * FROM pg_auth_members WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'charlie') AND member = (SELECT oid FROM pg_roles WHERE rolname = 'managers');

D SELECT * FROM pg_roles WHERE rolname = 'managers' AND rolcanlogin = true;

💡 Hint

Check the join conditions and role/member columns in pg_auth_members.

pg_auth_members links roles and members by OIDs. roleid is the group role, member is the login role. Joining pg_roles twice allows filtering by names.

🔧 Debug

expert

Why Does This Role Membership Query Fail?

Consider this query to check if 'dave' is a member of 'admins': SELECT * FROM pg_auth_members WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'dave') AND member = (SELECT oid FROM pg_roles WHERE rolname = 'admins'); Why does this query return no rows even if 'dave' is a member of 'admins'?

PostgreSQL

SELECT * FROM pg_auth_members WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'dave') AND member = (SELECT oid FROM pg_roles WHERE rolname = 'admins');

A Because the query syntax is invalid and causes a runtime error.

B Because 'dave' is not a login role and cannot be a member of any group role.

C Because pg_auth_members only stores direct memberships, and 'dave' is an indirect member.

D Because roleid should be the group role 'admins' and member should be the login role 'dave', but the query reverses them.

💡 Hint

Check the meaning of roleid and member columns in pg_auth_members.

In pg_auth_members, roleid is the group role's OID and member is the login role's OID. The query reverses these, so it finds no matches.