PostgreSQL

Connect PostgreSQL to Oleria to see every role, grant, and access path across your self-hosted or cloud-managed Postgres databases. Oleria also flags risky privileges, such as superuser accounts and row-level security bypass.

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 access matching your deployment - see 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

Self-hosted
Google Cloud SQL
AWS RDS / Aurora
Azure Flexible Server

Create a connector role

Connect as an admin and create a role for Oleria:

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;

Enable pgaudit for activity (optional)

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.

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.

Restart PostgreSQL, then run CREATE EXTENSION pgaudit; in each database.
Reading the log file requires two separate grants, not just one - a role with only pg_read_server_files will still fail:

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 connector database user

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.

Note your Cloud SQL connection name

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.

Create a service account for connector and log access

In IAM & Admin, create a service account.
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.
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.
Create a JSON key for the service account and download it - you’ll paste its contents into Oleria’s GCP Service Account Key field.

The connection form requires this key, because Oleria has no default credentials to use for your project.

Enable pgAudit and Data Access audit logging

Without this step, no pgAudit entries ever reach Cloud Logging, regardless of how the service account is configured.

Set the cloudsql.enable_pgaudit database flag to On on the instance (Edit -> Flags). This restarts the instance.
Connect to each database and run CREATE EXTENSION pgaudit;.
Set the pgaudit.log database flag to the statement classes you want audited (for example ddl, role).
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.
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.

Create a connector database user

Connect to the database and create a login role for Oleria, then grant it CONNECT on every database you want Oleria to see, the same as the self-hosted flow:

CREATE ROLE oleria_connector WITH LOGIN PASSWORD 'your-password';
GRANT CONNECT ON DATABASE your_database TO oleria_connector;

Optional: use AWS IAM database authentication

By default Oleria signs in with the password above. If you prefer short-lived IAM tokens instead of a stored password, enable IAM database authentication on the RDS or Aurora instance (Modify -> Additional configuration), then run:

GRANT rds_iam TO oleria_connector;

You then select Use AWS IAM database authentication (instead of a password) in the connection form and don’t enter a password.

Create the Oleria access role with CloudFormation

You don’t grant Oleria access to your AWS account by hand. When you select Connect in Oleria, Oleria opens an AWS CloudFormation quick-create link in a new tab. Launch the stack in your AWS account. It creates a read-only IAM role that only Oleria can assume, and it gives the role permission to read the PostgreSQL log group, describe your RDS instances and clusters, and, if you use IAM database authentication, connect with rds-db:connect. When the stack finishes, copy the RoleArn from its Outputs tab. See Connect PostgreSQL to Oleria.

Enable pgaudit and CloudWatch Logs export

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.

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.
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.
Connect to each database and run CREATE EXTENSION pgaudit;.
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/<your-instance-identifier>/postgresql, which Oleria derives automatically. For an Aurora cluster, the log group is named /aws/rds/cluster/<your-cluster-identifier>/postgresql instead - enter this in the CloudWatch Log Group connection field, since Oleria can’t derive the cluster form automatically.
No extra IAM grant is needed for the log group. The role that the CloudFormation stack creates can already read RDS log groups.

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. For Aurora, enter a DB instance identifier (for example, the writer), not the cluster identifier.

Enable Microsoft Entra authentication

On the Flexible Server, enable Microsoft Entra authentication and set a Microsoft Entra admin.

In the Azure Portal, register an application in Microsoft Entra ID and create a client secret for it (Certificates & secrets -> New client secret).
As the Microsoft Entra admin, create a PostgreSQL role for the application and grant it CONNECT on every database you want Oleria to see.

Enable pgaudit (optional, for activity)

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.
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.
Connect to each database and run CREATE EXTENSION pgaudit;.

Route logs to Log Analytics

On the Flexible Server, go to Monitoring -> Diagnostic settings and add a diagnostic setting.
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.

Note your server, tenant, and Log Analytics details

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

Open the integration

Complete the connection form

Select your Cloud Provider first - the form updates to show only the fields that provider needs.

Self-hosted
Google Cloud SQL
AWS RDS / Aurora
Azure Flexible Server

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. Paste the entire downloaded JSON key file. Used for both the Cloud SQL database connection and Cloud Logging access. The service account needs the Cloud SQL Client role.

Field	Notes
Cloud Provider	Select AWS RDS / Aurora.
Database	Defaults to `postgres`.
User	Required. The role you created above.
Password	Required, unless you select Use AWS IAM database authentication (instead of a password). The field is hidden when you do.
AWS Region	Required. The region your RDS instance is in (for example, `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.
Use AWS IAM database authentication (instead of a password)	Optional checkbox. Select it only if you completed the IAM database authentication step above.

Select Connect. Oleria opens a CloudFormation link in a new tab. Launch the stack in your AWS account, then return to Oleria and provide:

Field	Notes
Role ARN	Required. The RoleArn output of the CloudFormation stack.
Confirm that the role is created using CloudFormation successfully	Required checkbox. Select it once the stack is created.

Select Authenticate. Don’t go back through the first step after you create the stack, because each pass generates a new external ID that an existing stack rejects.

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 (for example, `myserver.postgres.database.azure.com`).
Azure Tenant ID	Required.
Azure Client ID	Required.
Azure Client Secret	Required.
Log Analytics Workspace ID	Required.

Complete the connection

Select Connect (Authenticate on the AWS Role ARN step). Oleria validates the connection, discovers your PostgreSQL roles, resources, and access, and begins the first sync.

Verify the integration

Confirm PostgreSQL appears on the Connected tab of your Oleria Integrations page. 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.

Overview

Generic connectors

Identity providers

HR systems

Cloud infrastructure

Applications

Ticketing

Messaging

What Oleria discovers

Prerequisites

Set up the integration

Connect PostgreSQL to Oleria

Verify the integration

Remediation actions

Known limitations

Contact us

​What Oleria discovers

​Prerequisites

​Set up the integration

​Connect PostgreSQL to Oleria

​Verify the integration

​Remediation actions

​Known limitations

​Contact us

What Oleria discovers

Prerequisites

Set up the integration

Connect PostgreSQL to Oleria

Verify the integration

Remediation actions

Known limitations

Contact us