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
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 ...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 ...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 resolve Connectivity issues with Postgresql Database from our product?
Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...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 ...