Login vs group roles in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new login role named 'user1'.

PostgreSQL

CREATE ROLE [1] LOGIN;

Drag options to blanks, or click blank then click option'

Agroup1

Buser1

Cadmin

Dreadonly

Attempts:

3 left

💡 Hint

Common Mistakes

Using a group role name instead of a login role name.

Forgetting the LOGIN keyword.

The CREATE ROLE command with LOGIN creates a login role. Here, 'user1' is the login role name.

2fill in blank

medium

Complete the code to create a group role named 'group1'.

PostgreSQL

CREATE ROLE [1] NOLOGIN;

Drag options to blanks, or click blank then click option'

Agroup1

Breadonly

Cadmin

Duser1

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOGIN keyword for group roles.

Confusing login roles with group roles.

The NOLOGIN option creates a group role that cannot log in directly.

3fill in blank

hard

Fix the error in the code to add the login role 'user1' to the group role 'group1'.

PostgreSQL

GRANT [1] TO user1;

Drag options to blanks, or click blank then click option'

Areadonly

Buser1

Cadmin

Dgroup1

Attempts:

3 left

💡 Hint

Common Mistakes

Granting the login role to the group role instead of the other way.

Using the wrong role names.

The GRANT command adds a role to another role. Here, 'group1' is granted to 'user1' to make 'user1' a member of 'group1'.

4fill in blank

hard

Fill both blanks to check which roles 'user1' is a member of.

PostgreSQL

SELECT rolname FROM pg_auth_members JOIN pg_roles ON pg_roles.oid = [1] WHERE [2] = (SELECT oid FROM pg_roles WHERE rolname = 'user1');

Drag options to blanks, or click blank then click option'

Aroleid

Bmember

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping roleid and member columns.

Using incorrect table aliases.

This query joins pg_auth_members and pg_roles to find roles where 'user1' is a member. roleid is the role granted, and member is the member role.

5fill in blank

hard

Fill all three blanks to create a login role 'user2', create a group role 'group2', and add 'user2' to 'group2'.

PostgreSQL

CREATE ROLE [1] LOGIN;
CREATE ROLE [2] NOLOGIN;
GRANT [3] TO user2;

Drag options to blanks, or click blank then click option'

Auser2

Bgroup2

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up login and group role names.

Granting the login role to the group role.

First, create the login role 'user2'. Then create the group role 'group2'. Finally, grant 'group2' to 'user2' to add the user to the group.