How to access our bundled PostgreSQL from External Applications & Servers

How to access our bundled PostgreSQL from External Applications & Servers


      To access a PostgreSQL database from an external machine, you need to configure PostgreSQL to allow remote connections, open the necessary network ports, and ensure proper authentication. Here are the steps to access PostgreSQL from an external machine:

Step 1 : Configure Access Control
  1. Open the pg_hba.conf file, located in the "<Product_Install_directory>/pgsql/data" directory. This file controls access to the PostgreSQL server. We need to add access rules to specify which hosts or networks are allowed to connect. 
  2. Add the host row with "IP address of the Application machine/a CIDR mask length" to enable PostgreSQL server accept connection from external application servers as mentioned in the below example.
  1. host    all             all             XX.XX.XX.XX/XX            md5
                                                                                     
Adding 0.0.0.0/0  instead of IP address enables access for all machine. This is not recommended since it raises a vulnerability that database server will be reachable from all the servers in the network.
Also, md5 is the recommended authentication mode. since "trust" mode will not undergo any authentication process. 

Step 2 : Configure PostgreSQL for Remote Access
  • open pgsql\ext_conf\postgres_ext.conf file.
    • add an entry listen_addresses= '127.0.0.1,<ip address(es)>'  at the end of file, which Specifies the TCP/IP address(es) on which the pgsql server is to listen for connections, so that external application servers can connect to that IP address. In general, this will be the <product_installed_server_machine> IP address. 
Similar to the host records, The special entry * corresponds to all available IP interfaces, but which is not recommended.
It is not advised to modify the default postgresql.conf file bundled along with PostgreSQL database. All the configuration changes are to be done in file in ext_conf folder of PostgreSQL database. 

Step 3 : Open Firewall Ports:
  1. If you have a firewall running on the PostgreSQL server, ensure that it allows incoming connections on the PostgreSQL port (default is 65432) from the external machine.
Step 4 : Restart PostgreSQL

  1. After making changes to PostgreSQL configuration files, you must restart the PostgreSQL server for the changes to take effect.
      On the external machine, use a PostgreSQL client/applications/terminal to connect to the PostgreSQL server using the IP address or host name of the <product_installation_server_name> server configured in the listen_address (refer second step) and the appropriate port (default is 65432). You also need the  username, password and database name. By default, database name will be 'servicedesk' or 'assetexplorer' with respect to the product.

For example in the terminal, we can connect by using the following command.
  1. psql -h <PostgreSQL_server_IP> -p 65432 -U <username> -d <database_name>
      Kindly contact our support Team if you face any issues in configuring this Setup