# Passwordless Access to Self-Hosted Postgres
{% hint style="danger" %}
### **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](https://developers.cloudflare.com/cloudflare-one/applications/non-http/infrastructure-apps/) service.
{% endhint %}
## Introduction
In this guide, we'll show you how to set up passwordless access to a self-hosted Postgres database using our SplitCert technology.
{% hint style="info" %}
**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
{% endhint %}
## Step 1: Prerequisites
There are a few things needed in order to hit the ground running.
1. A [BastionZero agent](/docs/deployment/installing-the-agent.md) with a version `>= 7.6.0` installed on a remote server or cluster
2. A remote server that is hosting a local [Postgres](https://www.postgresql.org/) database
3. The server that hosts the BastionZero agent (mentioned in step 1) is able to make an inbound connection to your local [Postgres](https://www.postgresql.org/) instance
4. The [Zero Trust Command Line Interface (ZLI)](/docs/deployment/installing-the-zli.md) with a version `>= 6.20.7` is installed on your local machine
{% hint style="info" %}
Instructions for installing the [BastionZero agent](/docs/deployment/installing-the-agent.md) and/or the [ZLI](/docs/deployment/installing-the-zli.md) are found in our [deployment](/docs/deployment/getting-started.md) section.
{% endhint %}
## 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:
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](https://cloud.bastionzero.com).
2. Navigate to the "[Targets](https://cloud.bastionzero.com/admin/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](https://cloud.bastionzero.com) 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](https://cloud.bastionzero.com). See our [ZLI Reference Guide](/docs/zli-reference-manual.md#policy) for information on authoring policy from the `zli`.
1. In the [web app](https://cloud.bastionzero.com), 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.
{% hint style="info" %}
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.
{% endhint %}
### 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
{% hint style="info" %}
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.
{% endhint %}
### 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
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.bastionzero.com/docs/how-to-guides/passwordless-database-access/passwordless-access-to-self-hosted-postgres.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.