Passwordless Access to Self-Hosted Postgres

Enable users to access a self-hosted Postgres database without the use of passwords

The BastionZero product is maintained for existing BastionZero customers only.

Moving forward, we are natively rebuilding BastionZero’s technology as Cloudflare’s Access for Infrastructure service.

Introduction

In this guide, we'll show you how to set up passwordless access to a self-hosted Postgres database using our SplitCert technology.

There are five major steps to this process:

1. Ensure all prerequisites are met

2. Configure your Postgres database to work with SplitCert

3. Create your SplitCert database target

4. Author your policy

5. Generate your certificate and configure it on your SplitCert database target

Step 1: Prerequisites

There are a few things needed in order to hit the ground running.

1. A BastionZero agent with a version >= 7.6.0 installed on a remote server or cluster

2. A remote server that is hosting a local Postgres database

3. The server that hosts the BastionZero agent (mentioned in step 1) is able to make an inbound connection to your local Postgres instance

4. The Zero Trust Command Line Interface (ZLI) with a version >= 6.20.7 is installed on your local machine

Instructions for installing the BastionZero agent and/or the ZLI are found in our deployment section.

Step 2: Configure your Postgres database

Because different Linux distributions use different path conventions, the exact commands and files used to configure your database may vary. We cover two common patterns below, but the configuration itself is agnostic to operating system and will work on any system that Postgres runs on.

For those running on Ubuntu

1. Navigate to your Postgres configuration directory. This is typically found at /etc/postgresql/12/main.

2. Edit the primary Postgres configuration file, postgresql.conf. Find the heading # - SSL - (~ around line 100) and comment out any content underneath it. Replace it with the following lines:

   ssl = on
   ssl_ca_file = '/etc/postgresql/12/main/pgconf/ca.crt'
   ssl_cert_file = '/etc/postgresql/12/main/pgconf/server.crt'
   ssl_key_file = '/etc/postgresql/12/main/pgconf/server.key'
   listen_addresses = '*'

3. Edit the host-based authentication file. Located in the same directory, this one is called pg_hba.conf. Replace the entire non-comment contents of this file with the following configuration:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   # "local" is for Unix domain socket connections only
   local   all             all                                     trust
   
   # SSL connections:
   hostssl all             all             0.0.0.0/0               cert clientcert=verify-full

4. Make a directory to store certs and keys. From the same Postgres configuration directory, do the following:

   mkdir /etc/postgresql/12/main/pgconf
   cd /etc/postgresql/12/main/pgconf
   touch ca.crt server.crt server.key
   chmod 600 *
   chown postgres *

5. Restart your Postgres instance to ensure the reconfiguration is valid.

   systemctl restart postgresql && systemctl status postgresql 

6. Ensure your configuration was successful. The below command should return a status of "RUNNING."

   systemctl status postgresql-12

Your Postgres instance is now configured to accept certificate-based authentication rather than the default behavior, which is to prompt for a password.

For those running on CentOS or Amazon Linux

1. Find your Postgres configuration directory. This is typically found at /var/lib/pgsql/12/data.

2. Edit the primary Postgres configuration file, postgresql.conf. Find the heading # - SSL - (~ around line 100) and delete or comment out any content underneath it. Replace it with the following lines:

   ssl = on 
   ssl_ca_file = '/var/lib/pgsql/12/data/pgconf/ca.crt'
   ssl_cert_file = '/var/lib/pgsql/12/data/pgconf/server.crt'
   ssl_key_file = '/var/lib/pgsql/12/data/pgconf/server.key'
   listen_addresses = '*'

3. Edit the host-based authentication file. Located in the same directory, this one is called pg_hba.conf. Replace the entire non-comment contents of this file with the following configuration:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   # "local" is for Unix domain socket connections only
   local   all             all                                     trust
   
   # SSL connections:
   hostssl all             all             0.0.0.0/0               cert clientcert=verify-full

4. Make a directory to store certs and keys. From the same Postgres configuration directory, do the following:

   mkdir /var/lib/pgsql/12/data/pgconf
   cd /var/lib/pgsql/12/data/pgconf
   touch ca.crt server.crt server.key
   chmod 600 *
   chown postgres *

5. Restart your Postgres instance to ensure the reconfiguration is valid.

   systemctl restart postgresql-12 && systemctl status postgresql-12 

6. Ensure your configuration was successful. The below command should return a status of "RUNNING."

   systemctl status postgresql-12

Your Postgres instance is now configured to accept certificate-based authentication rather than the default behavior, which is to prompt for a password.

Step 3: Create your SplitCert database target

1. Navigate to the BastionZero web app.

2. Navigate to the "Targets" section of the BastionZero cloud app.

3. Click "Create" in the top right corner and select "Database" underneath the "Target" section.

4. Name your database target. We recommend giving it a descriptive name for ease of reference.

5. Select the Proxy Target or Proxy Environment as the proxy type. This should be either the target you installed the BastionZero agent on that has access to your database, or an environment with multiple targets with the BastionZero agent installed.

6. Select "SplitCert" for the Authentication Type and "Unmanaged Postgres" as the Database Type.

7. Enter the remote host address and port where your database can be reached by your proxy target.

8. When entering the local host information, the local port entry is optional. If you do not specify a port, BastionZero will select an available port at the time a database connection is made.

9. Select the environment to place this database target into.

10. Click "Add" to create the database target.

11. You should see this target appear on the Targets page.

Selecting your target as a SplitCert database target is required in order to access your database using a specific database role.

Step 4: Author your policy

Authoring policy can be done in two ways: through the web app or through BastionZero's command-line interface, the Zero Trust Command Line Interface (ZLI).

In this guide, we'll be highlighting how to author a policy using the web app. See our ZLI Reference Guide for information on authoring policy from the zli.

1. In the web app, again click "Create" in the top right corner. This time, select "Policy."

2. Create a proxy policy with access to the environment that your SplitCert database target belongs to or to your SplitCert database target explicitly if you are not utilizing environments. Ensure that you select the IdP user(s), group(s), and service account(s) that need access to your Postgres database. The target user(s) must be specified in order to connect to your database as a specific database role.

   
3. (Recommended) Create a target access policy to the proxy agent to make distributing the SplitCert keys easier. A Tunnel-only policy is sufficient, but you could instead include Shell access if you prefer to copy/paste the keys using the clipboard.

   

Step 5: Generate your certificate, configure your SplitCert database target, and configure your Postgres database

In order to facilitate passwordless access to your Postgres database, SplitCert uses an X.509 certificate where the private key is split into two key shards - one is held on the same server where the BastionZero agent is installed and the other is held by BastionZero. Access is granted to your database only when both shards co-sign a client certificate and a valid policy exists allowing the IdP user access.

In the below sections, you will first generate your certificate, which is then separated into two key shards. BastionZero will provision your SplitCert target with one key shard. It is your job to enable your database to permit authentication using SplitCert.

Generate your certificate and configure your SplitCert database target

1. Make sure your SplitCert database target is online before completing step 2 below. In order for a target to be configured with a certificate, it must be online.

2. Generate a certificate using:

   zli generate certificate --targets {name of your SplitCert database target} --selfHosted

   This command will both create the X.509 certificate, split its private key into two key shards, and configure your BastionZero database target with the necessary key shard. It will also put the second key shard in a newly-created local directory that is timestamped with the time your certificate was created.

If you have multiple database targets you would like to provision with SplitCert, you can use the --all flag rather than naming database targets explicitly.

Configure your Postgres database

1. To simplify this step, we recommend configuring SSH access so you can copy files to your targets directly.

   zli generate sshConfig

   This command will update your SSH configuration file with a BastionZero-specific config (which is added using an include statement) that will translate the tunnel or shell permissions you configured via policy in Step 4.3 above into a configuration file that allows you to connect to your target using native SSH commands.

2. Move the files from the newly-created directory onto your proxy target (this is the target listed as "Proxy Target" in your database target).

   • On Ubuntu, this may look like scp -r ./{your-datestamped-folder}/* =root@ubuntu-splitcert-demo:/etc/postgresql/12/main/pgconf.

   • On CentOS, this may look like scp -r ./{your-datestamped-folder}/* root@ubuntu-splitcert-demo:/var/lib/pgsql/12/data/pgconf/pgconf.

3. If you have shell permissions, you need to manually copy and paste the contents of your local files into their remote counterparts.

4. Restart your Postgres instance. The easiest way to do this is via a single ssh command.

   # For Ubuntu
   ssh root@ubuntu-splitcert-demo systemctl restart postgresql
   
   # For CentOS or Amazon Linux
   ssh root@ubuntu-splitcert-demo systemctl restart postgresql-12

Step 6: Access your database

1. Create a new connection to your database target using zli connect. Make sure you specify the target user you configured during policy setup (i.e., connect postgres@ubuntu-splitcert-pg). You are looking for the zli to output a success message like the following: > Started db daemon at localhost:6105 for ubuntu-splitcert-pg.

2. You can access your database in two ways:

   • Through a client application

   • Through the command line

In both of the examples below, we are setting sslmode to disable, which tells our local database client not to send a client certificate to the server. In this case, the client certificate is generated just-in-time by the SplitCert protocol.

Through your favorite client application

In this example, we're accessing a Postgres database through DBeaver.

Create a new connection to your Postgres database

Disable SSL mode

When you create a new connection, select "PostgreSQL" from the database selection. Make any modifications to the connection necessary (i.e., if you are running it on a custom port rather than the default).

The critical step is making sure you check the use SSL box and then under the "Advanced" section, select "disable" for SSL mode.

Through the command line

In this example, we're using psql from the command line to access a Postgres database.

psql -h localhost -p 55432 -U postgres sslmode=disable
# or
PGSSLMODE=disable psql -h localhost -p 55432 -U postgres