--- title: "How to allow remote access to PostgreSQL" url: "https://ploi.io/documentation/database/how-to-allow-remote-access-to-postgresql" published: "2025-09-05" last_updated: "2025-09-05" --- # How to allow remote access to PostgreSQL Setting up remote access to a PostgreSQL server on your server can be useful for various reasons, such as managing your database from different locations or connecting applications hosted on separate servers to your database. It allows you to centralize your data storage and perform database administration tasks more efficiently. Here's a simple guide to set up remote access to a PostgreSQL server on an Ubuntu server, assuming you already have a PostgreSQL server installed: 1. Configure PostgreSQL server to allow remote connections ---------------------------------------------------------- By default, PostgreSQL only listens for local connections. You need to modify two configuration files to enable remote connections. ### Edit postgresql.conf You can edit the database configuration in Ploi by going to the **Manage** tab and pressing **Edit database configuration**. If you want to do it manually: open the PostgreSQL configuration file (postgresql.conf) in a text editor. The location of this file typically is `/etc/postgresql/{version}/main/postgresql.conf` where {version} is your PostgreSQL version (e.g., 14, 15, 16). Find the line with `listen_addresses` and change its value to `'*'` to allow connections from any IP address, or set it to specific IP addresses to restrict remote access: ``` listen_addresses = '*' ``` If the line is commented (starts with #), uncomment it by removing the # symbol. Save the file. ### Edit pg\_hba.conf Next, you need to configure client authentication. Open the pg\_hba.conf file, typically located at `/etc/postgresql/{version}/main/pg_hba.conf`. Add the following line to allow connections from any IP address (adjust as needed for your security requirements): ``` # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 ``` For better security, you can restrict access to specific IP addresses or IP ranges: ``` host all all 192.168.1.0/24 md5 host all all 10.0.0.5/32 md5 ``` The authentication methods can be: - `md5`: Password-based authentication (encrypted) - `scram-sha-256`: More secure password authentication (recommended for PostgreSQL 10+) - `trust`: No authentication (not recommended for remote connections) Save the file. 2. Restart PostgreSQL service ----------------------------- Restart the PostgreSQL service for the changes to take effect. You can do this via the **Service** tab in Ploi or by running this command in SSH: ``` sudo systemctl restart postgresql ``` 3. Create a remote user and grant privileges -------------------------------------------- Now you'll need to create a user that can connect remotely. You can do this by pressing the dropdown in the **Databases** tab behind your database, and press **Users**. Next, create a fresh user with remote access enabled, optionally fill in an IP address for additional security. Alternatively, you can create a user via the PostgreSQL command line: ``` sudo -u postgres psql ``` Then run: ``` CREATE USER remote_user WITH PASSWORD 'your_secure_password'; GRANT ALL PRIVILEGES ON DATABASE your_database TO remote_user; ``` For more granular permissions: ``` -- Grant specific privileges GRANT CONNECT ON DATABASE your_database TO remote_user; GRANT USAGE ON SCHEMA public TO remote_user; GRANT CREATE ON SCHEMA public TO remote_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO remote_user; ``` Exit PostgreSQL: ``` \q ``` 4. Update firewall rules ------------------------ If you have a firewall enabled (e.g., UFW), you will need to allow connections to the PostgreSQL server. By default, PostgreSQL listens on port 5432. To allow incoming connections on this port, you can head over to the **Network** tab in Ploi and allow port 5432 there. Alternatively, via command line: ``` sudo ufw allow 5432/tcp ``` For better security, restrict access to specific IP addresses: ``` sudo ufw allow from 192.168.1.100 to any port 5432 ``` 5. Test the remote connection ----------------------------- You can test the remote connection using the psql client from another machine: ``` psql -h your_server_ip -U remote_user -d your_database -p 5432 ``` Or use a connection string: ``` psql "postgresql://remote_user:your_password@your_server_ip:5432/your_database" ``` Security best practices ----------------------- 1. **Use strong passwords**: Always use complex, unique passwords for database users 2. **Limit IP addresses**: Restrict access to specific IP addresses when possible 3. **Use SSL/TLS**: Configure PostgreSQL to use encrypted connections 4. **Regular updates**: Keep PostgreSQL and your system updated 5. **Monitor access logs**: Regularly check PostgreSQL logs for unauthorized access attempts 6. **Use connection pooling**: For applications, consider using connection pooling to manage connections efficiently 7. **Implement proper user permissions**: Grant only necessary privileges to each user Troubleshooting --------------- If you cannot connect remotely, check the following: 1. **PostgreSQL is running**: `sudo systemctl status postgresql` 2. **Configuration is correct**: Verify listen\_addresses in postgresql.conf 3. **Authentication is configured**: Check pg\_hba.conf entries 4. **Firewall allows connections**: Verify port 5432 is open 5. **Network connectivity**: Test with `telnet your_server_ip 5432` 6. **Check PostgreSQL logs**: Located at `/var/log/postgresql/postgresql-{version}-main.log` If everything is set up correctly, you should now be connected to your PostgreSQL server remotely.