> For the complete documentation index, see [llms.txt](https://docs.openiam.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.openiam.com/connector-configuration/postgresql.md).

# PostgreSQL connector

This page describes how to use OpenIAM PostgreSQL Connector

The PostgreSQL connector synchronizes **users, groups, and group memberships** from a PostgreSQL database to OpenIAM, and manages standard PostgreSQL role privileges.

| Operation                                                                                                     | Supported |
| ------------------------------------------------------------------------------------------------------------- | --------- |
| Synchronize users, groups, and memberships                                                                    | ✅         |
| Add / revoke PostgreSQL privileges (`CREATEDB`, `INHERIT`, `LOGIN`, `REPLICATION`, `CREATEROLE`, `SUPERUSER`) | ✅         |
| Test connection                                                                                               | ✅         |

{% hint style="info" %}
This connector manages **PostgreSQL database roles/accounts**. To provision rows into an application's own tables in a PostgreSQL database, use the [JDBC connector](/connector-configuration/jdbc.md) instead.
{% endhint %}

## Use cases

* **Importing PostgreSQL database roles** (users, groups, memberships) into OpenIAM for visibility and access review.
* Managing **PostgreSQL privileges** through OpenIAM roles.

## Versions and compatibility

The end application is a **PostgreSQL database**, reached over JDBC. The connector manages database roles and their privileges, so it has no API version of its own — what it supports is what the server and the JDBC driver support.

<table><thead><tr><th width="188">Property</th><th>Value</th></tr></thead><tbody><tr><td><strong>Platform</strong></td><td>PostgreSQL</td></tr><tr><td><strong>Interface</strong></td><td>JDBC</td></tr><tr><td><strong>Managed objects</strong></td><td>Database roles, and the privileges granted to them</td></tr><tr><td><strong>Connection</strong></td><td>Connection string and driver, set on the managed system</td></tr></tbody></table>

## Prerequisites

* A PostgreSQL **admin** account for the connector.
* Network access from the connector host to the database host/port.

## Installing the connector

### Kubernetes

Enable the connector in your deployment configuration; no separate installation is required.

### RPM

Use the connector service unit (`openiam-connector@postgresql-connector-rabbitmq`) or run the connector JAR — see [Connectors via RPM](/installation/rpm/rpm-single-node/add-a-connector.md).

## Starting, stopping, and restarting

<table><thead><tr><th width="175">Deployment</th><th>Commands</th></tr></thead><tbody><tr><td>RPM (systemd)</td><td><code>systemctl start|stop|restart openiam-connector@postgresql-connector-rabbitmq</code></td></tr><tr><td>Kubernetes</td><td><code>kubectl rollout restart</code> the connector deployment.</td></tr></tbody></table>

## Command-line parameters and properties

The connector is a Spring Boot application. Its defaults ship inside the jar, and you override them in either of two places:

* **An external property file**, read at `${confpath}/conf/properties/postgresql-connector.properties`. The file is optional, and `confpath` is itself a system property, set with `-Dconfpath=/data/openiam/`.
* **Directly on the command line**, as `-D` arguments, which win over the file.

{% code overflow="wrap" %}

```bash
java -Dconfpath=/data/openiam/ -Dlogging.level.root=INFO -Dorg.openiam.connector.queue=PostgreSQL_1_Request -jar postgresql-connector-rabbitmq.jar
```

{% endcode %}

| Property                                  | Default                 | Purpose                                                                                                                                                                                               |
| ----------------------------------------- | ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `org.openiam.connector.queue`             | `PostgreSQL_1_Request`  | ⚠️ The default queue name **lacks the usual `_Connector_` infix** (it is `PostgreSQL_1_Request`, not `POSTGRESQL_Connector_1_Request`). The connector record in OpenIAM must match this exact string. |
| `org.openiam.connector.queueResponseName` | `PostgreSQL_1_Response` |                                                                                                                                                                                                       |

{% hint style="warning" %}
**Two names on this connector do not follow the pattern the others use.** Its external property file is `postgresql-connector.properties`, without the `-rabbitmq` that every other connector has, and its default queue is `PostgreSQL_1_Request`, without the `_Connector` segment. Overrides written to `postgresql-connector-rabbitmq.properties` are read by nothing. Use the names above exactly as written.
{% endhint %}

The connection details themselves are **not** set here — they are managed system parameters, covered under [Configuring the managed system](#configuring-the-managed-system).

## Verifying the connector is running

{% stepper %}
{% step %}
In the Webconsole, open the managed system — the **Running connector status** indicator must be green.
{% endstep %}

{% step %}
Click **Test Connection** — it must return success.
{% endstep %}

{% step %}
In the RabbitMQ management console, confirm the `PostgreSQL_1_Request` queue exists and has a consumer.
{% endstep %}
{% endstepper %}

## Troubleshooting

| Symptom                           | Likely cause                                                                 | Resolution                                                                                       |
| --------------------------------- | ---------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ |
| Connector never receives messages | Queue-name mismatch — the **non-standard default name** is the usual culprit | The connector record's queue must equal `PostgreSQL_1_Request` exactly (no `_Connector_` infix). |
| Test connection fails             | Wrong connection string or credentials                                       | Verify the `jdbc:postgresql://host:port/db` format and the admin account.                        |
| Privilege changes don't apply     | Custom field missing or misnamed                                             | The custom field must be called "PostgreSQL privilege" and set on the role.                      |
| Search returns nothing            | Search filter                                                                | Use `rolname=?` as the user search filter.                                                       |

See also: [general connector troubleshooting](/connector-configuration/4-troubleshootingconnector.md) and the [connector error-code reference](/connector-configuration/4-troubleshootingconnector.md#connector-error-codes).

## Registering the connector in OpenIAM

For first usage, configure the connector:

* Go to Webconsole → **Provisioning → Connectors → PostgreSQL Connector**.
* Check the rows **"Connection string"** and **"Search Filter for User"**.
* Press **Save**.

## Configuring the managed system

Go to Webconsole → **Provisioning → Managed System → PostgreSQL Managed system** and configure the fields for your environment:

<table><thead><tr><th width="211">Field</th><th>Value</th></tr></thead><tbody><tr><td>Host</td><td>Database host</td></tr><tr><td>Port</td><td>Database port</td></tr><tr><td>Connection string</td><td><code>"jdbc:postgresql://database_host:database_port/database_name"</code></td></tr><tr><td>LoginId / Password</td><td>Credentials for an <strong>admin</strong> user of the database</td></tr><tr><td>Search filter for user</td><td><code>rolname=?</code></td></tr></tbody></table>

## Attribute mapping and policy map

To manage **PostgreSQL privileges** (`CREATEDB`, `INHERIT`, `LOGIN`, `REPLICATION`, `CREATEROLE`, `SUPERUSER`):

{% stepper %}
{% step %}
Create a custom field called **PostgreSQL privilege** — Webconsole → **Administration → Custom Fields → Create New Custom Field**. Give it the type **Text Field** and mark it public.

<figure><img src="https://2965242200-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F0uHWHRgcad6P03ehqgmX%2Fuploads%2Fgit-blob-7817d7d9e2d6b54004ba486434ef0be9d829f505%2Fprivilege.png?alt=media" alt="The Create New Custom Field form, with the name and the English display name both set to PostgreSQL privilege, Is Public switched on, the type set to Text Field, and the other thirteen display-name languages left empty"><figcaption></figcaption></figure>
{% endstep %}

{% step %}
Give the field a **Metadata Type** from the `ROLE_TYPE` grouping, and add it to a **Role Page Template**. A custom field that is created but never added to a template renders nowhere, so without this the Role screen has no **PostgreSQL privilege** field to fill in. See [Page templates and custom fields](/administration-guide/identities/page-templates-and-custom-fields.md).
{% endstep %}

{% step %}
Create a role for the PostgreSQL managed system and put the privilege you want in that field.

<figure><img src="https://2965242200-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F0uHWHRgcad6P03ehqgmX%2Fuploads%2Fgit-blob-b485c24bb6cfbb8eeb7e7d99dbd1ee77a8559dee%2Fprivilege_role.png?alt=media" alt="An access role named Role for create db, bound to the PostgreSQL managed system, with the PostgreSQL privilege field set to CREATEDB"><figcaption></figcaption></figure>
{% endstep %}
{% endstepper %}

The connector reads these from a policy-map attribute named `privileges`, and derives the revoke form by prefixing `NO` — so removing `SUPERUSER` is sent as `NOSUPERUSER`. Map the custom field to `privileges` on the managed system's policy map, or the role is granted in OpenIAM and nothing reaches the database.

## Reconciliation and synchronization

Configure a sync against the managed system as described in [Configuring synchronization](/administration-guide/lifecycle/synchronize-and-import-data.md) — the connector supplies users, groups, and memberships as source records (search filter `rolname=?`).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.openiam.com/connector-configuration/postgresql.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.