PostgreSQL

Estimated reading time: 1 min

In order to ensure access to your database you’ll need to make sure your firewall is configured to allow the address 52.21.150.73 to connect to your server. If your database is on a private network and you do not want it to be accessible from the internet, you can set up a SSH tunnel.

Navigate to your Datasets page, click ‘plus icon’ on the bottom left and select Datasource. Choose PostgreSQL from the data sources list.

To connect to your PostgreSQL database you should first create a read-only user on your database.

Connect to your PostgreSQL server instance using pgAdmin. Then right-click on ‘Group Roles’ and select ‘New Group Role’.

Type in the name you can easily remember and understand the meaning and click ‘OK’.  You do not need to create a password for the Group role; we will do it later for the login.

Expand ‘Databases’, then right-click on the database you would like to connect to Slemma and select ‘Properties’.

In the ‘Privileges‘ tab select the newly created group from the ‘Role’ drop-down menu, check the ‘Connect’ box and click ‘Add/Change’. When done, click ‘OK’.

Click the expand arrow next to your database and expand ‘Schemas’. Right-click ‘public’ and select ‘Grant Wizard’.

On the ‘Selection’ tab click ‘Check All’.

In the ‘Privileges‘ tab select the newly created group from the ‘Role’ drop-down menu and check the ‘Select‘ box, then click ‘Add/Change’. When done, click ‘OK’.

Right-click ‘Login Roles’ and select ‘New Login Role’.

Type a descriptive name for the user.

On the ‘Definition‘ tab enter a password.

On the ‘Role membership‘ tab move the newly create group to the ‘Member‘ window and click ‘OK’.

Use these credentials (login role and password) when setting up a PostrgeSQL connection for Slemma.

Next you should allow Slemma to connect to your database. To do this you should modify the pg_hba.conf file to allow the created above user connect to your database from Slemma’s IP (52.21.150.73). Add the following line to the file:

host mydatabase slemma_read_only 52.21.150.73/32 md5

Where ‘mydatabase’ is the name of the desired database and ‘slemma_read_only’ is a created user on the first step.

Restart your PostgreSQL server to apply changes. Refer to the PostgreSQL documentation to learn more.

Was this article helpful?
Dislike 1
Views: 1088