Role creation and management in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this role creation query?

Consider the following SQL command executed in PostgreSQL:

CREATE ROLE analyst LOGIN PASSWORD 'secure123' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

What will be the result when you run \du analyst to list this role's attributes?

PostgreSQL

CREATE ROLE analyst LOGIN PASSWORD 'secure123' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

A Role name: analyst, Attributes: Cannot create DB, Can create role, Can inherit, Can login

B Role name: analyst, Attributes: Superuser, Can create DB, Can create role, Can inherit, Can login

C Role name: analyst, Attributes: Cannot login, Can create DB, Can create role, Can inherit

D Role name: analyst, Attributes: Cannot create DB, Cannot create role, Cannot inherit, Can login

💡 Hint

Look carefully at the keywords NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOINHERIT and LOGIN.

The command creates a role named 'analyst' that can login but does not have superuser privileges, cannot create databases or roles, and does not inherit privileges from other roles.

📝 Syntax

intermediate

Which option correctly grants a role the ability to create databases?

You want to grant the role 'developer' the permission to create databases. Which of the following SQL commands is syntactically correct and achieves this?

A ALTER ROLE developer WITH CREATEDB;

B ALTER ROLE developer SET CREATEDB;

C ALTER ROLE developer CREATEDB;

D ALTER ROLE developer GRANT CREATEDB;

💡 Hint

Remember the syntax for altering role attributes in PostgreSQL.

The correct syntax to grant the CREATEDB attribute is 'ALTER ROLE role_name CREATEDB;'. The other options are invalid syntax.

🔧 Debug

advanced

Why does this role creation command fail?

You run the following command:

CREATE ROLE manager PASSWORD 'pass123' LOGIN;

But PostgreSQL returns an error. What is the cause?

PostgreSQL

CREATE ROLE manager PASSWORD 'pass123' LOGIN;

A The PASSWORD clause is deprecated; use ENCRYPTED PASSWORD instead.

B The PASSWORD clause must come after LOGIN keyword.

C The LOGIN keyword must come before PASSWORD clause.

D The PASSWORD clause requires the keyword ENCRYPTED before it.

💡 Hint

Check PostgreSQL documentation for role creation syntax regarding passwords.

In modern PostgreSQL versions, the PASSWORD clause alone is deprecated. You must use ENCRYPTED PASSWORD or specify password encryption explicitly.

🧠 Conceptual

advanced

What is the effect of the NOINHERIT attribute on a role?

In PostgreSQL, what does setting the NOINHERIT attribute on a role do?

A The role cannot grant privileges to other roles.

B The role cannot inherit privileges from roles it is a member of.

C The role cannot login to the database.

D The role cannot be a member of any other role.

💡 Hint

Think about what inheritance means in role membership.

NOINHERIT means the role does not automatically get privileges from roles it belongs to; it must explicitly SET ROLE to use those privileges.

❓ optimization

expert

Efficiently revoking multiple privileges from a role

You want to revoke the privileges to create databases and create roles from the role 'temp_admin'. Which single SQL command correctly and efficiently revokes both privileges?

A ALTER ROLE temp_admin NOCREATEDB NOCREATEROLE;

B REVOKE CREATEDB, CREATEROLE FROM temp_admin;

C REVOKE ALL PRIVILEGES ON DATABASE temp_admin;

D ALTER ROLE temp_admin REVOKE CREATEDB, CREATEROLE;

💡 Hint

Remember that CREATEDB and CREATEROLE are role attributes, not privileges on objects.

CREATEDB and CREATEROLE are role attributes set via ALTER ROLE, not privileges revoked with REVOKE. Option A correctly uses ALTER ROLE with NOCREATEDB and NOCREATEROLE.

Practice

1. What is the primary purpose of a role in PostgreSQL?

easy

A. To control access and permissions for users and groups

B. To store data in tables

C. To create backups of the database

D. To optimize query performance

Solution

1. Step 1: Understand the concept of roles

   Roles in PostgreSQL are used to manage who can access the database and what they can do.

2. Step 2: Identify the main function of roles

   Roles control permissions and access rights, not data storage or backups.

3. Final Answer:

   To control access and permissions for users and groups -> Option A

4. Quick Check:

   Roles = Access control [OK]

Hint: Roles manage user permissions and access rights [OK]

Common Mistakes:

• Confusing roles with tables or data storage
• Thinking roles handle backups
• Assuming roles optimize queries

2. Which of the following is the correct syntax to create a role with login permission in PostgreSQL?

easy

A. CREATE ROLE user1 WITH LOGIN;

B. CREATE ROLE user1 CAN LOGIN;

C. CREATE ROLE user1 LOGIN;

D. CREATE ROLE user1 ALLOW LOGIN;

Solution

1. Step 1: Recall the syntax for creating a role with login

   The correct syntax uses WITH LOGIN to allow the role to log in.

2. Step 2: Check each option

   CREATE ROLE user1 WITH LOGIN; uses 'WITH LOGIN' which is correct. Others use incorrect keywords.

3. Final Answer:

   CREATE ROLE user1 WITH LOGIN; -> Option A

4. Quick Check:

   WITH LOGIN = enable login [OK]

Hint: Use WITH LOGIN to grant login rights when creating roles [OK]

Common Mistakes:

• Omitting WITH before LOGIN
• Using CAN or ALLOW instead of WITH
• Forgetting semicolon at end

3. Given the commands:

CREATE ROLE analyst NOLOGIN;
ALTER ROLE analyst CREATEDB;

What is true about the role analyst?

medium

A. The role can log in but cannot create databases

B. The role can log in and create databases

C. The role cannot log in but can create databases

D. The role cannot log in and cannot create databases

Solution

1. Step 1: Analyze the CREATE ROLE command

   The role 'analyst' is created with NOLOGIN, so it cannot log in.

2. Step 2: Analyze the ALTER ROLE command

   The role is altered to have CREATEDB permission, so it can create databases.

3. Final Answer:

   The role cannot log in but can create databases -> Option C

4. Quick Check:

   NOLOGIN + CREATEDB = no login, can create DB [OK]

Hint: NOLOGIN disables login; CREATEDB allows database creation [OK]

Common Mistakes:

• Assuming NOLOGIN means role cannot do anything
• Confusing CREATEDB with login permission
• Ignoring ALTER ROLE effects

4. Identify the error in the following command:

CREATE ROLE manager LOGIN PASSWORD 'secret';

medium

A. PASSWORD must be set using ENCRYPTED keyword

B. LOGIN cannot be used with PASSWORD

C. PASSWORD should be set with USING keyword

D. PASSWORD must be set using WITH keyword

Solution

1. Step 1: Check correct syntax for setting password

   In PostgreSQL, options like PASSWORD must be specified after WITH keyword.

2. Step 2: Identify the error in the command

   The command misses WITH before PASSWORD, causing syntax error.

3. Final Answer:

   PASSWORD must be set using WITH keyword -> Option D

4. Quick Check:

   Use WITH before PASSWORD [OK]

Hint: Use WITH before PASSWORD when creating roles [OK]

Common Mistakes:

• Omitting WITH before PASSWORD
• Thinking LOGIN disallows PASSWORD
• Using ENCRYPTED incorrectly

5. You want to create a role named developer that can log in, create databases, and also inherit permissions from another role team_member. Which command correctly achieves this?

hard

A. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT team_member;

B. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer;

C. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; ALTER ROLE developer IN ROLE team_member;

D. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT developer TO team_member;

Solution

1. Step 1: Create role with login, createdb, and inherit

   The role must be created with WITH LOGIN, CREATEDB, and INHERIT options.

2. Step 2: Grant membership to inherit permissions

   To inherit permissions from team_member, grant team_member role to developer using GRANT.

3. Step 3: Check each option

   CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer; correctly creates the role and grants team_member to developer. Others misuse syntax or reverse grant direction.

4. Final Answer:

   CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer; -> Option B

5. Quick Check:

   GRANT role TO user for inheritance [OK]

Hint: Use GRANT role TO user to inherit permissions [OK]

Common Mistakes:

• Putting role name after INHERIT
• Reversing GRANT direction
• Missing WITH keyword or semicolon