How do I connect to postgres from a client machine?

December 9, 2017 2.8k views
PostgreSQL Ubuntu 16.04

This is an extremely straightforward question. "How do I connect to Postgres DB installed on a digitalocean droplet, from an external client via command-line or via third party apps like PSequel (available for MacOS) or PGAdmin?". I am surprised that there is no direct tutorial to explain this in plain simple words. Here is what I did so far:

  1. Created a brand new spick and span droplet running Ubuntu 16.04.
  2. Installed postgres following this tutorial - https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04
  3. Now I have Ubuntu user "sammy", postgres user "sammy" and postgres database "sammy". I am happily able to connect to DB from within the box.
  4. All that is required now is to connect to this database, from external machine, which for some reason is a herculean task.
  5. I followed https://www.digitalocean.com/community/questions/remote-connect-to-postgresql-with-pgadmin and https://www.digitalocean.com/community/questions/how-to-connect-remotely-to-the-postgres-db-on-the-server-from-the-local-client-machine and https://askubuntu.com/questions/423165/remotely-access-postgresql-database. I changed my /etc/postgresql/9.5/main/pg_hba.conf to host all all 0.0.0.0/0 md5 and in /etc/postgresql/9.5/main/postgresql.conf added listen_addresses='*'
  6. Restarted postgres on remote server via sudo service postgresql restart
  7. [On my local Mac laptop] psql -h <IPv4 address> -p 5432 -U sammy -d sammy. However I get the error psql: FATAL: password authentication failed for user "sammy"

How do I connect to postgres server? I would highly recommend digitalocean's moderators to write a detailed but simple blog on "Connecting to Postgres from external client, along with best practises."

4 Answers

Is there any reason why you wouldn't just SSH into the droplet and run the psql command locally as the postgresql user? It seems like that would be a lot more straightforward and you wouldn't need to have postgresql remotely accessible. Making it remotely accessible doesn't seem to be the best idea from a security standpoint.

  • Several reasons.

    1. I need to give DB access to non-technical people, who just dont understand ssh or cannot work on command line. But they can use apps like PSequel or PGAdmin which have a GUI interface. Therefore we cannot just ssh into the remote machine everytime to access data.
    2. I need to wire up the database to visualization tools like Google DataStudio or Periscope which can create fancy dashboards with visual graphs and pie charts. They need a database "connection URI" of the format username:password@hostname:port/database
    3. I do not want to give complete ssh access to people, because they can mess around with server and files. (I understand that I can limit user access to certain directories. But then it is more time consuming for me to create a server profile for every user and then fence it.)
    4. I need a simple solution to access data, with fewer keystrokes.

If you really want to streamline it, what I would do would be to create an alias or a script on my local machine to run all the commands on my remote machine after SSH-ing in.

i.e. ssh sammy@ipaddress 'psql' or something similar to that.

Well...
For desktop GUI Apps it's easy to setup user group and give ssh access
But for Google DataStudio you must provide database a access from outside.
It mean you need to setup PSQL to listen on 0.0.0.0
In my opinion that is worse than give limited SSH access to non-technical people that you know.
Possible solution is:
Setup limited SSH to provide access for desktop Apps
Use google docs to store Excel data from your server

Is there an answer to this yet? I am also attempting to do the same. remote connection should be possible.

Have another answer? Share your knowledge.