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
- 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.
- 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.
- 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:
- 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
- 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.
- 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
New to ADSelfService Plus?
Related Articles
External Postges - PG Extension Issue
Error: If cxs who are using external postgres(not bundled postgres) faces below error, 1. Migration fails with "Upgrade halted. Please create postgres extension pg_trgm and try again" error. (or) 2. Start-up fails with "Application startup halted. ...How to Connect External Postgres
Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should ...How to change the database from MySQL/PostgreSQL to MSSQL
The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. For ...Securing PostgreSQL Database Connection with SSL in ServiceDeskPlus
Overview This article serves as a comprehensive guide for customers intending to secure their database connections when using with ServiceDeskPlus Application with PostgreSQL database, similar to securing Microsoft SQL Server (MSSQL) connections. The ...How to reset database users' Password in bundled Postgres?
We have built several database users in our product with bundled postgres, including "rouser," a read-only account used to configure the external reporting tool, "sdpadmin," who handles database activities integrated with our product, and "postgres," ...