> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oleria.com/llms.txt
> Use this file to discover all available pages before exploring further.
# PostgreSQL
Oleria provides identity security and access management teams with visibility and intelligence into who has access to what, where they got that access, how they use it, and whether they should even have it. As part of that promise, we integrate your PostgreSQL databases into the Oleria platform, giving you visibility into every role, grant, and access path across self-hosted or cloud-managed Postgres. This document provides step-by-step guidance for integrating PostgreSQL with your Oleria workspace.
This integration supports self-hosted PostgreSQL, Google Cloud SQL, AWS RDS/Aurora, and Azure Database for PostgreSQL Flexible Server. Pick the setup path that matches your deployment below.
## What Oleria discovers
| Area | Detail |
| :------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Accounts** | PostgreSQL login roles (`rolcanlogin = true`), including Google Cloud SQL's IAM-authenticated roles. PostgreSQL roles have no email field - Oleria synthesizes one from the role name and connection host for identity matching. |
| **Roles and groups** | Every PostgreSQL role, login and non-login. A non-login role whose only members are login roles is modeled as a group; other non-login roles are modeled as roles, along with direct role-to-role membership. |
| **Resources** | Databases, schemas, tables, sequences, foreign servers, and functions (including `SECURITY DEFINER` functions), plus the containment hierarchy from database down to table. |
| **Access grants** | Direct object grants, database-level `CONNECT`/`TEMP`/`CREATE` grants, and default privilege grants (`ALTER DEFAULT PRIVILEGES`) that apply to future objects. |
| **Impersonation** | `SET ROLE` assumption paths, a superuser's implicit ability to assume any role, and `SECURITY DEFINER` function execution paths. |
| **Risk signals** | Superuser accounts, row-level security bypass (`bypassrls`), replication privilege, expired credentials (`rolvaliduntil` in the past), role grants with `ADMIN OPTION`, and `CREATEROLE` privilege. |
| **Activity** | Parsed pgaudit log entries - DDL changes, role grants and revokes, and connect/disconnect events. Requires pgaudit and log access configured for your deployment - see setup below. |
## Prerequisites
* A PostgreSQL role with **CONNECT** privilege on every database you want Oleria to see. Discovery doesn't require superuser.
* Cloud-provider credentials matching your deployment - see [Set up the integration](#set-up-the-integration) below.
* Optional: grant that same role **CREATEROLE** (or superuser), if you also want to run remediation actions.
PostgreSQL superusers bypass every object-level grant. Rather than showing a misleadingly empty access list for a superuser account, Oleria models that bypass explicitly as access to every database.
## Set up the integration
Connect as an admin and create a role for Oleria:
```sql theme={null}
CREATE ROLE oleria_connector WITH LOGIN PASSWORD 'your-password';
GRANT CONNECT ON DATABASE your_database TO oleria_connector;
```
Repeat the `GRANT CONNECT` for every database you want Oleria to see.
To also run remediation actions, grant **CREATEROLE** instead of a plain role: `ALTER ROLE oleria_connector WITH CREATEROLE;`
Oleria reads activity directly from your PostgreSQL log file over the same connection, using `pg_read_file()` - no separate log shipping is needed.
This approach requires PostgreSQL to write logs to a file at a known path. It does **not** work with the official Postgres Docker image (which logs to container stdout) or deployments that route logs to syslog or systemd journal only. For those setups, skip this step - Oleria will connect successfully but won't show activity.
1. Install the `pgaudit` extension and enable it in `postgresql.conf`:
```
shared_preload_libraries = 'pgaudit'
logging_collector = on
log_directory = 'log'
log_destination = 'stderr'
log_line_prefix = '%m [%p] user=%u '
log_connections = on
log_disconnections = on
pgaudit.log = 'ddl, role'
```
`log_line_prefix` must include both `%p` (the backend process id) and `%u` (the session user). `%p` lets Oleria correlate a pgaudit entry with the connection it came from to fill in the client IP and database; `%u` attributes the entry to the right actor. Dropping either still captures entries, but with a blank actor (`%u`) or blank IP/database (`%p`).
`pgaudit` has no dedicated class for connect/disconnect events - those come from PostgreSQL's own `log_connections`/`log_disconnections` output, which default to off. Without them, DDL and role changes are still captured, but no connect/disconnect activity will show up.
2. Restart PostgreSQL, then run `CREATE EXTENSION pgaudit;` in each database.
3. Reading the log file requires two separate grants, not just one - a role with only `pg_read_server_files` will still fail:
```sql theme={null}
GRANT pg_read_server_files TO oleria_connector;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_logfile() TO oleria_connector;
```
`pg_read_server_files` covers reading the log file itself, but Oleria also calls `pg_current_logfile()` to locate it, and PostgreSQL restricts that function to superuser by default (and, from PostgreSQL 17 only, the `pg_monitor` role). The explicit `GRANT EXECUTE` above works on every supported version. Skipping this step doesn't break setup; Oleria simply won't show activity.
Create a PostgreSQL login role in Cloud SQL and grant it **CONNECT** on every database you want Oleria to see. Oleria's role and grant discovery queries are all against `pg_catalog`, which is readable by any connected user - no elevated database privilege is needed for discovery.
To also run remediation actions (assign or remove role membership), grant this user **CREATEROLE** as well.
On the instance's **Overview** page, copy the **Connection name** - it's in `project:region:instance` format. Oleria uses this to route through the Cloud SQL connector rather than a raw IP address, so no IP allowlisting is required.
1. In **IAM & Admin**, create a service account.
2. Grant it **Cloud SQL Client** (`roles/cloudsql.client`) at the project level - the Cloud SQL Connector uses this to authorize the connection at the IAM level before the PostgreSQL handshake.
3. Grant it **Private Logs Viewer** (`roles/logging.privateLogViewer`) at the project level, not the more common Logs Viewer role. pgAudit entries on Cloud SQL are delivered as Data Access audit logs, which only Private Logs Viewer can read.
4. Create a JSON key for the service account and download it - you'll paste its contents into Oleria's **GCP Service Account Key** field.
Without this key, Oleria falls back to its own Application Default Credentials, which only reach your project if you've separately granted Oleria's runtime identity access - not the typical setup. Provide the key directly unless Oleria Support has told you otherwise.
Without this step, no pgAudit entries ever reach Cloud Logging, regardless of how the service account is configured.
1. Set the `cloudsql.enable_pgaudit` database flag to **On** on the instance (**Edit** -> **Flags**). This restarts the instance.
2. Connect to each database and run `CREATE EXTENSION pgaudit;`.
3. Set the `pgaudit.log` database flag to the statement classes you want audited (for example `ddl, role`).
4. Also set the `log_connections` and `log_disconnections` database flags to **On**. pgAudit has no class of its own for connect/disconnect events - those come from PostgreSQL's own connection logging, which Cloud SQL leaves off by default. Skipping this step still delivers DDL and role changes; you'll just see no connect/disconnect activity.
5. In **IAM & Admin** -> **Audit Logs**, find **Cloud SQL API**, and enable the **Data Read** and **Data Write** log types. Data Access audit logs are off by default - this is what actually routes pgAudit entries into Cloud Logging.
On the RDS or Aurora instance, enable **IAM database authentication** (**Modify** -> **Additional configuration**).
Connect to the database and create or update a user to authenticate via IAM instead of a password:
```sql theme={null}
CREATE USER oleria_connector;
GRANT rds_iam TO oleria_connector;
```
Grant **CONNECT** on every database you want Oleria to see, the same as the self-hosted flow.
Oleria connects using short-lived IAM tokens, not a static password, and needs `rds-db:connect` permission scoped to this database user. Contact Oleria Support for the exact IAM identity to grant this to - the specifics depend on your deployment model.
Oleria reads activity from the PostgreSQL log RDS exports to CloudWatch Logs - there's no separate log-shipping agent to install.
Skipping this step means Oleria won't show activity for this connection, the same as the other three deployment paths - it doesn't fail the sync.
1. In the parameter group attached to your instance, add `pgaudit` to `shared_preload_libraries`. This is a static parameter - RDS requires a reboot for it to take effect.
2. In the same parameter group, set `pgaudit.log` to the statement classes you want audited (for example `ddl, role`), and set `log_connections` and `log_disconnections` to `1`. pgAudit has no class of its own for connect/disconnect events - those come from PostgreSQL's own connection logging, which RDS leaves off by default.
3. Connect to each database and run `CREATE EXTENSION pgaudit;`.
4. On the instance, go to **Modify** -> **Log exports**, and enable **PostgreSQL log**. This ships `postgresql.log` to a CloudWatch Logs log group. For a plain RDS instance, this group is named `/aws/rds/instance//postgresql`, which Oleria derives automatically. **For an Aurora cluster**, the log group is named `/aws/rds/cluster//postgresql` instead - enter this in the **CloudWatch Log Group** connection field below, since Oleria can't derive the cluster form automatically.
5. Grant Oleria's connecting identity (the same one from the previous step) `logs:FilterLogEvents` on that log group, in addition to the `rds-db:connect` permission already granted.
The **RDS Instance Identifier** field is required in practice, even though Oleria's connection form doesn't mark it as such. Without it, Oleria can't resolve the instance's hostname, and - for a plain RDS instance - can't locate the activity log either.
On the Flexible Server, enable **Microsoft Entra authentication** and set a Microsoft Entra admin.
1. In the Azure Portal, register an application in **Microsoft Entra ID** and create a client secret for it (**Certificates & secrets** -> **New client secret**).
2. As the Microsoft Entra admin, create a PostgreSQL role for the application and grant it **CONNECT** on every database you want Oleria to see.
1. Under **Settings** -> **Server parameters**, allowlist and load `pgaudit` (`azure.extensions` and `shared_preload_libraries`), then set `pgaudit.log` to the statement classes you want audited (for example `ddl, role`). Azure requires each class spelled out individually - it doesn't support the `-` shortcut from pgaudit's own docs.
2. In the same **Server parameters** page, set `log_connections` and `log_disconnections` to **ON**. pgAudit has no class of its own for connect/disconnect events - those come from PostgreSQL's own connection logging, which Azure leaves off by default.
3. Connect to each database and run `CREATE EXTENSION pgaudit;`.
1. On the Flexible Server, go to **Monitoring** -> **Diagnostic settings** and add a diagnostic setting.
2. Select the **PostgreSQLLogs** category, choose **Send to Log Analytics workspace**, and pick your workspace.
Azure diagnostic settings offer two destination table options: **Resource specific** and **Azure diagnostics**. You must select **Resource specific** - Oleria queries the per-resource-type `PGSQLServerLogs` table, not the legacy shared `AzureDiagnostics` table the other option writes to. Picking **Azure diagnostics** results in a connection that syncs normally but shows no activity, with nothing indicating why.
Collect the Flexible Server name, your Microsoft Entra tenant ID, the app's client ID and secret, and the Log Analytics Workspace ID from the diagnostic setting above.
## Connect PostgreSQL to Oleria
Log in to your Oleria workspace and navigate to **Integrations** -> **PostgreSQL** -> **Connect**.
Select your **Cloud Provider** first - the form updates to show only the fields that provider needs.
| Field | Notes |
| :----------------- | :------------------------------------------------------ |
| **Cloud Provider** | Select **None (self-hosted)**. |
| **Host** | Required. The hostname or IP of your PostgreSQL server. |
| **Port** | Defaults to `5432`. |
| **Database** | Defaults to `postgres`. |
| **User** | Required. The role you created above. |
| **Password** | Required. |
Oleria auto-detects your pgaudit log file's path via `pg_current_logfile()` - there is no field to override it today. If your log file lives somewhere Postgres doesn't report (an unusual logging setup), contact Oleria Support.
| Field | Notes |
| :--------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Cloud Provider** | Select **Google Cloud SQL**. |
| **Database** | Defaults to `postgres`. |
| **User** | Required. The role you created above. |
| **Password** | Required. |
| **Cloud SQL Connection Name** | Required. Found on the instance's Overview page in `project:region:instance` format. |
| **GCP Service Account Key (JSON)** | Required in practice. Used for both the Cloud SQL database connection and Cloud Logging access. Without it, both fall back to Oleria's own Application Default Credentials - only skip if Oleria Support has told you to. |
| Field | Notes |
| :-------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Cloud Provider** | Select **AWS RDS / Aurora**. |
| **Database** | Defaults to `postgres`. |
| **User** | Required. The role you created above. |
| **AWS Region** | Required. The region your RDS instance is in (e.g. `us-east-1`). |
| **RDS Instance Identifier** | Required in practice - see the warning above. |
| **CloudWatch Log Group** | Optional, Aurora only. Overrides the CloudWatch Logs log group used for activity. Aurora exports logs under a cluster-form path Oleria can't derive automatically - see the pgaudit/CloudWatch Logs export step above. Leave blank for a plain RDS instance. |
| Field | Notes |
| :----------------------------- | :------------------------------------------------------------------------------------ |
| **Cloud Provider** | Select **Azure Flexible Server**. |
| **Database** | Defaults to `postgres`. |
| **User** | Required. The role you created above. |
| **Server Name** | Required. The Flexible Server hostname (e.g. `myserver.postgres.database.azure.com`). |
| **Azure Tenant ID** | Required. |
| **Azure Client ID** | Required. |
| **Azure Client Secret** | Required. |
| **Log Analytics Workspace ID** | Required. |
Oleria validates the connection, discovers your PostgreSQL roles, resources, and access, and begins the first sync.
## Verify the integration
Confirm PostgreSQL appears in your Oleria workspace's connected integrations. After the first sync completes, you can review the discovered roles, resources, access grants, and activity in your Oleria workspace.
## Remediation actions
Both remediation actions need the connector role to hold **CREATEROLE** or superuser, and both require approval before Oleria runs them.
| Action | Notes |
| :-------------- | :------------------------------------------------------------------------------------------ |
| **Assign role** | Grants a PostgreSQL role to another role (`GRANT role TO grantee`). Fully reversible. |
| **Remove role** | Revokes a PostgreSQL role from another role (`REVOKE role FROM grantee`). Fully reversible. |
## Known limitations
* **No account lifecycle:** Oleria can grant or revoke role membership, but can't create, disable, or delete a PostgreSQL role. This integration remediates access, not identity lifecycle.
* **Synthetic email:** PostgreSQL roles have no native email field. Oleria synthesizes one from the role name and connection host, which may not match a real address used elsewhere in your organization.
* **Auth method visibility:** Oleria can show a role's configured authentication method only when the connector role is a true superuser able to call `pg_hba_file_rules()`. This is unavailable on Google Cloud SQL, which restricts that function even for superuser-equivalent roles - Oleria falls back gracefully with no error.
* **Column-level grants:** only columns with an explicit grant are inventoried; columns covered solely by a table-wide grant aren't listed individually.
## Contact us
For questions about this integration, contact us at [support@oleria.com](mailto:support@oleria.com).