# PostgreSQL > Octelium documentation. Canonical page: . 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](https://octelium.com/docs/octelium/latest/user/cli/access.md#postgresql). You can also read more detailed guides [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/postgresql-sso-zero-trust.md) for generic databases behind NAT, [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/cockroachdb.md) for CockroachDB and [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/neon.md) 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](https://octelium.com/docs/octelium/latest/management/core/secret.md#creating-a-secret)) as follows: ```bash octeliumctl create secret pg-password # OR via a --value flag octeliumctl create secret --value pg-password # OR via a --file flag octeliumctl create secret --file /PATH/TO/PASSWORD pg-password ``` Now, you define your *Service* as follows: ```yaml kind: Service metadata: name: pg-db spec: mode: POSTGRES port: 5432 config: upstream: url: postgres://address-to-pg # !mark(1:6) 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](https://octelium.com/docs/octelium/latest/management/core/service/overview.md#remotely-via-a-connected-user). ## SSL Mode By default, the *Service* attempts to use `prefer` ssl mode (read more [here](https://www.postgresql.org/docs/current/libpq-ssl.html)). 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: ```yaml 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 # !mark 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](https://octelium.com/docs/octelium/latest/management/core/service/overview.md#upstreams-over-tls)). Here is an example: ```yaml 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 # !mark sslMode: VERIFY_FULL # !mark(1:5) 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: ```yaml kind: Service metadata: name: svc1 spec: mode: POSTGRES config: # Your config.... authorization: inlinePolicies: - spec: rules: - effect: ALLOW condition: any: of: # !mark(1:2) - 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](https://octelium.com/docs/octelium/latest/management/core/service/dynamic-config.md) 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: ```yaml 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 # !mark(1:2) 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](https://octelium.com/docs/octelium/latest/management/core/service/dynamic-config.md)) 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: ```yaml 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: ```json { "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: ```yaml 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 # !mark(1:2) visibility: disableQuery: true ```