DevOps: Set up PostgreSQL on Ubuntu Server

In this section, I will go through the steps to set up a PostgreSQL database server on Ubuntu Server 22.04

Prerequisite:

• A VM installed Ubuntu Server 22.04, named dbserver

• Default account: dbuser

• IP Address: 192.168.1.220 (example)

Principles:

1. Each service has its own user

2. Each service has its own configuration files

Let's get started!

• ssh to the db server: ssh dbuser@192.168.1.1220

• Switch to root account

• Check update and upgrade the system

• Install PostgreSQL: apt install postgresql postgresql-contrib

• Start the service: systemctl start postgresql.service

• Give it a check: netstat -tlpun

root@dbserver:~# netstat -tlpun
...
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      6192/postgres
...

• It's running, let's examine this 127.0.0.1:5432:

  • 127.0.0.1 local host address, which means you only access this PostgreSQL service from the local machine, it's not public to call from outside.

  • 5432 the port that the service is listening on.

• What do we do now? As the database admin, I would like to create a new database named jvmdb (for example), create a user named jvm_user to use that database, then I will send that DB's credentials to the appropriate person.

• Switch to Postgres's user: sudo -i -u postgres (Principle #1. Each service has its own user), then psql to access the Postgres.

root@dbserver:~# sudo -i -u postgres
postgres@dbserver:~$ psql
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

• Create user jvm_user, set it Create DB and check.

postgres-# CREATE ROLE jvm_user WITH LOGIN PASSWORD 'jvmuser01';
postgres=# ALTER ROLE jvm_user CREATEDB;
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 jvm_user  | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

• Create DB named jvmdb, then grant access to jvm_user.

postgres=# CREATE DATABASE jvmdb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE jvmdb TO jvm_user;
GRANT
postgres=# \list
jvmdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | jvm_user=CTc/postgres

It's not done!

He needs to make this service public.

Principle #2. Each service has its own configuration files.

In Postgres, it's here /etc/postgresql/14/main/pg_hba.conf

root@dbserver:~# vi /etc/postgresql/14/main/pg_hba.conf
// and make changes like this:

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
...
# local         DATABASE  USER  METHOD  [OPTIONS]
  host          jvmdb  jvm_user  0.0.0.0/0  md5
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
...

Quick explanation:

• Local: host -> type of connection -> This case is TCP/IP

• Database: jvmdb -> the name of database

• User: jvm_user -> the user of database

• Method: 0.0.0.0/0 -> the ip address range is allowed to connect to the DB -> 0.0.0.0/0 any ip adresses allowed. THIS CAN POSE A SECURITY RISK.

• md5 is the authentication method, here is the password authentication will use md5 method.

That's it, now we have to restart the postgres service.

root@dbserver:~# systemctl restart postgresql

And that's all for the Admin site. So, the admin will send out the credentials to the right person.

Now, acting as the DB user, I received the credentials from the DB Admin server.

psql -h 192.168.1.220 -U jvm_user -d jvmdb                                                                  ─╯
Password for user jvm_user:
psql (14.11 (Homebrew), server 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
jvmdb=>

Try to create a new table named members

jvmdb=> CREATE TABLE members (
    member_id serial PRIMARY KEY,
    codename varchar (25) NOT NULL,
    description varchar (225),
    created_date date
);
CREATE TABLE
jvmdb=>

...and input a few data.

jvmdb=> INSERT INTO members (codename, description, created_date)
jvmdb-> VALUES
    ('Alpha', 'Lead Developer', '2024-02-26'),
    ('Beta', 'UI Designer', '2024-02-26'),
    ('Gamma', 'Database Administrator', '2024-02-26'),
    ('Delta', 'Software Tester', '2024-02-26'),
    ('Epsilon', 'System Analyst', '2024-02-26');
INSERT 0 5
jvmdb=>

and select...

jvmdb=> SELECT * FROM members;
 member_id | codename |      description       | created_date
-----------+----------+------------------------+--------------
         1 | Alpha    | Lead Developer         | 2024-02-26
         2 | Beta     | UI Designer            | 2024-02-26
         3 | Gamma    | Database Administrator | 2024-02-26
         4 | Delta    | Software Tester        | 2024-02-26
         5 | Epsilon  | System Analyst         | 2024-02-26
(5 rows)

That's all, Hope this helps!