From the objective of OSS-DB Exam Silver
- Operation management - basic operation management work
Sample Question
1.40
Choose two correct explanations about creating, deleting, or changing database users in PostgreSQL.
- When creating a user with CREATE USER, a schema corresponding to that user name is automatically created.
- To execute CREATE USER, you need the CREATEROLE authority, or the appropriate authority equal to or greater than it.
- Deleting a user with DROP USER deletes objects owned by the user at the same time.
- To execute DROP USER, DROPROLE authority or appropriate authority equivalent to or higher than DROPROLE authority is required.
-
You can change the user's name with ALTER USER.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/21
Answer and Explanation
Creating, deleting and changing database users are done using the CREATE USER, DROP USER, and ALTER USER statements, respectively. In PostgreSQL implementation, users and roles are not different, so CREATE ROLE, DROP ROLE, ALTER ROLE can do the same thing.
CREATEROLE authority is required to create, delete and change users. Of course, even an administrator user with SUPERUSER privilege can perform the same operation, but there is no authority called DROPROLE. There is also authority called CREATEUSER, but since it is deprecated and it has the same meaning as SUPERUSER, please be careful.
PostgreSQL does not automatically create a schema even if CREATE USER is executed. If necessary, CREATE SCHEMA is used.
When deleting a user with DROP USER, an error occurs if the user owns an object such as a table. You must delete the object owned by the user before you can delete the user.
ALTER USER old_name RENAME TO new_name;
You can change the name of an existing user by executing it.
Therefore, the correct answers are B and E.
You can change the user's name with ALTER USER.