MCQs on User Management and Security | PostgreSQL Database

Boost your PostgreSQL expertise with this curated set of 30 multiple-choice questions on user management and security. Learn about roles, permissions, privileges, and security best practices to ensure database protection.

---

Topics Covered:

• Creating and Managing Roles
• Role Inheritance and Permissions
• Granting and Revoking Privileges
• Security Best Practices

---

MCQs on User Management and Security

Creating and Managing Roles

1. Which command is used to create a role in PostgreSQL?
   • A) CREATE ROLE <role_name>;
   • B) NEW ROLE <role_name>;
   • C) CREATE USER <role_name>;
   • D) ROLE CREATE <role_name>;
2. In PostgreSQL, roles can function as:
   • A) Users
   • B) Groups
   • C) Both A and B
   • D) Neither A nor B
3. What option allows the created role to log into the database?
   • A) WITH LOGIN
   • B) ALLOW LOGIN
   • C) ENABLE LOGIN
   • D) LOGIN TRUE
4. How do you delete a role in PostgreSQL?
   • A) DROP USER <role_name>;
   • B) REMOVE ROLE <role_name>;
   • C) DELETE ROLE <role_name>;
   • D) DROP ROLE <role_name>;
5. Which command is used to alter an existing role’s attributes?
   • A) MODIFY ROLE <role_name>;
   • B) ALTER ROLE <role_name>;
   • C) UPDATE ROLE <role_name>;
   • D) CHANGE ROLE <role_name>;

---

Role Inheritance and Permissions

6. What does the INHERIT attribute in PostgreSQL roles do?
   • A) Allows roles to inherit permissions from other roles
   • B) Allows roles to inherit schema ownership
   • C) Allows roles to create child roles
   • D) Allows roles to inherit database connections
7. How do you grant a role the ability to inherit privileges?
   • A) GRANT INHERIT TO <role_name>;
   • B) ALTER ROLE <role_name> WITH INHERIT;
   • C) GRANT PERMISSIONS TO <role_name>;
   • D) ENABLE INHERIT FOR <role_name>;
8. What happens when a role does not have the NOINHERIT attribute?
   • A) It inherits all granted privileges automatically
   • B) It requires explicit permission for privileges
   • C) It cannot access any database objects
   • D) It becomes a superuser
9. How can a role be allowed to bypass all access controls in PostgreSQL?
   • A) Assign SUPERUSER attribute
   • B) Assign BYPASS attribute
   • C) Assign ADMIN attribute
   • D) Assign CONTROL attribute
10. Which command grants the ability to inherit privileges to a role?
    • A) ALTER ROLE <role_name> INHERIT;
    • B) GRANT PRIVILEGES TO <role_name>;
    • C) CREATE INHERIT ROLE <role_name>;
    • D) ENABLE INHERIT ROLE <role_name>;

---

Granting and Revoking Privileges

11. Which SQL command grants permissions on a table to a role?
    • A) PERMIT <role_name> ON <table_name>;
    • B) ALLOW <role_name> ON <table_name>;
    • C) GRANT <permissions> ON <table_name> TO <role_name>;
    • D) GIVE <permissions> TO <role_name>;
12. How do you revoke a specific privilege from a role?
    • A) REMOVE <privilege> FROM <role_name>;
    • B) REVOKE <privilege> ON <object> FROM <role_name>;
    • C) DELETE <privilege> FROM <role_name>;
    • D) RESET PRIVILEGES <role_name>;
13. What does the GRANT option WITH GRANT OPTION allow?
    • A) Allows the role to create databases
    • B) Allows the role to revoke granted privileges
    • C) Allows the role to grant the privilege to others
    • D) Allows the role to drop database objects
14. If a role has privileges on a table, how can they be checked?
    • A) LIST PRIVILEGES <role_name>;
    • B) \dp <table_name> in psql
    • C) SHOW PERMISSIONS <table_name>;
    • D) DESCRIBE GRANTS <role_name>;
15. What is the default behavior when GRANT is used on a table without specifying permissions?
    • A) Grants all privileges
    • B) Grants SELECT privilege
    • C) Grants SELECT and INSERT privileges
    • D) Grants no privileges
16. How do you revoke ALL privileges from a role on a specific table?
    • A) RESET PRIVILEGES <role_name>;
    • B) REVOKE ALL ON <table_name> FROM <role_name>;
    • C) REMOVE ALL <role_name>;
    • D) RESET PERMISSIONS <role_name>;
17. Which privilege is required to modify data in a table?
    • A) SELECT
    • B) UPDATE
    • C) DELETE
    • D) INSERT
18. Can a role with no LOGIN attribute be granted privileges?
    • A) Yes
    • B) No
    • C) Only if it has SUPERUSER attribute
    • D) Only if granted by a superuser
19. What command allows revoking all privileges granted by a specific role?
    • A) REMOVE ALL GRANTS <role_name>;
    • B) REVOKE GRANTED BY <role_name>;
    • C) REVOKE ALL PRIVILEGES BY <role_name>;
    • D) RESET GRANTS BY <role_name>;
20. What privilege is needed to create new objects in a schema?
    • A) CREATE
    • B) ALTER
    • C) OWNER
    • D) SUPERUSER

---

Security Best Practices

21. Which role attribute should be minimized to reduce security risks?
    • A) LOGIN
    • B) SUPERUSER
    • C) CREATE ROLE
    • D) NOINHERIT
22. What is a key benefit of using role inheritance in PostgreSQL?
    • A) Centralized management of permissions
    • B) Automatic database backups
    • C) Improved query performance
    • D) Simplified schema creation
23. What should be done to secure database connections over a network?
    • A) Use SSL/TLS encryption
    • B) Enable SUPERUSER for all roles
    • C) Use default passwords
    • D) Allow public IP connections
24. What is the purpose of the pg_hba.conf file?
    • A) To manage role privileges
    • B) To configure database authentication
    • C) To set the PostgreSQL version
    • D) To define the schema structure
25. How can you enforce password complexity in PostgreSQL?
    • A) Using password_encryption configuration
    • B) Using a custom password policy function
    • C) Adding constraints to the role creation
    • D) All of the above
26. Why is it important to regularly audit user roles and privileges?
    • A) To improve database performance
    • B) To detect unauthorized access
    • C) To create new schemas
    • D) To enable faster queries
27. Which command ensures that passwords are stored securely?
    • A) SET password_encryption TO ‘md5’;
    • B) SET password_encryption TO ‘scram-sha-256’;
    • C) SET password_security TO ‘true’;
    • D) ENCRYPT PASSWORDS;
28. What is the best practice for default roles after installation?
    • A) Remove or restrict default roles
    • B) Grant all permissions to public
    • C) Change superuser to read-only
    • D) Enable all default privileges
29. Which PostgreSQL log file setting is useful for monitoring failed login attempts?
    • A) log_connections
    • B) log_disconnections
    • C) log_min_error_statement
    • D) log_line_prefix
30. How can you prevent SQL injection in PostgreSQL?
    • A) Use parameterized queries
    • B) Sanitize user inputs
    • C) Limit user privileges
    • D) All of the above

Answer Key

Qno	Answer
1	A) CREATE ROLE <role_name>;
2	C) Both A and B
3	A) WITH LOGIN
4	D) DROP ROLE <role_name>;
5	B) ALTER ROLE <role_name>;
6	A) Allows roles to inherit permissions
7	B) ALTER ROLE <role_name> WITH INHERIT;
8	A) It inherits all granted privileges
9	A) Assign SUPERUSER attribute
10	A) ALTER ROLE <role_name> INHERIT;
11	C) GRANT <permissions> ON <table_name> TO <role_name>;
12	B) REVOKE <privilege> ON <object> FROM <role_name>;
13	C) Allows the role to grant the privilege to others
14	B) \dp <table_name> in psql
15	A) Grants all privileges
16	B) REVOKE ALL ON <table_name> FROM <role_name>;
17	B) UPDATE
18	A) Yes
19	C) REVOKE ALL PRIVILEGES BY <role_name>;
20	A) CREATE
21	B) SUPERUSER
22	A) Centralized management of permissions
23	A) Use SSL/TLS encryption
24	B) To configure database authentication
25	D) All of the above
26	B) To detect unauthorized access
27	B) SET password_encryption TO ‘scram-sha-256’;
28	A) Remove or restrict default roles
29	A) log_connections
30	D) All of the above