# 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="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-f21295624730daeb00b9608513e816b7f8c6c0fc%2Fimage.png?alt=media" 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$;
```