Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Concept of database role / user)

Sample Question


Choose two appropriate explanations for the differences between CREATE USER and CREATE ROLE in PostgreSQL.

  1. CREATE ROLE can be executed by normal users, but CREATE USER requires superuser privileges.

  2. Roles created with CREATE ROLE can be granted to users with the GRANT command, but users created with CREATE USER cannot be granted to other users with GRANT.

  3. The LOGIN attribute defaults to NOLOGIN for a role created with CREATE ROLE, and to LOGIN for a user created with CREATE USER.

  4. When a user is created with CREATE USER, a schema with the same name is automatically created, but with CREATE ROLE, no schema is created.

  5. A command called 'createuser' is provided to perform the operation equivalent to CREATE USER from the OS command line, but no command named 'createrole' is provided.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

The SQL standard defines users and roles as separate concepts, and they are created with the SQL commands CREATE USER and CREATE ROLE, respectively. A user is a user to connect to a database, and a role is a collection of different privileges. Users can be granted different privileges individually, or they can be granted privileges in bulk by granting roles.
PostgreSQL includes the concepts of users and roles, but they are internally implemented as exactly the same thing. Executing the CREATE USER command internally invokes the CREATE ROLE command. The only difference is that the LOGIN attribute defaults to NOLOGIN if CREATE ROLE is used, and it defaults to LOGIN if CREATE USER is used. That means users can connect to the database by default, but roles cannot.
Both CREATE ROLE and CREATE USER require CREATEROLE or superuser privilege.
Internally, both users and roles are the same, so both can be granted to other users or roles via GRANT.
PostgreSQL schemas must be created separately by running CREATE SCHEMA, and are not created automatically, for example by CREATE USER.
PostgreSQL provides a utility command called createuser to create database users from the OS command line. Internally it executes CREATE ROLE, but by default it creates a user with LOGIN privileges, so in that sense it is equivalent to CREATE USER. No command called createrole is provided, but you can create a NOLOGIN user by executing with the option -L or --no-login.

So the correct answers are C and E.