SSL connections for AlloyDB Omni

AlloyDB Omni is the downloadable version of the Google Cloud fully PostgreSQL compatible database service AlloyDB. It is packed with a lot of improvements in performance, scalability and management. You can read more about AlloyDB Omni in the Google blog. The deployment and management parts of the AlloyDB Omni is left to the customer and it makes perfect sense — it is deployed in the customer’s environment and depends on internal requirements. But there are some best practices we probably all should follow. One of them is securing connections to our databases. It is important to keep communications between applications and database instances secure and encrypted. In the blog I will show you how to enable basic SSL encryption for database connections using self-signed server certificates.

First let me describe in a couple of words how SSL works.

• Client connects to the server
• Server communicates back with SSL capabilities and sends certificate to the client
• Client get the certificate and if “sslmode” is “verify-ca” then it verifies it using certificate authority
• Client generate, encrypt symmetric key and send the encrypted key to the server
• Server and client are using the key to encrypt the traffic

PostgreSQL supports several SSL modes for the connections described on the documentation page. Here we are going to limit our scope by ssl modes “prefer” and “require” when self-signed certificates are not verified by the client.

By default the AlloyDB is started without any certificate and allows unencrypted communications. That’s probably fine for development and non-production testing when no private or real data is in use. But when it comes to more serious matters we start to think about security. So, how do we approach it? Let me follow you step-by-step.

Let’s assume you’ve already installed AlloyDB Omni using the single container approach. If you need a guide — here you can read how to do that. My database server has IP address 10.128.0.2 and my client is deployed in the same network. What would happen if we tried to connect?

gleb@psqlclient:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db sslmode=disable"
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
Type "help" for help.

quickstart_db=#

It is connected and encryption is not enabled. Now we try to enforce SSL from the client side.

gleb@psqlclient:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db ssqlmode=require"
psql: error: invalid connection option "ssqlmode"
gleb@psqlclient:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db sslmode=require"
psql: error: connection to server at "10.128.0.2", port 5432 failed: server does not support SSL, but SSL was required
gleb@psqlclient:~$

We can see that the server doesn’t support encryption as of now. Let’s fix it. We need to create a self-signed certificate, place it to the path accessible by the postgres and adjust some instance parameters.

On the VM where we have deployed the AlloyDB Omni container we are creating the RSA key for the SSL certificate using openssl utility.

gleb@omniserver:~$ openssl enrsa -aes128 2048 > server.key
Invalid command 'enrsa'; type "help" for a list.
gleb@omniserver:~$ openssl genrsa -aes128 2048 > server.key
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
gleb@omniserver:~$

It has been created with a password phrase and we don’t want it for our case. We are removing it by importing and exporting the key.

gleb@omniserver:~$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key
gleb@omniserver:~$

Then we adjust the permissions for the key.

gleb@omniserver:~$ chmod 400 server.key

Now we can create our certificate using the server.key. I’ve used only Canonical Name (CN) as a parameter but you can add other certificate properties.

gleb@omniserver:~$ openssl req -new -key server.key -days 365 -out server.crt -x509 -subj "/CN=omniserver.com" 
gleb@omniserver:~$

And copy the same certificate as a root certificate. Remember, we are using self-signed certificate approach.

gleb@omniserver:~$ cp server.crt root.crt
gleb@omniserver:~$

Now we can copy both certificates to the PGDATA path in our container. To get the path we can check the PGDATA environment variable for the user postgres in our container.

gleb@omniserver:~$ sudo docker exec -u postgres my-omni env | grep PGDATA
PGDATA=/var/lib/postgresql/data
gleb@omniserver:~$

Now we can copy our key and both certificates to the $PGDATA.

gleb@omniserver:~$ sudo docker cp server.crt my-omni:/var/lib/postgresql/data/
gleb@omniserver:~$ sudo docker cp root.crt my-omni:/var/lib/postgresql/data/
gleb@omniserver:~$ sudo docker cp server.key my-omni:/var/lib/postgresql/data/

And adjust ownership for the copied files.

gleb@omniserver:~$ sudo docker exec my-omni chown postgres:postgres /var/lib/postgresql/data/server.crt
gleb@omniserver:~$ sudo docker exec my-omni chown postgres:postgres /var/lib/postgresql/data/root.crt
gleb@omniserver:~$ sudo docker exec my-omni chown postgres:postgres /var/lib/postgresql/data/server.key

The next step is to configure the instance parameters to enable the SSL. We will keep all parameters as default and only switch the “ssl” to “on” and restart the container.

gleb@omniserver:~$ sudo docker exec my-omni /bin/bash -c "echo ssl = on >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
gleb@omniserver:~$

The server now supports SSL and we can connect from our client machine with “sslmode=require”.

gleb@psqlclient:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db sslmode=require"
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

quickstart_db=#

You can see in the output that we have connected using TSLv1.3. That’s great but even better if we can enforce our connections to use SSL. It can be done by modifying the pg_hba.conf file in our container.

gleb@omni01:~$ sudo docker exec my-omni /bin/bash -c "sed -i 's/host all all all scram-sha-256/hostssl all all all scram-sha-256/' /var/lib/postgresql/data/pg_hba.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
gleb@omni01:~$ 

We replaced “host” by “hostssl” for all but localhost connections and, as result, any non-ssl modes will be rejected by the server.

gleb@omniserver:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db sslmode=disable"
psql: error: connection to server at "10.128.0.2", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.128.0.2", user "postgres", database "quickstart_db", no encryption
gleb@omniserver:~$ psql "host=10.128.0.2 user=postgres dbname=quickstart_db sslmode=prefer"
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

quickstart_db=#

That’s how to enable the SSL using a self-signed certificate. But if your company uses proper certificates with registered root authority then you need to create a key and request the server certificate and root certificate from your company’s certificate authority. Then you will be using it the same way as the self-signed certificate. There are some other more advanced SSL modes such as “verify-ca” and “verify-full” but they deserve a dedicated blog.

You can try AlloyDB Omni right now using instructions provided on the AlloyDB documentation page and let us know how it works for you. Any feedback is very welcome. Happy testing!