# PostgreSQL

## Supported Capabilities

{% tabs %}
{% tab title="Supported Capabilities" %}
**General**

* **Metadata** — metadata extraction and display of asset information (tables, columns, schemas). Types collected: Schema, Table, Column, View
* **Profiling** — data profiling on the Profiler tab
* **Preview** — sample data preview
* **Data Quality** — data quality monitoring and observability
* **Configurable Collection** — selective ingestion of schemas/workspaces in Data Source Management
* **View Table** — view tables, which are virtual tables based on SQL queries

**Data Quality Monitors**

* Freshness
* Volume
* Field Health
* Custom SQL
* Schema Drift

**Lineage**

* **View Table Lineage** — tracks virtual tables (views) and their data dependencies
* **Foreign Key Lineage** — tracks relationships between tables via primary and foreign keys
  {% endtab %}

{% tab title="Not Supported" %}
**General**

* External Table
* Stored Procedure

**Data Quality Monitors**

* Job Failure

**Lineage**

* External Table Lineage
* SQL Query Lineage
* Stored Procedure Lineage
  {% endtab %}
  {% endtabs %}

## Connection Requirements

Connecting to decube is as easy as providing us with credentials to your PostgreSQL database. At a minimum, we require:

* `username`
* `password`
* `host address`
* `host port`
* `database name`

<figure><img src="/files/krmj4GIVvyeKBtkWnyYN" alt=""><figcaption><p>PostgreSQL</p></figcaption></figure>

The `source name` will be for you to differentiate and recognize particular sources within the decube application.

We strongly encourage you to create a decube **read-only user** for this credential purpose, which you can follow [here](#custom-user-for-decube).

### **Security Concerns**

If access to your database is protected by security measures, we allow for connecting via [SSH Tunneling](https://docs.decube.io/how-to-connect-data-sources/enabling-vpc-access/ssh-tunneling) or you could [whitelist our IP](https://docs.decube.io/how-to-connect-data-sources/enabling-vpc-access/ip-whitelisting). See more [here.](https://docs.decube.io/how-to-connect-data-sources/enabling-vpc-access)

### Custom User for decube

We highly recommend that you create a **Read-Only** user for decube. We have prepared a script that you may run on your PostgreSQL database to create this user.

1. Create a New User for decube

```
CREATE ROLE decube WITH LOGIN ENCRYPTED PASSWORD 'a_new_password';
```

2\. Allow connection to the database

```
GRANT CONNECT ON DATABASE "your_db_name" TO decube;
```

3\. We need access to information\_schema

```
GRANT USAGE ON SCHEMA information_schema TO decube;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO decube;
```

4\. You may need to run this per schema that you have based on the default behavior of the schema.

```
GRANT USAGE ON SCHEMA <schema_name> TO decube; 
GRANT SELECT ON ALL TABLES IN SCHEMA TO decube;
```

If you want all of the tables in all your schemas to be included in decube, run the following SQL script:

```sql
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace where nspname !~* 'pg|information_schema'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO decube $$, sch);
    EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO decube $$, sch);
    END LOOP;
END;
$do$;
```


---

# Agent Instructions: Querying This Documentation

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

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.decube.io/databases/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.