Connect PostgreSQL database

You can connect your PostgreSQL database to Ubiq in just 4 steps. Please replace the parts in bold below as per your requirement.

Step 1. Create a read-only user in your database

Log into PostgreSQL database (e.g $mydb) with an admin/owner username (e.g postgres) and create a unprivileged User (username - $readonlyuser, password - $password)

$ psql -U postgres $mydb

$mydb=# CREATE ROLE $readonlyuser password '$password' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;


Step 2. Grant read-only permissions to the user

Grant usage & select permissions for your read-only user ($schema is generally 'public') to all tables in $mydb database.

$mydb=# GRANT USAGE ON SCHEMA $schema TO $readonlyuser;

$mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA $schema TO $readonlyuser;


Step 3. Add Ubiq's public IP in pg_hba.conf

Open pg_hba.conf file (Windows Location or Linux location). Scroll down to the bottom of the file. You will see the following line with a list of permissions.

# TYPE   DATABASE   USER   ADDRESS   METHOD

Add the following line

host   $mydb   $readonlyuser   54.245.93.65/32   password

For more information about this file, please refer here. $mydb is the database name, $readonlyuser is the username & $password is the password to be provided in the Create Connection form below.


Step 4. Create connection

When you click on Create Connection on the Data Sources tab, on project homepage, you will see the following form.

Select the type of database you want to create a connection for. The form has the following fields:

  • Connector Name - Connection/Data source name you want to refer the connection as.
  • Host IP or URL - the host name or IP address of the server your database is hosted on
  • Database name - the name of the database you're connecting
  • User name - the name of a read-only user to your database
  • Password - the password for the read-only user
  • Port Number - the port that the database is listening to (default: 5432 )

Please ensure the host name, database name, user name, password & port are entered correctly before you click Connect. On successful connection, you will see the data source listed under the Data Sources Tab. In case of issues, you will see an intuitive message on the form.

For more information about user roles & privileges, please refer here or here