Skip to main content

Lab 003: Install and Configure PostgreSQL

Requirements​

The Nautilus application development team has shared that they are planning to deploy one newly developed application on Nautilus infra in Stratos DC. The application uses PostgreSQL database, so as a pre-requisite we need to set up PostgreSQL database server as per requirements shared below:

a. Install and configure PostgreSQL database on Nautilus database server.

b. Create a database user kodekloud_cap and set its password to dCV3szSGNA.

c. Create a database kodekloud_db10 and grant full permissions to user kodekloud_cap on this database.

d. Make appropriate settings to allow all local clients (local socket connections) to connect to the kodekloud_db10 database through kodekloud_cap user using md5 method (Please do not try to encrypt password with md5sum).

e. At the end its good to test the db connection using these new credentials from root user or server sudo user.

Note

This lab configures PostgreSQL with md5 authentication for local connections. Ensure that all credential changes are made securely.

Prerequisites​

  • Access to Nautilus database server with root or sudo privileges.
  • PostgreSQL packages available in the system repository.

Steps​

# 1. Install PostgreSQL server and contrib packages
sudo yum install -y postgresql-server postgresql-contrib

# 2. Initialize the PostgreSQL database
sudo postgresql-setup initdb

# 3. Enable and start PostgreSQL service
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql

# 4. Connect to PostgreSQL and create user and database
sudo -u postgres psql << EOF
CREATE USER kodekloud_cap WITH ENCRYPTED PASSWORD 'dCV3szSGNA';
CREATE DATABASE kodekloud_db10 OWNER kodekloud_cap;
GRANT ALL PRIVILEGES ON DATABASE kodekloud_db10 TO kodekloud_cap;
\q
EOF

# 5. Configure PostgreSQL to listen on localhost
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = 'localhost'/g" /var/lib/pgsql/data/postgresql.conf

# 6. Update pg_hba.conf to use md5 authentication for local connections
sudo sed -i 's/local   all             all                                     peer/local   all             all                                     md5/g' /var/lib/pgsql/data/pg_hba.conf
sudo sed -i 's/host    all             all             127.0.0.1\/32            ident/host    all             all             127.0.0.1\/32            md5/g' /var/lib/pgsql/data/pg_hba.conf

# 7. Restart PostgreSQL service
sudo systemctl restart postgresql
sudo systemctl status postgresql

Verification​

Test database connection with the created user:

psql -U kodekloud_cap -d kodekloud_db10 -h localhost -W
# When prompted, enter password: dCV3szSGNA

# If successful, you should connect to the database
# Verify with:
\l
\q

Resources​