HomeDocumentationChangelog
HomeRequest DemoContact
HomeRequest DemoContact

PostgreSQL

Create a PostgreSQL Source

Prepare credentials and permission in PostgreSQL

Certain credentials and permission are required for Validio to validate your data.

For detailed information about permissions in PostgreSQL, refer to Privileges.

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.
/*--------------------------------------- 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 alread existing user.
<Some_Password123>                                      -- Password for vaidio-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;

Create the Source in Validio

Credential parameters

FieldRequiredDescriptionExample
NameIdentifier for the credentials. Used when accessing Sources.service_acount_product_staging
HostDNS hostname or IP address to the PostgreSQL database server.
PortPort number of the PostgreSQL database server.
UserUsername of PostgreSQL account with read access to the desired table.
PasswordPassword of the specified PostgreSQL user.
Default databaseName of the default PostgreSQL database where the table to read is included.

Configuration parameters

FieldRequiredDescription
NameIdentifier for the Source. Used when setting up validators.
DatabaseName of the PostgreSQL database.
SchemaName of the schema that contains the table.
TableName of the table to read data from.
Cron presetDetermines how often to query the bucket for new data based on a preset option.

Select custom to use your own cron expression.
Cron expressionDetermines how often to query the table for new data, based on cron expression.

Expression of cron presets are displayed here.

Used to enter your own cron expression.

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.
/*--------------------------------------- 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)
;