Install PostgreSQL on Debian and Enable Remote Access

发表于 分类于

PostgreSQL is a powerful, open-source relational database widely used in production systems. This guide walks through installing PostgreSQL on Debian, configuring it to support remote connections.

This tutorial is suitable for:

  • Backend developers
  • Self-hosted servers / VPS
  • Hexo-based technical blogs

Tested on Debian 12 (Bookworm).

1. Install PostgreSQL on Debian

First, update your package index:

1
sudo apt update

Install PostgreSQL and the client tools:

1
sudo apt install -y postgresql postgresql-contrib

After installation, PostgreSQL should start automatically. Verify the status:

1
sudo systemctl status postgresql

2. Check PostgreSQL Version

Switch to the postgres system user:

1
sudo -i -u postgres

Check the installed version:

1
psql --version

Exit back to your normal user:

1
exit

3. Set Password for postgres User

By default, PostgreSQL uses peer authentication locally.
Set a password for the postgres database user:

1
sudo -u postgres psql

Inside the PostgreSQL shell:

1
ALTER USER postgres WITH PASSWORD 'strong_password_here';

Exit:

1
\q

4. Configure PostgreSQL to Listen on All Addresses

PostgreSQL only listens on localhost by default.
Edit the main configuration file using Vim:

1
sudo vim /etc/postgresql/*/main/postgresql.conf

Find the following line:

1
#listen_addresses = 'localhost'

Change it to:

1
listen_addresses = '*'

Save and exit Vim:

  • Press ESC
  • Type :wq
  • Press Enter

5. Allow Remote Connections (pg_hba.conf)

Edit the client authentication file:

1
sudo vim /etc/postgresql/*/main/pg_hba.conf

Add the following line at the end of the file:

1
2
# Allow IPv4 remote connections
host    all     all     0.0.0.0/0       scram-sha-256

(Optional) For IPv6:

1
host    all     all     ::/0            scram-sha-256

This allows password-based authentication from any IP.

⚠️ In production, restrict the IP range instead of using 0.0.0.0/0.

6. Restart PostgreSQL

Apply the configuration changes:

1
sudo systemctl restart postgresql

Verify PostgreSQL is listening on port 5432:

1
ss -lntp | grep 5432

You should see PostgreSQL listening on 0.0.0.0:5432.

7. Configure Firewall (If Enabled)

If you are using ufw, allow PostgreSQL traffic:

1
2
sudo ufw allow 5432/tcp
sudo ufw reload

Check status:

1
sudo ufw status

8. Test Remote Connection

From another machine:

1
psql -h your_server_ip -U postgres -p 5432

If successful, you should see the PostgreSQL prompt:

1
postgres=#

9. (Optional) Create a New Database and User

1
sudo -u postgres psql
1
2
3
CREATE DATABASE app_db;
CREATE USER app_user WITH PASSWORD 'app_password';
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

Security Notes

  • ❌ Do NOT expose PostgreSQL directly to the public internet without IP restrictions
  • ✅ Use strong passwords
  • ✅ Prefer VPN or private network access
  • ✅ Regularly update PostgreSQL

PostgreSQL is now ready for use in production or development environments.

Happy hacking 🚀