# Zero Trust Access to PostgreSQL with SSO

> Octelium documentation. Canonical page: <https://octelium.com/docs/octelium/latest/management/guide/service/databases/postgresql-sso-zero-trust>.

Octelium provides unified, seamless, zero trust, secretless access, for both `HUMAN` and `WORKLOAD` *Users* (read more about *Users* [here](https://octelium.com/docs/octelium/latest/management/core/user.md)), to any private/internal on-prem PostgreSQL-based database behind NAT as well as to any publicly protected SaaS PostgreSQL-based database (read more about `POSTGRES` *Services* [here](https://octelium.com/docs/octelium/latest/management/core/service/postgres.md)) without having to share and manage passwords (read more about secretless access [here](https://octelium.com/docs/octelium/latest/management/core/service/secretless.md)). Octelium provides the following:

- Secretless access for all *Users* to all PostgreSQL-based databases without sharing and distributing the database passwords to your *Users*.
- Identity-based, context-aware dynamic routing to different database hosts, users and passwords representing different contexts and privileges.
- Centralized identity-based, application-layer (L7) aware access control (read more about access control [here](https://octelium.com/docs/octelium/latest/management/core/policy.md)).
- Enforce dynamic SSO, Hardware-based FIDO2 Passkey/WebAuthn authentication for your *Users* to access sensitive/production databases.
- OpenTelemetry-native, identity-based, L7 aware visibility and auditing that captures requests and responses including serialized JSON body content.
- Seamless horizontal scalability and availability since Octelium operates on top of Kubernetes (read more about how Octelium works [here](https://octelium.com/docs/octelium/latest/overview/how-octelium-works.md)).
- GitOps-friendly declarative, programmable management (read more [here](https://octelium.com/docs/octelium/latest/management/core/overview.md)).

## A Simple Example

In this guide we are going to provide access to an internal PostgreSQL database (check out an example guide for a SaaS PostgreSQL-based database such as CockroachDB [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/cockroachdb.md)). First, we need to create a *Secret* for the PostgreSQL database's password as follows:

```bash
octeliumctl create secret my-pg-password
```

Now we create the *Service* for our database as follows:

```yaml
kind: Service
metadata:
  name: my-postgres
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://my-db.local
    postgres:
      user: postgres
      database: postgres
      auth:
        password:
          fromSecret: my-pg-password
```

> **Note:**
>
> If the upstream is listening over TLS, then you can enable the SSL mode as shown in detail [here](https://octelium.com/docs/octelium/latest/management/core/service/postgres.md#ssl-mode).

> **Note:**
>
> The example above shows an upstream that is directly accessible by the *Cluster*. You can also remotely serve upstreams behind NAT from anywhere as shown in detail [here](https://octelium.com/docs/octelium/latest/management/core/service/overview.md#remotely-via-a-connected-user).

You can now apply the creation of the *Service* as follows (read more [here](https://octelium.com/docs/octelium/latest/management/core/overview.md)):

```bash
octeliumctl apply /PATH/TO/SERVICE.YAML
```

Now after connecting to the *Cluster* via the `octelium connect` command (read more about connecting to *Clusters* [here](https://octelium.com/docs/octelium/latest/user/cli/connect.md)), you can simply access the database whose hostname is at `my-postgres.default` or simply `my-postgres` (read more [here](https://octelium.com/docs/octelium/latest/management/core/service/overview.md#dns)) as follows:

```bash
psql -h my-postgres 
```

You can also connect via the rootless mode and map the *Service* to your host's localhost (read more [here](https://octelium.com/docs/octelium/latest/user/cli/connect.md#rootless)) as follows:

```bash
# Connect as non-root/unprivileged OS user
octelium connect -p my-postgres:5432

# Now access the Service at localhost
psql -h localhost
```

## Dynamic Configuration

You can also provide dynamic secretless access where you can set different users, databases and passwords for different *Users* under different contexts. Read more about dynamic configuration [here](https://octelium.com/docs/octelium/latest/management/core/service/dynamic-config.md). Here is an example where *Users* are dynamically assigned to different PostgreSQL users with different privileges based on their identity.

For example, you could create a `full_access` user for full access as follows:

```sql
-- Create the user
CREATE USER full_access WITH PASSWORD '<YOUR_PASSWORD>';

-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE my_db TO full_access;

-- Connect to the database and grant schema permissions
GRANT ALL PRIVILEGES ON SCHEMA public TO full_access;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO full_access;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO full_access;
```

And another `read_only` user for read-only as follows:

```sql
-- Create the user
CREATE USER read_only WITH PASSWORD '<YOUR_OTHER_PASSWORD>';

-- Grant connect and usage permissions
GRANT CONNECT ON DATABASE my_db TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;

-- Grant select (read) permissions on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
```

Now you define the *Service* so that more privileged *Users* belonging to either of `engineering` or `admins` *Groups* to automatically be assigned to the `full_access` user, and other *Users* (e.g. junior engineers, AI agents) are assigned to the `read_only` user as follows:

```yaml
apiVersion: core/v1
kind: Service
metadata:
  name: my-db
spec:
  mode: POSTGRES
  port: 5432
  dynamicConfig:
    configs:
      - name: read-only
        upstream:
          url: postgres://my-pg:5432
        postgres:
          user: read_only
          database: my_db
          auth:
            password:
              fromSecret: read-only-password
          sslMode: REQUIRE
      - name: full-access
        upstream:
          url: postgres://my-pg:5432
        postgres:
          user: full_access
          database: my_db
          auth:
            password:
              fromSecret: full-access-password
    rules:
      - condition:
          match: ctx.user.spec.groups.hasAny(["engineering", "admins"])
        configName: full-access
      - condition:
          matchAny: true
        configName: read-only
```

> **Note:**
>
> You might also want to read about Octelium's PostgreSQL L7 aware access control [here](https://octelium.com/docs/octelium/latest/management/core/service/postgres.md#access-control) and access logs [here](https://octelium.com/docs/octelium/latest/management/core/service/postgres.md#visibility)

## Authentication

`HUMAN` *Users* can use their emails to authenticate to the *Cluster* via web browsers using *IdentityProviders*. There are currently 3 methods:

- GitHub OAuth *IdentityProvider* as shown in detail [here](https://octelium.com/docs/octelium/latest/management/core/identity-providers.md#github-oauth2)
- OpenID Connect *IdentityProviders* (e.g. Okta, Auth0, etc...) as shown [here](https://octelium.com/docs/octelium/latest/management/core/identity-providers.md#openid-connect).
- SAML 2.0 *IdentityProviders* (e.g. Okta, Entra ID, etc...) as shown [here](https://octelium.com/docs/octelium/latest/management/core/identity-providers.md#saml-20).

Furthermore, `HUMAN` *Users* can register their FIDO2 *Authenticators* (e.g. Yubikeys) in order to natively login later via Passkey (read more [here](https://octelium.com/docs/octelium/latest/management/core/authenticator.md#passkey-login)).

> **Note:**
>
> You can read more about *Authenticators* and WebAuthn/TOTP MFA as shown [here](https://octelium.com/docs/octelium/latest/management/core/authenticator.md).

For `WORKLOAD` *Users*, they can authenticate themselves via the `octelium login` or `octeliumctl login` commands using various ways:

- OAuth2 client credentials (read more [here](https://octelium.com/docs/octelium/latest/management/core/credential.md#oauth2-client-credentials))
- "Secretless" OpenID Connect identity assertions which can be used by `octelium` CLIs and containers running in cloud providers, GitHub Action runners, Kubernetes clusters, etc... (read more [here](https://octelium.com/docs/octelium/latest/management/core/identity-providers.md#oidc-assertion)).
- Access tokens directly issued and used as bearer authentication tokens (read more [here](https://octelium.com/docs/octelium/latest/management/core/credential.md#access-tokens)).

## Visibility

Octelium also provides OpenTelemetry-ready, application-layer L7 aware visibility and access logging in real time (read more about visibility [here](https://octelium.com/docs/octelium/latest/management/core/visibility.md)). Here is an example:

```yaml
{
  "apiVersion": "core/v1",
  "entry": {
    "common": {
      // Omitted for the sake of brevity of the example
    },
    # !mark(1:8)
    "info": {
      "postgres": {
        "query": {
          "query": "CREATE DATABASE db01;"
        },
        "type": "QUERY"
      }
    }
  },
  "kind": "AccessLog",
  "metadata": {
    // Omitted for the sake of brevity of the example
  }
}
```