Before ingesting PostgreSQL metadata into Dawiso, prepare your account for authentication by configuring a PostgreSQL user and grant it the necessary permissions.

Connection prerequisites

  • Make sure you are connected as a superuser or a role with the CREATEROLE privilege

Connection configuration

Dawiso Cloud currently supports traditional authentication method using credentials. You will need a user with required permissions and a password to ingest data from the PostgreSQL service.

Create new user

Create a new user (e.g., dawiso_reader) in Oracle and assign it a strong password using the following statement:

CREATE USER dawiso_reader WITH PASSWORD 'password123';
  • Make sure to replace the password123 with a strong password. For more details, refer to the official PostgreSQL documentation.
  • If you choose your own username, make sure to replace it in all following statements.

Grant permissions

System catalog access

Run the following statements to grant the newly created user access to PostgreSQL’s system catalog to discover database structure and metadata:

GRANT CONNECT ON DATABASE database_name TO dawiso_reader;
GRANT USAGE ON SCHEMA pg_catalog TO dawiso_reader;
GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA pg_catalog TO dawiso_reader;
  • Replace the database_name with the actual database name.
  • Minimum permissions allow Dawiso to:
    • Connect to the database
    • Readtable definitions, column types, constraints, and relationships from PostgreSQL metadata

Access to business schemas

Grant access to each schema containing tables you want Dawiso to ingest:

GRANT USAGE ON SCHEMA schema_name TO dawiso_reader;
GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA schema_name TO dawiso_reader;
  • Replace the schema_name with the actual schema names.
  • Repeat for all your schemas.

[Optional] Grant access to future tables

This step is optional but recommended as without this, you will need to manually re-grant permissions when new tables are created.

To automatically grant permissions to tables created after setup, add default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT SELECT, REFERENCES ON TABLES TO dawiso_reader;
  • Repeat for every business schema in your database.

Using the SUPERUSER option

As an alternative to the granular permission setup described above, you can create a PostgreSQL superuser. This bypasses all permission requirements and no additional GRANT statements are necessary:

CREATE USER dawiso_integration SUPERUSER PASSWORD 'password123';
Warning

A superuser in PostgreSQL bypasses all permission checks. This means they can execute any command, access all data, and even perform operations that may crash the database server or access the underlying operating system. Use this option with extreme caution and only if you are authorized to do so.

Sample script

You can copy and paste the following script and run it on your PostgreSQL server.

You must have sufficient privileges to create users (for example, connect as a superuser or a role with the CREATEROLE privilege).

CREATE USER dawiso_reader WITH PASSWORD 'password123';

GRANT CONNECT ON DATABASE database_name TO dawiso_reader;

GRANT USAGE ON SCHEMA pg_catalog TO dawiso_reader;
GRANT USAGE ON SCHEMA schema_name TO dawiso_reader;

GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA pg_catalog TO dawiso_reader;
GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA schema_name TO dawiso_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT SELECT, REFERENCES ON TABLES TO dawiso_reader;
  • Make sure to replace the following placeholders with real values:
    • password123 with a strong password.
    • database_name with the actual database name.
    • schema_name with actual schema names. If you are using the default schema, replace the value with public.
    • If you chose a custom user name, make sure to replace it in this snippet.