ubuntu pgadmin連接遠程服務器步驟

Prerequisites
Before starting, ensure you have:

• A remote PostgreSQL server with a valid IP address.
• A PostgreSQL user with remote connection permissions (e.g., postgres or a custom user).
• Basic familiarity with Ubuntu terminal commands.

Step 1: Install pgAdmin on Ubuntu

Update your package list and install pgAdmin4 using the following commands:

sudo apt update
sudo apt install pgadmin4

During installation, you’ll be prompted to set a master password for pgAdmin (required to access the web interface).

To launch pgAdmin, run:

pgadmin4

This will open the pgAdmin web interface in your default browser. Log in with the master password you set.

Step 2: Configure the Remote PostgreSQL Server for Remote Connections

2.1 Modify postgresql.conf to Allow Remote Connections

Edit the PostgreSQL configuration file to enable listening on all network interfaces:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

Replace <version> with your PostgreSQL version (e.g., 15 for PostgreSQL 15). Find the line:

#listen_addresses = 'localhost'

Uncomment it and change to:

listen_addresses = '*'

This allows PostgreSQL to accept connections from any IP address.

2.2 Update pg_hba.conf to Permit Remote Authentication

Edit the client authentication file:

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

Add the following line at the end of the file to allow password-based connections from any IP (adjust the IP range for security in production):

host    all             all             0.0.0.0/0               md5

Save and close both files.

2.3 Restart PostgreSQL to Apply Changes

sudo systemctl restart postgresql

2.4 Set a Password for the PostgreSQL User

If the postgres user (or your target user) doesn’t have a password, set one:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'your_strong_password';"

Replace your_strong_password with a secure password.

Step 3: Configure the Firewall on the Remote Server

Allow incoming traffic on PostgreSQL’s default port (5432) using ufw:

sudo ufw allow 5432/tcp
sudo ufw enable  # Enable firewall if not already active

Verify the rule is applied:

sudo ufw status

You should see 5432/tcp ALLOW in the output.

Step 4: Add a Remote Server Connection in pgAdmin

1. Open pgAdmin in your browser and log in.
2. In the left-hand panel, right-click the Servers node and select Create > Server….
3. In the General tab:
   • Enter a descriptive name for the server (e.g., Remote Production DB).
4. Switch to the Connection tab and fill in the remote server details:
   • Host name/address: The public IP address or hostname of the remote PostgreSQL server.
   • Port: 5432 (default; change if your server uses a different port).
   • Maintenance database: postgres (default system database).
   • Username: Your PostgreSQL username (e.g., postgres).
   • Password: The password you set for the user in Step 2.4.
5. Click Save to store the connection.

Step 5: Test the Connection

• Expand the newly created server entry in the Servers panel.
• Double-click the Databases node under the server.
• If the connection is successful, pgAdmin will display a list of databases on the remote server.
• Double-click a database (e.g., postgres) to open it and start querying.

Security Recommendations

• Restrict IP Access: In pg_hba.conf, replace 0.0.0.0/0 with your client’s specific IP range (e.g., 192.168.1.0/24) to limit connections.
• Use Strong Passwords: Ensure all PostgreSQL users have complex passwords.
• Enable SSL: Configure SSL encryption in postgresql.conf (set ssl = on) and provide certificates to secure data in transit.
• Update Regularly: Keep PostgreSQL and pgAdmin updated to patch security vulnerabilities.

By following these steps, you can securely connect pgAdmin on Ubuntu to a remote PostgreSQL server and manage your databases efficiently.