Golang connect to Postgres using SSL certificate

Question

First of all, question is languate-agnostic. I'm trying to write a simple application that connects to PostgreSQL using SSL.

1. I created certificates using scripts:

# Create CA private key
openssl genrsa -des3 -out root.key 4096
#Remove a passphrase
openssl rsa -in root.key -out root.key

# Create a root Certificate Authority (CA)
openssl \
    req -new -x509 \
    -days 365 \
    -subj "/CN=localhost" \
    -key root.key \
    -out root.crt

# Create server key
openssl genrsa -des3 -out server.key 4096
#Remove a passphrase
openssl rsa -in server.key -out server.key

# Create a root certificate signing request
openssl \
    req -new \
    -key server.key \
    -subj "/CN=localhost" \
    -text \
    -out server.csr

# Create server certificate
openssl \
    x509 -req \
    -in server.csr \
    -text \
    -days 365 \
    -CA root.crt \
    -CAkey root.key \
    -CAcreateserial \
    -out server.crt

2. I created a database using:

init.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE TESTING_DATA (
    ID SERIAL PRIMARY KEY,
    UUID UUID UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
    NAME TEXT NOT NULL,
    INFO NUMERIC(3, 2)
);

INSERT INTO TESTING_DATA (NAME, INFO)
VALUES
    ('Notebook', 1),
    ('Office supplies', 2),
    ('Pencil', 2),
    ('Office supplies', 1),
    ('Eraser', 1),
    ('Coffee', 1),
    ('Cookies', 2),
    ('Puzzles', 5)
;

postgresql.conf

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

pg_hba.conf

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

Dockerfile

FROM postgres:12-alpine

ENV POSTGRES_USER=pguser
ENV POSTGRES_PASSWORD=pgpassword
ENV POSTGRES_DB=securitylearning

COPY pg_hba.conf postgresql.conf /etc/postgresql/config/
COPY --chown=postgres:postgres root.crt server.crt server.key /etc/postgres/security/
COPY init.sql /docker-entrypoint-initdb.d/
EXPOSE 5432
CMD ["postgres", "-c", "config_file=/etc/postgresql/config/postgresql.conf", "-c", "hba_file=/etc/postgresql/config/pg_hba.conf"]

I launched a container, I ensured that from the container itself I can connect to database and select something from the table.

3. I created a simple program: server.go

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

func main() {
    connection := fmt.Sprint(
        " host=localhost",
        " port=5432",
        " user=pguser",
        " dbname=securitylearning",
        " sslmode=verify-full",
        " sslrootcert=root.crt",
        " sslkey=client.key",
        " sslcert=client.crt",
    )
    db, err := sql.Open("postgres", connection)
    defer db.Close()
    if err != nil {
        panic(err)
    }
    err = db.Ping()
    if err != nil {
        panic(err)
    }
    row := db.QueryRow("SELECT * FROM TESTING_DATA")
    fmt.Println(row)
}

I tried to:

• place files root.crt, server.crt, server.key next to the compiled binary and add to the connection string in go file sslrootcert, sslcert, sslkey respectively
• place same files, but with names root.crt, postgresql.crt, postgresql.key in ~/.postgresql/ directory, because pq uses them by default.

For now, it's not working. I randomly get one of those two errors:

read: connection reset by peer

or

EOF

Could you please help? What am I missing here? Or could you point me to some resources? Thanks in advance.

Update 1 Thanks to suggestion in comments, I created client key and certificate, using

# Create client key
openssl genrsa -out client.key 4096
#Remove a passphrase
openssl rsa -in client.key -out client.key

# Create client certificate signing request
openssl \
    req -new \
    -key client.key \
    -subj "/CN=172.17.0.2" \
    -out client.csr

# Create client certificate
openssl \
    x509 -req \
    -in client.csr \
    -CA root.crt \
    -CAkey root.key \
    -CAcreateserial \
    -days 365 \
    -text \
    -out client.crt

I'm using 172.17.0.2 in CN, because it's host IP from docker container's perspective.

I've tried both:

• using following keys in connection string from program

" sslrootcert=root.crt",
" sslkey=client.key",
" sslcert=client.crt",

• copying root.crt, client.key, client.srt to ~/.postgresql/, trying psql with

psql "host=localhost port=5432 user=pguser dbname=securitylearning sslmode=verify-full sslrootcert=root.crt sslkey=client.key sslcert=client.crt"

or without password.

Both ways still fail to connect. In psql case I get error psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Answer 1

Thanks to suggestions in comments I managed to solve it.

First of all, as suggested, I stepped back and tried to proceed with smaller steps. Such as, securely connect with psql from host.

Mistake 1

I forgot to add the following property to postgresql.conf

listen_addresses = '*'

The documentation says:

If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it.

Mistake 2

I fell into a little misconception with certificates and their common names (CN). The following points should be applied to scripts that create certificates. In short:

• CN for CA can be anything as long as it is different from the server's CN. See this question and answer for details
• CN for server must be IP/hostname by which we will call server from client (here it's localhost. But if the database would be located at cooldatabase.com <- this would be server's CN)
• CN for client must be username by which we will connect (here, it's pguser)

When I fixed these two issues - I managed to connect via both psql and go program! Also, the default postgresql.conf is very informative!