Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.3 Configuration files (pg_hba.conf)

Sample Question

2.24

In which cases is a password required when connecting, if pg_hba.conf is defined as below? Select all that apply. The IP address of the machine cl1 is 192.168.1.10 and the IP address of the machine cl2 is 192.168.1.11.

host db1 u1 192.168.1.10/32 trust
host db2 u2 192.168.1.11/32 md5
host db1 all 192.168.1.0/24 md5
host db2 all 192.168.1.0/24 trust

 
  1. when connecting from machine cl1 to database db1 by user u1

  2. When connecting from machine cl1 to database db2 by user u1

  3. When connecting from machine cl1 to database db1 by user u2

  4. When connecting from machine cl2 to database db1 by user u2

  5. When connecting from machine cl2 to database db2 by user u2

  6. When connecting from machine cl2 to database db2 by user u1

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

Answer and Explanation

PostgreSQL maintains client authentication in a file called pg_hba.conf. "hba" stands for Host-Based Authentication, and it describes the authentication method used by each host to allow (or deny) connections.

For connections over TCP/IP, start with host, followed by the database name, user name, client IP address, and authentication method, separated by spaces, on one line. If a row matches the database name, user name, and IP address (the upper row takes precedence if there is more than one), the authentication method for that row is used, and if there is no matching row, the connection is rejected.

You can either write individual names for both database and user names, write multiple names separated by commas, or write "all" to specify all. The IP address is specified with a network such as /32 for a single IPv4 host or /24 for multiple hosts. Alternatively, you can write the host name.

If the authentication method is trust, connection is allowed without requiring a password. To authenticate with a password, specify md5. Other authentication methods are also available. Please refer to the documentation for details.

Let's look at each configuration line in the pg_ hba.conf shown in the question. The first line allows user u1 to connect to database db1 from machine cl1 without a password. The second line shows that user u2 is required to use a password to connect to database db2 from machine cl2. The third line shows that, for all users, a password is required to connect to database db1 from the network containing machines cl1 and cl2. The fourth line allows all users to connect to database db2 from the network containing machines cl1 and cl2 without a password.

Now let's look at the individual options. A is exactly what the first line indicates, so no password is required. B matches the fourth line, so no password is required either. C matches the third line, so a password is required. D also matches the third line, so a password is required. E matches both lines 2 and 4, but a password is required because line 2 takes precedence. F matches the fourth line, so no password is required.

Therefore, the correct answers are C, D, and E.

This question is about understanding how to confiture, so trust is used as the authentication method. However, in real world database operations, you will rarely use trust unless you are connecting to a test database from a specific environment. Make sure you understand the limitations that can be put in place, as this can be an important part of security design.