PostgreSQL

 > Data Sources > PostgreSQL

To connect a PostgreSQL Data Source go to Data Sources page.

Click on the green Add Data Source button and choose PostgreSQL.

Then you have to insert these configurations:

  • Type: type of connection, it can be Direct or SSH Tunnel
  • Name: name you can define to identify this database connection.
  • Database: database name.
  • Host: hostname of the database.
  • Port: port the database runs on (default is 5432).
  • Username: database user login.
  • Password: database user password.
  • Security: check if you want the connection to be SSL secured.
  • Show Advanced: optional additional configuration.

postgresql

 

Direct Connection #

Allows Viur to connect directly to your database, in most cases you need to allow the connection from the Viur Server.

Important Note: You may need to whitelist the Viur Server IP 23.102.130.176

 

SSH Tunnel Connection #

A SSH Tunnel consists of an encrypted tunnel created through a SSH protocol connection it can be used to transfer unencrypted traffic over a network through an encrypted channel.

It is useful if you do not wish to modify any firewall rules, you can use an SSH tunnel to make an outbound encrypted request from your network to ours instead.

It allows Viur to connect to your database through a SSH Server, however you need to configure additional information as follows:

SSH Tunnel Connection

  • Host: hostname or IP address of the SSH Server.
  • Port: port of the SSH Server.
  • Username: SSH Server user login.
  • Authentication Type: type of the authentication, which can be Password or a Key.
    • Password: password for authentication.
    • Private Key: key for authentication, it must be provided in OpenSSH format.
  • Passphrase: password for the security private key.

Tip: You may click Connect button to check the current SSH configuration.

Important Notes

  • Though we use the highest security best practices, we recommend that you create a read-only user for the database.
  • If your are using a password manager like LastPass, it might wrongly replace your database credentials for the credentials you use to authenticate in Viur, causing an error when saving the connection. To avoid this, you must disable the password manager browser's extension temporarily.

 

Create a Database Read-Only User #

This SQL command creates and grants the user described by [username] and [password] with read-only access to ALL tables in your database defined by [database_name].

CREATE USER [username] WITH PASSWORD '[password]';
GRANT CONNECT ON DATABASE [database_name] TO [username];
GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO [username];

If you would like to restrict access to only certain tables in your database separate the various tables names [table_name] with a comma as shown above.

CREATE USER [username] WITH PASSWORD '[password]';
GRANT CONNECT ON DATABASE [database_name] TO [username];
GRANT SELECT
ON TABLE [table_name],[table_name], ...
TO [username];

 

Show Advanced #

These configurations are optional:

  • Query Timeout: maximum time the query is allowed to run before the database engine cancels it.
  • Cache Duration: the duration of time the retrieved data will be stored on Viur's cache.
  • Timezone: the dates will be shown in relation to the chosen timezone.

Show Hide Advanced

 

Note
Don't forget to Press Save to add this connection.

Still need help? Get in touch!
Last updated on 9th Dec 2023