PostgreSQL - Octelium Docs

Setting the Service mode to POSTGRES enables it to operate in application-layer aware PostgreSQL mode. This mode enables you to provide secret-less 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.

Secret-less Access

You can simply provide your database configuration (i.e. user, database and password) to provide secret-less access for your Users as follows:

1kind: Service
2metadata:
3  name: pg-db-01
4spec:
5  # Set the mode to POSTGRES
6  mode: POSTGRES
7  port: 1234
8  config:
9    upstream:
10      url: postgres://address-to-pg
11    postgres:
12      # RECOMMENDED. Override the DB user. If not set, then the value from the downstream is used.
13      user: postgres
14      # RECOMMENDED. Override the DB name. If not set, then the value from the downstream is used.
15      database: db01
16      # RECOMMENDED. Set the password of the user.
17      auth:
18        password:
19          fromSecret: <SECRET_NAME>
20      # Optionally use REQUIRE if the database is behind TLS
21      sslMode: REQUIRE

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:

1kind: Service
2metadata:
3  name: svc1
4spec:
5  mode: POSTGRES
6  config:
7   # Your config....
8  authorization:
9    inlinePolicies:
10      - spec:
11          rules:
12            - effect: ALLOW
13              condition:
14                any:
15                  of:
16                    - match: ctx.request.postgres.connect.user == "db-user-1"
17                    - match: ctx.request.postgres.connect.database == "db-01"

NOTE

You do not actually 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

Dynamic Configuration

You can use dynamic configuration in order to, for example, route to different upstreams and/or setting different users, databases and passwords depending on the request's context (read more about dynamic configuration here). Here is an example:

1kind: Service
2metadata:
3  name: example-svc
4spec:
5  mode: POSTGRES
6  dynamicConfig:
7    configs:
8    - name: prod
9      upstream:
10        url: postgres://address-to-pg
11      postgres:
12        user: prod
13        database: prod-db
14        auth:
15          password:
16            fromSecret: prod-password
17    - name: dev
18      upstream:
19        url: postgres://address-to-pg
20      postgres:
21        user: dev
22        database: dev-db
23        auth:
24          password:
25            fromSecret: dev-password
26    rules:
27    - condition:
28        match: '"prod" in ctx.user.spec.groups'
29      configName: prod
30    - condition:
31        matchAny: true
32      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:

1{
2  "apiVersion": "core/v1",
3  "entry": {
4    "common": {
5      "connectionID": "dnm3-09gn-6rt58k768x9cns8fxbu1w54v-v446-a6fy",
6      "endedAt": "2025-03-21T22:15:32.000550409Z",
7      "isAuthenticated": true,
8      "isAuthorized": true,
9      "namespaceRef": {
10        "apiVersion": "core/v1",
11        "kind": "Namespace",
12        "name": "default",
13        "resourceVersion": "0195b81d-43ec-7ba7-81f7-9b77202b3612",
14        "uid": "bff0768e-eec8-4044-a972-9f7af81b79d8"
15      },
16      "reason": {
17        "details": {
18          "policyMatch": {
19            "inlinePolicy": {
20              "name": "allow-all",
21              "resourceRef": {
22                "apiVersion": "core/v1",
23                "kind": "Group",
24                "name": "g1",
25                "resourceVersion": "0195b821-209b-76f7-bde9-b078e870621c",
26                "uid": "972e1808-7248-468a-ba7c-8da43d33c626"
27              }
28            }
29          }
30        },
31        "type": "POLICY_MATCH"
32      },
33      "regionRef": {
34        "apiVersion": "core/v1",
35        "kind": "Region",
36        "name": "default",
37        "uid": "6e233d12-2fd6-4670-a82d-8c712e1f7374"
38      },
39      "serviceRef": {
40        "apiVersion": "core/v1",
41        "kind": "Service",
42        "name": "pg1.default",
43        "resourceVersion": "0195bac5-10c2-7cb5-a59b-216d19ea7001",
44        "uid": "d4293385-2aa7-4b42-a1a2-4a85803ee21b"
45      },
46      "sessionRef": {
47        "apiVersion": "core/v1",
48        "kind": "Session",
49        "name": "usr1-3pt93j",
50        "resourceVersion": "0195bac2-c5c8-7206-9076-191558cfc9be",
51        "uid": "e8bea7be-b858-4457-a8ee-a0cb7f6c91da"
52      },
53      "startedAt": "2025-03-21T22:15:32.000470456Z",
54      "userRef": {
55        "apiVersion": "core/v1",
56        "kind": "User",
57        "name": "usr1",
58        "resourceVersion": "0195b821-228d-79b2-92f3-4bb458944f97",
59        "uid": "e0f251b2-6952-4a46-a771-2712a45c47f5"
60      }
61    },
62    "info": {
63      "postgres": {
64        "query": {
65          "query": "CREATE DATABASE db01;"
66        },
67        "type": "QUERY"
68      }
69    }
70  },
71  "kind": "AccessLog",
72  "metadata": {
73    "actorRef": {
74      "apiVersion": "core/v1",
75      "kind": "Session",
76      "name": "usr1-3pt93j",
77      "resourceVersion": "0195bac2-c5c8-7206-9076-191558cfc9be",
78      "uid": "e8bea7be-b858-4457-a8ee-a0cb7f6c91da"
79    },
80    "createdAt": "2025-03-21T22:15:32.000522377Z",
81    "id": "qz07-s8ho-pf8t84xm5rhqrmnpttybpspx-aqnp-rkeg",
82    "targetRef": {
83      "apiVersion": "core/v1",
84      "kind": "Service",
85      "name": "pg1.default",
86      "resourceVersion": "0195bac5-10c2-7cb5-a59b-216d19ea7001",
87      "uid": "d4293385-2aa7-4b42-a1a2-4a85803ee21b"
88    }
89  }
90}

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.