8.6. Securing PostgreSQL by limiting access to authenticated local users

Procedure

1. Install PostgreSQL:

   # dnf install postgresql-server

2. Initialize a database storage area using one of the following options:

   1. Using the initdb utility:

      $ initdb -D /home/postgresql/db1/

      The initdb command with the -D option creates the directory you specify if it does not already exist, for example /home/postgresql/db1/. This directory then contains all the data stored in the database and also the client authentication configuration file.

   2. Using the postgresql-setup script:

      $ postgresql-setup --initdb

      By default, the script uses the /var/lib/pgsql/data/ directory. This script helps system administrators with basic database cluster administration.

3. To allow any authenticated local users to access any database with their usernames, modify the following line in the pg_hba.conf file:

   local   all             all                                     trust

   This can be problematic when you use layered applications that create database users and no local users. If you do not want to explicitly control all user names on the system, remove the local line entry from the pg_hba.conf file.

4. Restart the database to apply the changes:

   # systemctl restart postgresql

   The previous command updates the database and also verifies the syntax of the configuration file.