> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validio.io/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Connect Validio to PostgreSQL to monitor and validate your operational data.

PostgreSQL is an open-source relational database management system known for its extensibility, standards compliance, and support for advanced data types. Integrating Validio with your PostgreSQL database lets you monitor, analyze, and validate your data assets.

To integrate with PostgreSQL,

1. Create a service account in PostgreSQL with the appropriate permissions for Validio. See [Prerequisites](#prerequisites).
2. Create a Validio credential to connect to the service account. See [Add a PostgreSQL Credential](#add-a-postgresql-credential).
3. Create a Validio source to monitor the tables in the PostgreSQL database. See [Add a PostgreSQL Source](#add-a-postgresql-source).

## Prerequisites

Set up an account in PostgreSQL with the necessary permissions for Validio to read and validate your data. For more information about credentials in Validio, see [Credentials](https://docs.validio.io/v10.1/docs/credentials.md). For detailed information about permissions in PostgreSQL, refer to [Privileges](https://www.postgresql.org/docs/current/ddl-priv.html).

> [!WARNING]
>
> **Credential Permission Requirements**
>
> Validio Credentials **require** `VIEWER` access rights when connecting to sources to read and access data. Admins must ensure that they do not provide `EDITOR` access rights to their credentials.

You can use this SQL script to set up a user and grant the necessary permissions for the source in Validio:

1. Copy this script to a SQL worksheet.
2. Follow the steps described in the script.

```sql title="SQL" theme={null}
/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_user>                                          -- Name of validio-user. It's not recommended to use an already existing user.
<Some_Password123>                                      -- Password for validio-user.
<database_to_monitor>                                   -- Database to monitor.
<user_creating_tables> [, <user2_creating_tables>...]   -- One or more users who will create future tables validio-user should immediately get access to.
<schema_to_monitor> [, <schema2_to_monitor>...]         -- Optionally filter access to one or more schemas 

*/------------------------------------------------ Script -----------------------------------------------------

-- An admin-user, such as postgres, is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'postgres';

-- Create Validio-user with necessary grants
CREATE USER <validio_user> PASSWORD '<Some_Password123>';

-- Grant monitor-role to user
GRANT pg_monitor TO <validio_user>; 

-- Grant Validio-user right to connect to the database to monitor
GRANT CONNECT ON DATABASE <database_to_monitor> TO <validio_user>;

-- Grant access to the data to validate. You can either write your own statements, or to simplify the process use the following query which generates grant statements for all
-- existing and future tables in your database, or filtered only to specific schemas if you like. Run the query and then copy&paste the result (the generated statements) into a worksheet. 
-- Review the generated statements before you run them, to ensure the grants will apply as you prefer.
SELECT p1 || schema_name || p2 || CASE WHEN o = 0 THEN ':    --------------' ELSE ' TO <validio_user>;' END AS statement
FROM (
    SELECT 0 AS o, '--------------    Privileges for schema ' AS p1, '' AS p2 UNION ALL
    SELECT 1, 'GRANT USAGE ON SCHEMA ', '' UNION ALL
    SELECT 2, 'GRANT SELECT ON ALL TABLES IN SCHEMA ', '' UNION ALL
    SELECT 3, 'ALTER DEFAULT PRIVILEGES FOR USER <user_creating_tables> [, <user2_creating_tables...>...] IN SCHEMA ', ' GRANT SELECT ON TABLES'
) AS s,
information_schema.schemata
--    WHERE schema_name IN ('<schema_to_monitor>' [, '<schema2_to_monitor>'...]) -- Uncomment and edit this line to limit the result to specific schemas
ORDER BY schema_name, o;
```

## Add a PostgreSQL credential

![The PostgreSQL credential configuration form, showing host, port, user, password, and default database fields](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/810-postgressql-credential-config.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=43265383ac982be62351ca08e53bf974)

Configuration for PostgreSQL credential

To add a credential for PostgreSQL,

1. Navigate to **Credentials** and click **+ New credential**.
2. Under **Namespace**, select a namespace where the resources will be created.
3. For **Credential Type**, select **PostgreSQL Credential**.
4. Fill in the credential parameter fields. Refer to the [PostgreSQL Credential Parameters](#postgresql-credential-parameters) table.
5. Check **Use for catalog and schema checks** to automatically discover credentials and add them to the catalog page.
6. Click **Create**.

Validio will validate the connection to the PostgreSQL account. If validation passes, Validio will automatically start fetching data. If validation fails, check that you provided the correct parameter values and try again.

Once the credential is created, you can add a [source](https://docs.validio.io/v10.1/docs/sources.md) to monitor PostgreSQL data.

### PostgreSQL credential parameters

| Field | Description |
| :- | :- |
| Name | Identifier for the credentials. Used when accessing Sources. |
| Host | DNS hostname or IP address to the PostgreSQL database server. |
| Port | Port number of the PostgreSQL database server. |
| User | Username of PostgreSQL account with read access to the desired table. |
| Password | Password of the specified PostgreSQL user. |
| Default database | Name of the default PostgreSQL database where the table to read is included. |
| SSL mode | How Validio secures the connection to the PostgreSQL server. Defaults to **Prefer**. See [SSL modes](#ssl-modes). |
| TLS CA certificate | (Optional, SSL mode **Require**, **Verify CA**, or **Verify full**) Paste or upload the CA certificate, in PEM format, used to verify the server certificate. Leave empty to use the system trust store. |

### SSL modes

The **SSL mode** sets whether Validio encrypts the connection to the PostgreSQL server, and how strictly it verifies the server certificate. The modes are listed from least to most strict.

| Mode | Behavior |
| :- | :- |
| Disable | Connects without SSL. |
| Allow | Tries a non-SSL connection first, and falls back to SSL if that fails. |
| Prefer | Tries an SSL connection first, and falls back to a non-SSL connection if that fails. This is the default. |
| Require | Connects with SSL only, without verifying the server certificate. If you provide a TLS CA certificate, Validio verifies the server certificate against it, as with **Verify CA**. |
| Verify CA | Connects with SSL only, and verifies that a trusted certificate authority issued the server certificate. |
| Verify full | Connects with SSL only, verifies the server certificate as with **Verify CA**, and also verifies that the server hostname matches the one in the certificate. |

Credentials created before SSL mode was available connect as **Prefer**. Changing the mode to **Disable**, **Allow**, or **Prefer** removes any stored TLS CA certificate.

## Add a PostgreSQL source

![The PostgreSQL source configuration form, showing credential, database, schema, and table selection](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/810-postgresql-source-config.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=1df64a1cbbbe0949e934999315464d3e)

Configuration for PostgreSQL source

To add a source for PostgreSQL,

1. Navigate to **Sources** and click **+ New source**.
2. Under Source type, select **PostgreSQL**.
3. Under **Config**,
   1. Select the valid **Credential** or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. **Select an existing table** -- Enter the **Database**, **Schema**, and **Table** to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the [Source Configuration Parameters](#source-configuration-parameters) table.
      2. **Use Custom SQL** -- Write a valid SQL query to specify the tables to monitor.
   3. Set the **Polling schedule**, which is how frequently the validators on the source will check for changes.
4. Under **Schema**, click **Continue** to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under **Source details**,
   1. Add **Tags** to help group related sources or to use for routing notifications.
   2. Add an **Owner** who will be the contact for incident notifications.
   3. Assign a **Priority**, which indicates the importance of incidents detected on this source.
6. Click **Continue** to create the source.
   Source names are generated automatically and  will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

### Source configuration parameters

| Parameter | Description |
| :- | :- |
| Credential | PostgreSQL credential. |
| Database | Name of the PostgreSQL database. |
| Schema | Name of the schema that contains the table. |
| Table | Name of the table to read data from. |

## Remove Source permissions

You can use this SQL script to remove configured permissions for the Source in Validio:

1. Copy this script to a SQL worksheet.
2. Follow the steps described in the script.

```text title="Text" theme={null}
/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_user>       -- Name of validio-user you want to remove.
<validio_group>      -- Name of validio-group you want to remove (if you have set up a group for Validio)

*/------------------------------------------------ Script -----------------------------------------------------

-- An admin-user, such as postgres, is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'postgres';

-- This script generates statements to remove Validio. Run the script, then copy&paste the statements into a worksheet and run them to remove all Validio-related entities (see inline-comments below for more details) from this Catalog. We recommend you to carefully look through the statements before you run then to make sure you really want to remove each entity.
SELECT 'REASSIGN OWNED BY <validio_user> TO CURRENT_ROLE;' AS statement UNION ALL  -- Reassign ownership from Validio-user to you.
SELECT 'DROP OWNED BY <validio_user>;' UNION ALL  -- Revoke all privileges from Validio-user
SELECT 'DROP USER <validio_user>;' UNION ALL  -- DROP Validio-user
SELECT 'DROP GROUP <validio_group>;'  -- DROP Validio-group (Remove this row if you haven't set up a Validio-group)
;
```