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.