Golang: Connecting to posgres using ssl

In this article, you will learn how to host a postgres server with ssl enabled using docker and connect to it using golang.

1. Hosting postgres with ssl

1.1 Setting up root certificate

First, we need to create a root certificate which can issue server and client certificates.

openssl genrsa -des3 -out root.key 4096
openssl rsa -in root.key -out root.key
openssl req -new -x509 -days 365 -subj "/CN=postgres" \
-key root.key -out root.crt

1.2 Setting up server certificate

Next, we create a server certificate for the postgres server. The CN should be the domain name the postgres server is hosted in. In our case, we are hosting it in localhost.

openssl genrsa -des3 -out server.key 4096
openssl rsa -in server.key -out server.key
openssl req -new -key server.key -subj "/CN=localhost" \
    -text -out server.csr
openssl x509 -req -in server.csr -text -days 365 \
    -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

1.3 Posgres config file

Enable ssl in postgres config file postgresql.conf. Set parameters for ssl as on and provide the location of root certificate, server certificate and server key. You can find all the configurations you can set in this file here.

ssl = on
ssl_ca_file = '/etc/postgres/security/root.crt'
ssl_cert_file = '/etc/postgres/security/server.crt'
ssl_key_file = '/etc/postgres/security/server.key'
password_encryption = scram-sha-256
listen_addresses = '*'
port = 8342

1.4 Client authentication config

Client authentication configuration for postgres is provided in pg_hba.conf file. We need to allow client connection using ssl. The pg_hba.conf file should look like this.

local     all      all                md5
host      all      all  127.0.0.1/32  md5
hostssl   all      all  0.0.0.0/0     cert clientcert=1

1.5 Docker file

Now, we need to create a docker file for the postgres server. We need to set username and password for the postgres server. Then copy the config files, server key and certificates  to the docker image. The docker file will look like this:

FROM postgres:12-alpine

ENV POSTGRES_USER=fenix
ENV POSTGRES_PASSWORD=password123

COPY pg_hba.conf postgresql.conf /etc/postgresql/config/
COPY --chown=postgres:postgres root.crt server.crt server.key /etc/postgres/security/
EXPOSE 8342
CMD ["postgres", "-c", "config_file=/etc/postgresql/config/postgresql.conf", "-c", "hba_file=/etc/postgresql/config/pg_hba.conf"]

1.6 Host the postgres server

Now that the docker file for the postgres server is created. Now we need to run it. Please ensure that the certificate files and config files are in the same folder as the Dockerfile. Then build the docker image using the following command:

docker build -t postgres .

Once the docker image is build we need to run the image.

docker run -d -p 8342:8342 -v '/data/pg':'/var/lib/postgresql/data' postgres

The above command will run the postgres server as a daemon on port 8342.

2. Connecting from go

2.1 Creating client certificate

Next, we need to create client certificate using which we can access the postgres server.

openssl genrsa -out client.key 4096
openssl rsa -in client.key -out client.key
openssl req -new -key client.key -subj "/CN=fenix" \
    -out client.csr
openssl x509 -req -in client.csr -CA root.crt \
    -CAkey root.key -CAcreateserial -days 365 \
    -text -out client.crt

2.2 Test connection using psql

Now lets test if you can connect to postgres server using the following command:

psql "host=localhost port=8432 user=fenix dbname=postgres sslmode=verify-full sslrootcert=root.crt sslkey=client.key sslcert=client.crt"

2.3 Connect using gorm

package main

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"log"

	"github.com/lib/pq"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type Test struct {
	gorm.Model
	Id       int            `db:"id"`
	Name     string         `db:"name"`
	Age      int            `db:"age"`
	Tags     pq.StringArray `gorm:"type:text[]"`
	Marks    pq.Int64Array  `gorm:"type:int[]"`
}

func main() {
	db, err := gorm.Open(postgres.Open("host=localhost port=8342 user=fenix dbname=postgres sslmode=verify-full sslrootcert=root.crt sslkey=client.key sslcert=client.crt"), &gorm.Config{})
	if err != nil {
		log.Println("Unable to connect to database. Err:", err)
		return
	}

	log.Println("Successfully connected to database!", db)
	err = db.AutoMigrate(&Test{})
	if err != nil {
		log.Println("Unable to migrate table. Err:", err)
		return
	}


	// Simple creation and selection
	db.Create(&Test{Name: "Fenix", Age: 20, Tags: []string{"test1", "test2", "test3"}, Marks: []int64{100, 20, 99}})
	t := &Test{}
	db.First(t, 6)
	fmt.Println(t)
}