Let's Configure a PostgreSQL Database Server for the Blog

I’m Rebuilding Write Software, Well Blog in Ruby on Rails

After three years on Ghost, I’m rebuilding this blog from scratch with Ruby on Rails. In a new series, I’ll document the journey of building a production Rails application, from development to deployment. Follow along as I build the new home for Write Software, Well.

Write Software, WellAkshay Khot

In the previous post, I created a new Rails application for my blog. In this post and the next one, I will deploy the app to a staging environment.

Personally, I prefer to have a staging environment up and running as soon as I have created a new Rails app. This lets us figure out environment-specific issues while the app is at its lowest level of complexity. By deploying a minimal codebase, we can isolate and resolve infra, networking, or config changes early on. It de-risks the entire development and makes future deployments much more predictable.

For my blog, the staging environment infrastructure will consist of two servers on DigitalOcean, one for the Rails application and the other for the PostgreSQL database. In this post, we'll provision and configure the servers, and in the next post we'll configure the Rails staging environment and deploy to it.

By the end of the next post, we'll have a live application running at staging.writesoftwarewell.com, ready for continuous development, deployment, and testing. Most of these steps (plus some more) apply for a production database setup as well, which we'll cover in a future post.

Provision the Application and Database Servers

I started writing a detailed guide to provision the servers with screenshots and everything, but realized that it will get outdated anytime there's a change in upstream documentation. Hence, I suggest that you read the official documentation of DigitalOcean or whichever service you're using, e.g. AWS or Hetzner.

Here's the official tutorial on DigitalOcean to provision droplets.

How to Create a Droplet | DigitalOcean Documentation

Create Droplets and customize the image, plan, authentication method, and quantity of Droplets you want.

.st0{fill:#0080FF;}

Here're the specs for my staging servers.

Application Server:

• RAM : 2 GB,
• Storage: 70 GB NVMe SSD
• Region: Amsterdam
• OS: Ubuntu

Database Server:

• RAM : 1 GB,
• Storage: 35 GB NVMe SSD
• Region: Amsterdam
• OS: Ubuntu

Note: Don't forget to create and upload an SSH key while provisioning the server so you can SSH into them. Also, make sure you put the droplets in the same default Virtual Private Cloud (VPC) so the app server can securely talk to the database server with minimum latency.

Since I'll use Kamal, we don't actually need to configure the application server. Kamal will handle everything for us. But we do need to configure the PostgreSQL database server, which I'll cover in the rest of this post.

Configure the PostgreSQL Database Server

Since I'm using the "solid trifecta", my cache, queue, and cable all will need a separate database, along with the primary application database. Hence it's nice to have a dedicated database instance running on a cheap server, than to sign up for a managed database service.

Once the database server is up and running and you can SSH into it, we'll install and set up the PostgreSQL instance in it. Then we'll allow the connection from the Rails application server. Finally, we'll restrict public access to the database with firewall.

Here's my Reddit post, which contains lots of solid advice. Check it out.

All right, let's SSH into the database server and install PostgreSQL.

Install PostgreSQL

The simplest way to install PostgreSQL is to run the apt install postgresql command. However, it often installs an older version of the database. If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository.

Check out the official documentation to learn more.

$ sudo apt install -y postgresql-common
$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Update the package lists:
$ sudo apt update

# Install the latest version of PostgreSQL:
$ sudo apt -y install postgresql-17 postgresql-contrib libpq-dev

Note: postgresql-contrib and libpq-dev are two separate but commonly used packages when working with PostgreSQL.

To check if PostgreSQL is installed correctly, run the following command. If it prints the version of the database, you're good to go.

$ psql --version
psql (PostgreSQL) 17.5 (Ubuntu 17.5-1.pgdg24.10+1)

Once PostgreSQL is installed, you can start and stop it and also check its status with the following commands:

$ sudo systemctl start postgresql
$ sudo systemctl stop postgresql
$ sudo systemctl status postgresql

Now that PostgreSQL is up and running, the next step is to access it in the terminal.

How to Access PostgreSQL

Typically, in a terminal, you can access the PostgreSQL database using the psql tool, which is an interactive CLI for the database. With it, you can:

• Connect to a specific database
• Run SQL queries and commands
• Manage tables, indexes, and users
• Import and export data
• Run any other admin tasks

First, check who you are and also see available users. I'm assuming you're still logged into the database server via SSH.

$ whoami
root

$ cut -d: -f1 /etc/passwd
root
postgres

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a two ways to use this account to access Postgres.

One way is to switch over to the postgres account on your server:

$ sudo -i -u postgres
$ psql

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database right away. To exit out of the PostgreSQL prompt, run the following:

$ \q

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

$ exit

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

$ sudo -u postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

Change Passwords

Now that you can access the database, the next step is to change the password of the Linux user as well as the default postgres user, since it has full superadmin access.

First, change the postgres user's Linux password (make sure you're running as a root user)

$ sudo passwd postgres
> enter the new password here

Note: PostgreSQL's internal users and authentication is separate from Linux users. The above password is not related to the database user password. It set a new password for the Linux user named postgres.

Next, change the postgres user's database password (used when the postgres user connects to the database). We'll use this password in the database connection string while connecting to the database from our Rails app.

$ sudo -u postgres psql
ALTER USER postgres WITH ENCRYPTED PASSWORD 'SecurePassword!23';

Now we've successfully set the database password for the postgres user. Next, we'll configure external connections to the database.

Configure External Connections to PostgreSQL

In this section, we'll learn how you can connect to the database from the Rails application server. We'll also disable all other access to the database server for security-reasons. But first,

Try Micro Editor

Before editing the config files, I highly recommend that you install the awesome micro editor (unless you're already a vim wizard).

$ sudo apt install micro

It's a successor to the nano editor, and is very easy to use and intuitive. It lets you scroll larger files with touchpad or mouse (just like you'd do with local files) and you can navigate anywhere by pointing and clicking on the screen.

Also, make note of the private IP addresses of your application server and the database server. Since we're using VPC, they can talk to each other on private IPs.

Let’s say:

• DB Droplet Private IP = 12.112.4.5
• Rails Droplet Private IP = 12.112.4.10

Remember that these are not the public IP addresses of the droplets, but the private ones (and fake, too).

With that, we're ready to allow remote connections to PostgreSQL. For this, we'll edit a couple of PostgreSQL configuration files, which are stored in the /etc/postgresql/17/main directory.

Restrict where PostgreSQL listens for connections

By default, database allows only connections from the local system. To allow other machines to connect to your PostgreSQL server, we need to edit the listen_addresses section in the postgresql.conf file. It specifies which TCP/IP addresses the PostgreSQL server itself will bind to and listen for connections from client applications. It does not restrict who can connect.

$ sudo micro /etc/postgresql/17/main/postgresql.conf

Look for this line:

# listen_addresses = 'localhost'

Uncomment and change it to the private IP address of your database server:

listen_addresses = '12.112.4.5'

To save the changes and exit, type Ctrl + S and then Ctrl + Q.

Here are the other values you can use, but they're not recommended:

• To listen on all IPv4 interfaces, use ‘0.0.0.0’,
• For IPv6 interfaces, use ‘::’
• To listen on all available network interfaces, both IPv4 and IPv6, use ‘*’.

Now that we've configured where PostgreSQL will listen for incoming connections, the next obvious step is to control who can connect to the database server.

Restrict who can connect to PostgreSQL

Finally, we need to configure who can connect to the PostgreSQL server, from where, using which authentication method. The pg_hba.conf file, which stands for host-based authentication config, controls that.

$ sudo micro /etc/postgresql/17/main/pg_hba.conf

Edit the IPv4 line like this, which only allows the Rails application server to connect to the database server:

host    all             all             12.112.4.10/32            scram-sha-256

• host: Applies to TCP/IP connections.
• all: Allow all databases.
• all: Allow all users.
• 12.112.4.10/32: Matches the application server's private IP address.
• scram-sha-256: Perform SCRAM-SHA-256 authentication to verify the user's password.

After making all these changes, restart PostgreSQL:

$ sudo systemctl restart postgresql

Next, we'll disable all external access to the database server with the firewall.

Set up firewall to restrict public access to database

First, I'll set up the ufw firewall and allow SSH before enabling the firewall. If you don't, you will lock yourself out. Then, we'll block all incoming requests to the server and only allow requests from the Rails application server on the default PostgreSQL port, which is 5432.

$ sudo apt install ufw
$ sudo ufw allow ssh
$ sudo ufw default deny incoming
$ sudo ufw allow from 12.112.4.10 to any port 5432

Next, enable the firewall.

$ sudo ufw enable

You can check the status of the firewall at any time with the following command.

$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW IN    Anywhere
5432                       ALLOW IN    12.112.4.10
22/tcp (v6)                ALLOW IN    Anywhere (v6)

Note: We don't need to enable HTTP(S) on the database server, since we won't be using an HTTP client to connect to the database server.

That's it. We have configured the database server to only accept connections from the Rails application server over the VPC, and we've restricted the public access to the server. To connect to the database from the Rails app, here're the details you'll need.

• Host: 12.112.4.5
• User: postgres
• Password: the one you set above, i.e. SecurePassword!23
• Port: 5432

Or we can use the following connection string.

# postgresql://user:password@host:port

postgresql://postgres:SecurePassword!23@12.112.4.5:5432

Just make a note of the connection string somewhere, we'll need it in the next post.

In the next post, we'll configure the staging environment and use Kamal to deploy the Rails application to the staging server. If you'd like to receive the future posts in the series by email, subscribe to the blog.

Bonus: Useful PostgreSQL Commands

Once you can access the database with psql, here are some useful commands you can run.

# list users
$ \du

# list all databases
$ \l

# list all tables or a particular table
$ \d
$ \d comments

# check connection info
$ \conninfo
You are connected to database "blog" as user "akshay" via socket in "/tmp" at port "5432"

# create User
$ CREATE USER blogger WITH PASSWORD '1234@data' CREATEDB;

# create database (no need to do this, as Rails will create it for you)
$ create database sample_db;

# grant user access to a database
$ grant all privileges on database mydb to myuser;

That's a wrap. I hope you found this article helpful and you learned something new.

As always, if you have any questions or feedback, didn't understand something, or found a mistake, please leave a comment below or send me an email. I reply to all emails I get from developers, and I look forward to hearing from you.

If you'd like to receive future articles directly in your email, please subscribe to my blog. Your email is respected, never shared, rented, sold or spammed. If you're already a subscriber, thank you.