> For the complete documentation index, see [llms.txt](https://docs.appstrategy.com/apprules-r-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/postgresql.md).

# PostgreSQL

Connects to PostgreSQL databases, including managed services such as Azure Database for PostgreSQL, Amazon RDS / Aurora and Google Cloud SQL. SQL is sent to the server as written, so the full PostgreSQL dialect is available.

#### Connection Properties

**Connection**

| Property          | Default | Notes                                                                             |
| ----------------- | ------- | --------------------------------------------------------------------------------- |
| Server            | —       | Required. Host name or IP address of the PostgreSQL server                        |
| Port              | `5432`  | Server port                                                                       |
| Database          | —       | Required. Name of the database                                                    |
| Timeout           | `30`    | Seconds to wait for a statement to complete (also used for token requests)        |
| Query Passthrough | `true`  | `true`: SQL is sent to PostgreSQL unchanged. `false`: see **Supported SQL** below |

**Authentication**

**Auth Scheme** selects the mechanism: `Password` (default), `AzureAD`, `AzureServicePrincipal`, `AzureServicePrincipalCert`, `AzurePassword`, `AzureMSI`, `AwsIAMRoles` or `GCPServiceAccount`.

With every scheme other than `Password`, the connector obtains a short-lived token from the cloud provider and uses it as the database password. **User** must still be set to the database user or role that the identity is mapped to.

| Property                | Used by                                      | Notes                                                                                                        |
| ----------------------- | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------ |
| User                    | All                                          | Database user. Required                                                                                      |
| Password                | Password, AzurePassword                      | Database password, or the Microsoft Entra user's password                                                    |
| Use SSL                 | All                                          | `true`: an encrypted connection is required. `false` (default): encryption is used when the server offers it |
| Azure Tenant            | All Azure schemes except AzureMSI            | Tenant ID or domain, e.g. `contoso.onmicrosoft.com`                                                          |
| OAuth Client Id         | Azure schemes                                | Application (client) ID. For AzureMSI: optional, client ID of a user-assigned managed identity               |
| OAuth Client Secret     | AzureServicePrincipal, AzureAD               | Application client secret                                                                                    |
| OAuth Access Token      | AzureAD                                      | Pre-obtained access token; used as-is when set                                                               |
| OAuth Refresh Token     | AzureAD                                      | Used to obtain an access token when no access token is set                                                   |
| OAuth JWT Cert          | AzureServicePrincipalCert, GCPServiceAccount | Certificate store name or file path. For GCP: path to the service account JSON key file                      |
| OAuth JWT Cert Type     | AzureServicePrincipalCert                    | `USER` (default), `MACHINE`, `PFXFILE` or `PEMKEY_FILE`                                                      |
| OAuth JWT Cert Subject  | AzureServicePrincipalCert                    | Certificate subject; `*` (default) picks the first certificate in the store                                  |
| OAuth JWT Cert Password | AzureServicePrincipalCert                    | Password of the `.pfx` or encrypted PEM file                                                                 |
| AWS Access Key          | AwsIAMRoles                                  | Access key of the IAM user                                                                                   |
| AWS Secret Key          | AwsIAMRoles                                  | Secret key of the IAM user                                                                                   |

Schemes:

* `Password` — standard PostgreSQL user name and password.
* `AzureAD` — Microsoft Entra user, with a supplied access token or a refresh token.
* `AzureServicePrincipal` — Entra application with a client secret.
* `AzureServicePrincipalCert` — Entra application with a certificate.
* `AzurePassword` — Entra user name and password (no MFA).
* `AzureMSI` — managed identity of the Azure VM or App Service running appRules.
* `AwsIAMRoles` — IAM database authentication for Amazon RDS / Aurora. The AWS region is read from the server host name (`….<region>.rds.amazonaws.com`). Set **Use SSL** to `true`.
* `GCPServiceAccount` — Google Cloud service account, using its JSON key file.

**Network**

These settings apply to the token requests made by the Azure and GCP schemes. They do not affect the connection to the database itself.

Proxy: **Proxy Auto Detect** (default `false`), **Proxy Server**, **Proxy Port** (default `80`), **Proxy User**, **Proxy Password**, **Proxy Exceptions** (semicolon-separated bypass list), **Proxy Auth Scheme** (`BASIC` default, `DIGEST`, `NTLM`, `NEGOTIATE`, `NONE`).

Firewall: **Firewall Type** (`NONE` default, `TUNNEL`, `SOCKS4`, `SOCKS5`), **Firewall Server**, **Firewall Port**, **Firewall User**, **Firewall Password**.

**SSL Server Cert** — `*` to accept any certificate from the token endpoint, or a specific certificate.

**Logging**

| Property  | Default                                                                         | Notes                         |
| --------- | ------------------------------------------------------------------------------- | ----------------------------- |
| Log Level | `0`                                                                             | `0` disabled, `1` log to file |
| Log File  | `%APPDATA%\appStrategy\PortalData\Default\Logs\Connectors\PostgreSQL_Debug.log` |                               |

#### Supported SQL

**Tables and columns**

* Tables and views are addressed as `table` or `schema.table`, e.g. `public.customers`.
* PostgreSQL stores unquoted names in lower case: `Customers` and `customers` are the same table. Use double quotes only for names created in mixed case: `"MyTable"`.
* Primary keys, foreign keys and indexes are read from the database.

**SELECT**

Queries are executed by PostgreSQL as written. Everything the server supports is available: joins, sub-queries, `GROUP BY`, aggregate and window functions, `UNION`, common table expressions (`WITH`), JSON operators, `LIMIT` / `OFFSET`, and so on.

```sql
SELECT * FROM public.customers WHERE country = @country ORDER BY name
SELECT c.name, COUNT(*) AS orders
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.name
SELECT * FROM orders ORDER BY order_date DESC LIMIT 50
```

**INSERT, UPDATE, DELETE**

```sql
INSERT INTO customers (name, country) VALUES (@name, @country)
UPDATE customers SET country = 'IE' WHERE id = @id
DELETE FROM customers WHERE id = @id
```

* With **Query Passthrough** `true` (default), statements are sent unchanged. DDL (`CREATE`, `ALTER`, `DROP`, `TRUNCATE`), `INSERT … ON CONFLICT`, `RETURNING` and function calls can also be executed.
* With **Query Passthrough** `false`, INSERT, UPDATE and DELETE are parsed and rebuilt with parameters and quoted names before being sent. Quoted names are case-sensitive, so table and column names must then be written exactly as they exist in the database. SELECT and all other statements are still sent unchanged.
* Saving records through a workflow target requires the table to have a primary key (used to locate the row to update or delete).

**Parameters**

Named parameters are written `@name` (the `:name` form is also accepted).

```sql
SELECT * FROM orders WHERE customer_id = @customer AND order_date >= @from
```

**Transactions**

Explicit transactions are not supported. Each statement is committed when it completes.

#### Data Types

All standard PostgreSQL types are supported, including `uuid`, `bytea`, `numeric`, `money`, date/time and `interval` types, `xml`, `citext`, `hstore`, network address, geometric and full-text search types.

| Type                | Notes                                                                                              |
| ------------------- | -------------------------------------------------------------------------------------------------- |
| `json`, `jsonb`     | Read and written as text. Converted with `::json` / `::jsonb` when the record is saved             |
| `vector` (pgvector) | Read and written as text, e.g. `[0.1,0.2,0.3]`. Converted with `::vector` when the record is saved |


---

# 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.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/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.