For SDP 10.5 and above series, STEP 1 can be done using the createPostgresUser.bat in ServiceDesk\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\ServiceDesk\bin>createPostgresUser.bat -sU postgres -sp <super-user-password> -U <new-username> -p <new-password> -r readonly

         i. For the options -sU and -sp, the Postgres super user username and password are to be provided. Default super user name is 'postgres'.

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

d. Open windows command prompt

e. Change directory to ServiceDesk\bin and execute startDB.bat. Wait for the success message.

C:\Program Files\ManageEngine\ServiceDesk\bin>startDB.bat

"C:\Program Files\ManageEngine\ServiceDesk\bin\\.."

Database server successfully started...

f. Change directory to ServiceDesk\pgsql\bin

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

g. 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\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=#

h. Grant the necessary permissions with following commands

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public to sdpreadonly;

GRANT

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_catalog to sdpreadonly;

GRANT

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA information_schema to sdpreadonly;

GRANT

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

servicedesk=# \q

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\bin and execute startDB.bat. Wait for the success message.

C:\Program Files\ManageEngine\ServiceDesk\bin>startDB.bat

"C:\Program Files\ManageEngine\ServiceDesk\bin\\.."

Database server successfully started...

d. Change directory to ServiceDesk\pgsql\bin

C:\Program Files\ManageEngine\ServiceDesk\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\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

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public to sdpreadonly;

GRANT

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_catalog to sdpreadonly;

GRANT

servicedesk=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA information_schema to sdpreadonly;

GRANT

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

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