# PostgreSQL

A common and reliable open-source relation database system with support for advanced SQL standards and diverse data types.

## Supported Connection Methods

Available methods for connections to the target system from Lumi AI:

* [x] **Direct** 

  *The default / typical connection option. Interfaces directly with the target system over the Internet with no mediation. Suitable for most cloud-hosted scenarios or public-facing resources. See* [*Network Configuration*](https://docs.lumi-ai.com/using-lumi/network-configuration) *for more details.*
* [x] **Gateway**\
  \&#xNAN;*An alternative connection method leveraging a Lumi AI Data Gateway. This uses Lumi AI's purpose-built connection agent to mediate communications. Ideal for restricting access to systems within a protected network. For more information, see* [*Data Gateway*](https://docs.lumi-ai.com/product-features/data-gateway)*.* 

## Supported Limits

The following are limits that can be configured for the system to moderate access and usage from users in Lumi AI:

* [x] **Cost Limit**\
  \&#xNAN;*Before running a query, if the system supports it, the system-specific compute cost (or surrogate) estimate will be processed and compared to an organization-level/admin-set cost limit for systems of this type (if configured/set). If exceeded, the query will not run (and either the workflow will attempt an optimization or the user will be notified).*
* [x] **Duration Limit**\
  \&#xNAN;*An alternative to cost, queries will be stopped the system supports a duration/timeout limit and one is set/configured at the organization level (across systems).*

## Available Parameters

These properties are the essential source system connection properties that all queries are directed towards.

*\* Required parameters*

{% hint style="info" %}
Note: The Gateway parameter is common to all systems (when supported) and is only available when gateway is the selected connection method.
{% endhint %}

<details>

<summary>Host*</summary>

Domain or IP of the database to allow a connection to Lumi AI.

:thumbsup: **Valid Examples**

* Domain name: *docs.lumi-ai.com*
* IPv4: *55.88.92.101*

:thumbsdown: **Invalid Examples**

* IPv4 with alphanumeric characters: *123.abc.def.456*
* Incomplete domain name resolutions: *lumi-ai.com*

</details>

<details>

<summary>Port*</summary>

Access port to allow for connections to the database.

:thumbsup: **Valid Examples**

* Default PostgreSQL port: *5432*

:thumbsdown: **Invalid Examples**

* Known Port Numbers (0-1023): *80*

</details>

<details>

<summary>Username*</summary>

Username of the generated database user which will be used for interactions and transactions.

**Special Considerations**

* If double quotes are used, then the name will be case sensitive

:thumbsup: **Valid Examples**

* Uses alphanumeric characters : *lumi1*
* Utilizing underscore (\_) in place of spaces: *user\_number*

:thumbsdown: **Invalid Examples**

* Starting with a number: *1lumi*
* Utilizing spaces: *user name*
* Using invalid characters: *user#1*

</details>

<details>

<summary>Password*</summary>

Password of the generated database user which will be used for interactions and transactions.

</details>

<details>

<summary>Database*</summary>

Literal database name that will be used for transactions within the agents.

**Special Considerations**

* If case sensitivity is required, utilize double quotes for database name; ex. "New\_database"

:thumbsup: **Valid Examples**

* Uses alphanumeric characters: *lumi1*
* Utilizing underscore (\_) in place of spaces: *new\_database*

:thumbsdown: **Invalid Examples**

* Starting with a number: *1lumi*
* Utilizing spaces: *user name*
* Using reserved words unquoted: *select*

</details>

## System Permissions & Configuration

Provided user must have the following:

* [x] ROLE with LOGIN privileges&#x20;
* [x] PRIVILEGE to CONNECT on database
* [x] PRIVILEGE to USAGE on schema
* [x] SELECT PRIVILEGE in table(s)
* [x] (if applicable) SELECT PRIVILEGE on table(s) for column(s)

For configurations:

* [x] Ensure the server is allowing connections from the Lumi gateway or server

## Special Considerations

* Special characters and case sensitivity can be utilized in database names and usernames via encasing the name in double quotes (" "), but is not recommended

## Common Issues

* Ensure the firewall settings allow for a connection to the database