1

I'm trying to set up a remotely accessible Postgres database. I want to host this databse on one Linux based device (HOST), and to access it on another Linux based device (CLIENT).

In my specific case, HOST is a desktop device running Ubuntu. CLIENT is a Chromebook with a Linux virtual system. (I know. But it's the closest thing to a Linux based device that I have to hand.

Steps Already Taken to Set Up the Database

  1. Installed the required software on HOST using APT.
PGP_KEY_URL="https://www.postgresql.org/media/keys/ACCC4CF8.asc"
POSTGRES_URL_STEM="http://apt.postgresql.org/pub/repos/apt/"
POSTGRES_URL="$POSTGRES_URL_STEM `lsb_release -cs`-pgdg main"
POSTGRES_VERSION="12"
PGADMIN_URL_SHORT="https://www.pgadmin.org/static/packages_pgadmin_org.pub"
PGADMIN_URL_STEM="https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt"
PGADMIN_TO_ECHO="deb $PGADMIN_URL_STEM/`lsb_release -cs` pgadmin4 main"
PGADMIN_PATH="/etc/apt/sources.list.d/pgadmin4.list"

sudo apt install curl --yes
sudo apt install gnupg2 --yes
wget --quiet -O - $PGP_KEY_URL | sudo apt-key add -

echo "deb $POSTGRES_URL" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt install postgresql-$POSTGRES_VERSION --yes
sudo apt install postgresql-client-$POSTGRES_VERSION --yes

sudo curl $PGADMIN_URL_SHORT | sudo apt-key add
sudo sh -c "echo \"$PGADMIN_TO_ECHO\" > $PGADMIN_PATH && apt update"
sudo apt update
sudo apt install pgadmin4 --yes
  1. Create a new Postgres user.
NU_USERNAME="my_user"
NU_PASSWORD="guest"
NU_QUERY="CREATE USER $NU_USERNAME WITH superuser password '$NU_PASSWORD';"

sudo -u postgres psql -c "$NU_QUERY"
  1. Created the new server and database. I did this manually, using the PGAdmin GUI.
  2. Added test data, a table with a couple of records. I did this with a script.
  3. Followed the steps given in this answer to make the databse remotely accessible.

Steps Already Taken to Connect to the Database REMOTELY

  1. Installed PGAdmin on CLIENT.
  2. Attempted to connect using PGAdmin. I used the "New Server" wizard, and entered:

Host IP Address: 192.168.1.255
Port: 5432 (same as when I set up the database on HOST)
User: my_user
Password: guest

However, when I try to save the connection, PGAdmin responds after a few seconds saying that the connection has timed out.

Tom Hosker
  • 526
  • 2
  • 17

1 Answers1

1

You have to configure listen_addresses in /var/lib/pgsql/data/postgresql.conf like this:

listen_addresses = '*'

Next make sure your firewall doesn't block the connection by checking if telnet can connect to your server:

$ telnet 192.168.1.255 5432
Connected to 192.168.1.255.
Escape character is '^]'.

If you see Connected network connectivity is ok. Next you have to configure access rights for remote hosts.

leo
  • 387
  • 1
  • 6
  • Thank you for alerting me to the `telnet` trick. I can get a connection at 192.168.1.159 5432 - that must be the address I want. However, when I try to connect in PGAdmin, I get a message beginning: 'No pg_hba.conf entry for host 192.168.1.208...' But I have already amended my pg_hba.conf, on both HOST and CLIENT, to include the line: `host all all 0.0.0.0/0 md5`! – Tom Hosker Jul 18 '22 at 16:29
  • 1
    Your error was "timed out". Authentication is the next part of your quest but I believe it's out of this question. did you see this manual https://www.netiq.com/documentation/cloud-manager-2-5/ncm-install/data/bf9b2zv.html? – leo Jul 19 '22 at 10:37
  • Thanks again for helping me with this. I actually decided that this question was inappropriate Stack Overflow, so I asked [a new question on Server Fault](https://serverfault.com/questions/1105921/why-does-postgres-tell-me-no-pg-hba-conf-entry-for-host-when-i-have-already) instead. This new question includes an updated summary of the current situation. – Tom Hosker Jul 19 '22 at 11:40