Remote read only access to database for Postgres customers

Remote read only access to database for Postgres customers
Use case: Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server.

By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow access from remote machine if required. 

Also, we need to create a user with  read only  permission to the servicedesk database alone.

ONLY  this user should be able to access the Postgres server from remote machines -- with a password prompt. SDP's default PG user 'sdpadmin' or PG super user 'postgres' have higher privileges in the Postgres server. These users should  NOT  be allowed access to Postgres server from remote machies.

Please find below, the steps of how this can be accomplished.


STEP 1  : Create a Postgres DB user 'sdpreadonly' with read only access to the 'servicedesk' database

For SDP 10.5 and above series,  STEP 1  can be done using the createPostgresUser.bat in ServiceDesk Plus MSP\bin directory.

a. Stop the ServiceDesk Plus application.
b. Open windows command prompt
c. Change directory to ServiceDesk\bin and invoke the createPostgresUser.bat script

C:\Program Files\ManageEngine\ServiceDeskPlus MSP\bin> createPostgresUser.bat -sU postgres -sp   <super-user-password>  -U dummy -p dummy123 -r readonly

PS : Contact team for  <super-user-password> details

-U dummy
-p dummy123
Above values in the command are sample user name and password. You may replace it with a new user name and password.

The option - r readonly  will make the newly created user a read only user. 


For SDP 9.4 and 10.0 series build, please follow the instructions mentioned below for  STEP 1

a. Stop the ServiceDesk Plus application.
b. Open windows command prompt
c. Change directory to ServiceDesk Plus MSP \bin and execute startDB.bat. Wait for the success message.

C:\Program Files\ManageEngine\ServiceDesk Plus MSP \bin>startDB.bat
"C:\Program Files\ManageEngine\ServiceDesk Plus MSP \bin\\.."
Database server successfully started...

d. Change directory to ServiceDesk Plus MSP \pgsql\bin

C:\Program Files\ManageEngine\ServiceDesk Plus MSP \bin>cd ..\pgsql\bin

e. Execute the below command to connect to Postgres query console using PG super user (postgres). Enter the password when prompted

C:\Program Files\ManageEngine\ServiceDesk Plus MSP \pgsql\bin>psql.exe -U postgres -p 65432 -h 127.0.0.1 -d servicedesk
Password for user postgres:
psql (10.5)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

servicedesk=#

f. Create a user with password and grant read only access to servicedesk database with following commands

servicedesk=# create user sdpreadonly;
CREATE ROLE

servicedesk=# ALTER USER  sdpreadonly  with password '<your-password-here>';
ALTER ROLE

servicedesk=# GRANT CONNECT ON DATABASE servicedesk TO  sdpreadonly ;
GRANT

servicedesk=# GRANT USAGE ON SCHEMA public TO  sdpreadonly ;
GRANT

servicedesk=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO  sdpreadonly ;
GRANT

servicedesk=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO  sdpreadonly ;
ALTER DEFAULT PRIVILEGES

g. Exit from Postgres query console by entering '\q'

servicedesk=# \q





STEP 2  : In ServiceDesk/pgsql/ext_conf/postgres_ext.conf, add below line to end of file
In 9.4 series and 10.0 series builds this file can be found in ServiceDesk/pgsql/data/postgres_ext.conf

listen_addresses = '*'

Default value for  max_connections  in postgres_ext.conf is 50. Of this 40 can be used by SDP application (configured in ServiceDesk/conf/database_params.conf).
Since we are allowing third party application connections, this can be  increased to 60.

NOTE :  The queries from the third party application will take up resources on the production database. Badly written queries may impact the application performance.




STEP 3  : In ServiceDesk/pgsql/data/pg_hba.conf, add entries for allowing access.

This can be done in two ways.

1. To allow access from any IP address, add the following line in the end of the file.

host    servicedesk     sdpreadonly         0.0.0.0/0               md5


(OR)

2. To allow access from particular IP addresses alone, add one entry for each IP address in the format mentioned below at the end of the file.

host    servicedesk      sdpreadonly          <ip-address-1>/32               md5
host    servicedesk      sdpreadonly          <ip-address-2>/32               md5

Note  : Using (2) is more secure but requires the client machines (machines on which third party tool runs) to have static IP address.


Save and close both the files.


Once this is done, start the ServiceDesk Plus application (which will start the Postgres database).

Now user will be able to connect from remote machine or third party applications using the host (server machine ip address), port (default 65432), username (sdpreadonly), password (<your-password>)

NOTE : Although the login is restricted with username and password, the communication over the connection is NOT encrypted. This means, any user monitoring the network traffic will be able to see the query statements and query response data.

To make this secure, please use SSL encryption for Postgres server.

Refer the Postgres docs for instructions on how this can be done.
https://www.postgresql.org/docs/10/ssl-tcp.html




        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

                      • Error While connecting default Postgres Database

                        If you are getting the below error while connecting the postgresDB , please follow the steps below : Error : psql: FATAL: no pg_hba.conf entry for host “::1”, user “postgres”, database “servicedesk”,SSL off Please open pg_hba.conf under [ServiceDesk ...

                      • How to connect to external postgres(external DB)

                        1. From the postgres installed path navigate to bin folder and open a command prompt 2. Execute the command --> pg_ctl -D "C:\Program Files\PostgreSQL\10\data" start (Postgres server gets started) 3. Connect to DB using the command-->psql -U postgres ...

                      • Steps to enable Read Committed Snapshot Isolation (RCSI)

                        Enabling Read Committed Snapshot Isolation in MS SQL server In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application server performance and crash issues. What is RCSI? Since databases handle ...

                      • No Managed Connections. Steps to enable Read Committed Snapshot Isolation- MSSQL

                        Enabling Read Committed Snapshot Isolation in MS SQL server In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application server performance and crash issues. What is RCSI?   Since databases ...

                      • MSP - Remote Asset Explorer Compatible Versions

                        Remote Asset Explorer-MSP Compatible Version From MSP 14000 - Compatible RAE version 6970-6985 From MSP 13000 - Compatible AE version (6900-6979). But RAE - DC integration, - If 6900,6901 RAE is used, then latest DC will not work. Bundled DC in RAE ...

                        Related Products