Role creation and management in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a role in PostgreSQL?

A role in PostgreSQL is an entity that can own database objects and have database privileges. It can represent a user or a group of users.

beginner

How do you create a new role with login capability?

Use the command: CREATE ROLE role_name LOGIN; This creates a role that can be used to connect to the database.

intermediate

What command grants a role the ability to create databases?

Use: ALTER ROLE role_name CREATEDB; to allow the role to create new databases.

intermediate

How can you assign one role to another role (role membership)?

Use: GRANT role_name TO member_role; to make member_role inherit privileges of role_name.

intermediate

How do you remove a role from the database?

Use: DROP ROLE role_name; to delete the role. Make sure the role owns no database objects or is not connected.

Which command creates a role that can log in to the database?

A CREATE ROLE user1 NOCREATE;

B CREATE ROLE user1 LOGIN;

C ALTER ROLE user1 LOGIN;

D GRANT LOGIN TO user1;

The CREATE ROLE user1 LOGIN; command creates a role with login permission.

How do you allow a role to create new databases?

A GRANT CREATEDB TO role_name;

B CREATE ROLE role_name CREATEDB;

C ALTER ROLE role_name CREATEDB;

D SET role_name CREATEDB;

Use ALTER ROLE role_name CREATEDB; to grant database creation rights.

What does the command GRANT admin TO user; do?

A Gives user the privileges of admin role.

B Creates a new role named admin for user.

C Removes admin role from user.

D Changes user role to admin.

It makes user a member of admin role, inheriting its privileges.

Which command removes a role from PostgreSQL?

A REMOVE ROLE role_name;

B DROP USER role_name;

C DELETE ROLE role_name;

D DROP ROLE role_name;

The correct command to remove a role is DROP ROLE role_name;.

Can a role in PostgreSQL represent a group of users?

A Yes, roles can be groups or individual users.

B No, roles are only individual users.

C Only if the role has LOGIN privilege.

D Only if the role is a superuser.

Roles can represent either individual users or groups of users.

Explain how to create a new PostgreSQL role that can log in and create databases.

Think about the commands to create a role and then grant it database creation rights.

Describe how role membership works in PostgreSQL and how to grant one role to another.

Consider how roles can be grouped by granting one role to another.