PostgreSQL

Setting the Service mode to POSTGRES enables it to operate in application-layer aware PostgreSQL mode. This mode enables you to provide secretless access for Users to the protected upstream PostgreSQL-based server without having to share the upstream's password. This mode also provides you with clear application-layer aware visibility where your Postgres sessions and queries are logged and audited in real-time.

note

Read more about access to POSTGRES from a User perspective here. You can also read more detailed guides here for generic databases behind NAT, here for CockroachDB and here for SaaS-based NeonDB.

Secretless Access

Secretless access enables you to provide secretless access for authorized Users to POSTGRES-based Service by automatically injecting passwords to authenticate to the upstream PostgreSQL database, and force the User to connect to the upstream as a specific user, possibly with a certain role corresponding to specific permissions, to a specific database.

First, you need to create a Secret to store the password of your upstream SSH server (read more here) as follows:

octeliumctl create secret pg-password
# OR via a --value flag
octeliumctl create secret --value <PASSWORD> pg-password
# OR via a --file flag
octeliumctl create secret --file /PATH/TO/PASSWORD pg-password

Now, you define your Service as follows:

kind: Service
metadata:
  name: pg-db
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://address-to-pg
    postgres:
      user: postgres
      database: db01
      auth:
        password:
          fromSecret: pg-password
      sslMode: REQUIRE

Now, the above configuration forces the User to authenticate as postgres with the password value set in the Secret pg-password and connect to the db01 database regardless of the information supplied by the downstream.

note

For internal/private PostgreSQL upstreams behind NAT, you need to remotely serve them via a connected octelium client or container as discussed here.

SSL Mode

By default, the Service attempts to use prefer ssl mode (read more here). You can, however, override that default behavior via the sslMode field and explicitly set it to one of the following modes:

DISABLE disables using TLS.
REQUIRE forces using TLS without verifying the upstream's certificate at all.
VERIFY_CA forces using TLS and additionally verifies that the upstream's certificate chains up to a trusted CA without verifying the upstream's hostname.
VERIFY_FULL forces using TLS and additionally verifies both the upstream's certificate chain and its hostname. This is the recommended mode for upstreams that are listening over TLS.

Here is an example:

kind: Service
metadata:
  name: pg-db
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://address-to-pg
    postgres:
      user: postgres
      database: db01
      auth:
        password:
          fromSecret: pg-password
      sslMode: REQUIRE

For the VERIFY_CA and VERIFY_FULL modes, the upstream's certificate is verified against the system's default trusted CAs. If the upstream is using a certificate that is signed by a private CA, you can set your own trusted CAs via the tls field (read more here). Here is an example:

kind: Service
metadata:
  name: pg-db
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://address-to-pg
    postgres:
      user: postgres
      database: db01
      auth:
        password:
          fromSecret: pg-password
      sslMode: VERIFY_FULL
    tls:
      trustedCAs:
      - |
        -----BEGIN CERTIFICATE-----
        CA_1
        -----END CERTIFICATE-----

Access Control

You can control access based on the Postgres request information. Such information are stored in ctx.request.postgres where it contains the username and database. Here is a detailed example of a inline Policy that controls access based on Postgres-specific information:

kind: Service
metadata:
  name: svc1
spec:
  mode: POSTGRES
  config:
   # Your config....
  authorization:
    inlinePolicies:
      - spec:
          rules:
            - effect: ALLOW
              condition:
                any:
                  of:
                    - match: ctx.request.postgres.connect.user == "db-user-1"
                    - match: ctx.request.postgres.connect.database == "db-01"

note

You do not actually need to control access by checking against the database user since you already override the database user in your Service configuration as illustrated above regardless of the database user value provided by the User. You can also use dynamic configuration in order to map different databases and/or database users to different Users under different conditions. You can read more about dynamic configuration here

By default, the Service enforces authorization only at the beginning of the connection. You can, however, enforce authorization for every PostgreSQL command by setting the authorization.mode field to ALL. In such case, the SQL statement of simple queries is stored in ctx.request.postgres.query.query while the name and SQL statement of extended query protocol parse requests are stored in ctx.request.postgres.parse.name and ctx.request.postgres.parse.query respectively. Here is an example:

kind: Service
metadata:
  name: pg-db
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://address-to-pg
    postgres:
      user: postgres
      database: db01
      auth:
        password:
          fromSecret: pg-password
      authorization:
        mode: ALL
  authorization:
    inlinePolicies:
      - spec:
          rules:
            - effect: ALLOW
              condition:
                matchAny: true
            - effect: DENY
              condition:
                any:
                  of:
                    - match: 'ctx.request.postgres.query.query.matches("(?i)^\\s*(drop|truncate)\\s")'
                    - match: 'ctx.request.postgres.parse.query.matches("(?i)^\\s*(drop|truncate)\\s")'

A denied command is not proxied to the upstream and the downstream receives an error instead. Note that fast-path function calls are always denied in this mode.

Dynamic Configuration

You can use dynamic configuration (read more about dynamic configuration here) to, for example, route to different upstreams or different database users with different privileges and roles (e.g. more privileged Users can automatically delete tables or databases while other Users cannot do so) based on identity and/or context. Here is an example:

kind: Service
metadata:
  name: example-svc
spec:
  mode: POSTGRES
  dynamicConfig:
    configs:
    - name: prod
      upstream:
        url: postgres://address-to-pg
      postgres:
        user: prod
        database: prod-db
        auth:
          password:
            fromSecret: prod-password
    - name: dev
      upstream:
        url: postgres://address-to-pg
      postgres:
        user: dev
        database: dev-db
        auth:
          password:
            fromSecret: dev-password
    rules:
    - condition:
        match: '"prod" in ctx.user.spec.groups'
      configName: prod
    - condition:
        matchAny: true
      configName: dev

Visibility

The Service emits access logs in real time to the audit collector. Each log provides PostgreSQL application-layer aware information about the request such as the command type, the query details, etc.... Here is an example:

{
  "apiVersion": "core/v1",
  "entry": {
    "common": {
      "connectionID": "dnm3-09gn-6rt58k768x9cns8fxbu1w54v-v446-a6fy",
      "endedAt": "2025-03-21T22:15:32.000550409Z",
      "isAuthenticated": true,
      "isAuthorized": true,
      "namespaceRef": {
        "apiVersion": "core/v1",
        "kind": "Namespace",
        "name": "default",
        "resourceVersion": "0195b81d-43ec-7ba7-81f7-9b77202b3612",
        "uid": "bff0768e-eec8-4044-a972-9f7af81b79d8"
      },
      "reason": {
        "details": {
          "policyMatch": {
            "inlinePolicy": {
              "name": "allow-all",
              "resourceRef": {
                "apiVersion": "core/v1",
                "kind": "Group",
                "name": "g1",
                "resourceVersion": "0195b821-209b-76f7-bde9-b078e870621c",
                "uid": "972e1808-7248-468a-ba7c-8da43d33c626"
              }
            }
          }
        },
        "type": "POLICY_MATCH"
      },
      "regionRef": {
        "apiVersion": "core/v1",
        "kind": "Region",
        "name": "default",
        "uid": "6e233d12-2fd6-4670-a82d-8c712e1f7374"
      },
      "serviceRef": {
        "apiVersion": "core/v1",
        "kind": "Service",
        "name": "pg1.default",
        "resourceVersion": "0195bac5-10c2-7cb5-a59b-216d19ea7001",
        "uid": "d4293385-2aa7-4b42-a1a2-4a85803ee21b"
      },
      "sessionRef": {
        "apiVersion": "core/v1",
        "kind": "Session",
        "name": "usr1-3pt93j",
        "resourceVersion": "0195bac2-c5c8-7206-9076-191558cfc9be",
        "uid": "e8bea7be-b858-4457-a8ee-a0cb7f6c91da"
      },
      "startedAt": "2025-03-21T22:15:32.000470456Z",
      "userRef": {
        "apiVersion": "core/v1",
        "kind": "User",
        "name": "usr1",
        "resourceVersion": "0195b821-228d-79b2-92f3-4bb458944f97",
        "uid": "e0f251b2-6952-4a46-a771-2712a45c47f5"
      }
    },
    "info": {
      "postgres": {
        "query": {
          "query": "CREATE DATABASE db01;"
        },
        "type": "QUERY"
      }
    }
  },
  "kind": "AccessLog",
  "metadata": {
    "actorRef": {
      "apiVersion": "core/v1",
      "kind": "Session",
      "name": "usr1-3pt93j",
      "resourceVersion": "0195bac2-c5c8-7206-9076-191558cfc9be",
      "uid": "e8bea7be-b858-4457-a8ee-a0cb7f6c91da"
    },
    "createdAt": "2025-03-21T22:15:32.000522377Z",
    "id": "qz07-s8ho-pf8t84xm5rhqrmnpttybpspx-aqnp-rkeg",
    "targetRef": {
      "apiVersion": "core/v1",
      "kind": "Service",
      "name": "pg1.default",
      "resourceVersion": "0195bac5-10c2-7cb5-a59b-216d19ea7001",
      "uid": "d4293385-2aa7-4b42-a1a2-4a85803ee21b"
    }
  }
}

As you can see in the above example, the type of this POSTGRES access Log is a QUERY. The POSTGRES mode has currently 8 Log types: SESSION_START, SESSION_END, QUERY, PARSE, CLOSE, EXECUTE, BIND, FUNCTION_CALL and some of these types include different detailed information according to their type. You can read more in the API reference.

You can also disable recording the statement text of the query and parse messages in the access logs via the visibility.disableQuery field. The access logs themselves are still recorded without the statement text. This is recommended for databases whose statements routinely embed credentials or other sensitive values. Here is an example:

kind: Service
metadata:
  name: pg-db
spec:
  mode: POSTGRES
  port: 5432
  config:
    upstream:
      url: postgres://address-to-pg
    postgres:
      user: postgres
      database: db01
      auth:
        password:
          fromSecret: pg-password
      visibility:
        disableQuery: true