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.