Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.2 Usage of server management tools (createuser/dropuser)

Sample Question


Choose the three most appropriate descriptions for the createuser command.

  1. You must have superuser privileges on the database to execute the command.

  2. Users can also be created in databases running on other hosts.

  3. If you optionally specify, for example, "-P passwd," the new user's password will be 'passwd'.

  4. You can connect to a database in psql and use the CREATE USER command to accomplish the same thing as the createuser command.

  5. You can use the dropuser command to delete a user created with the createuser command, or you can connect to a database, using psql etc., and run the DROP USER command.

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

Answer and Explanation

For many databases, you can create a user by connecting to the database and executing the SQL command CREATE USER, but PostgreSQL provides a command called createuser that can do the same thing from the OS level.

CREATE USER requires the CREATEROLE privilege. Of course, you can create a user with superuser privileges, and you need superuser privileges to create a user with superuser privileges, but for normal user creation, CREATEROLE privilege is sufficient.

The createuser command can create a user by connecting to a database running on another host (remote) by specifying the host and port number to connect to with the -h and -p options. Alternatively, the environment variables PGHOST and PGPORT can be specified.

The -P option (where P is a capital letter) allows you to specify a password for the new user, but the password string is entered interactively at the command prompt rather than on the command line.

There are a variety of other options for the createuser command, but they are either options for connecting to the database (specifying the destination and the user to use) or for running CREATE USER after connecting, so you can do the exact same thing if you connect to the database using psql, for example, and then run CREATE USER.

The SQL for dropping users is DROP USER, but there is also a dropuser command that can be executed from the OS level. The user can be dropped with either the dropuser command or SQL's DROP USER, since there is no distinction whether the user was created with createuser or CREATE USER.

So the correct answers are B, D, and E.

Note that, in PostgreSQL users and roles are implemented as the same thing. CREATE USER internally executes the CREATE ROLE command, and the createuser command is a also wrapper for the CREATE ROLE command.