How do I connect to postgres from a client machine?

December 9, 2017 25.5k 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 -
  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 and and I changed my /etc/postgresql/9.5/main/pg_hba.conf to host all all 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.”

6 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.

  • might this work?

    ssh sammy@hostname “psql -Uw username -d database”

    or this

    psql -h hostname -Uw username -d database

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
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.

One more interested party here.

So far I had success with:

  1. ssh sammy@ipaddress createdb sammydb
  2. ssh sammy@ipaddress createuser sammydbuser

But not with:

  1. ssh sammy@ipaddress psql -c “alter user ‘sammydbuser’ with encrypted password 'some_string’;”
  2. ssh sammy@ipaddress psql -c “grant all privileges on database 'sammydb’ to 'sammydbuser’;”

Sharing any thoughts would be greatly appreciated …

Oh, I found a solution to the above accidentally - it is related to the escaping characters:

this worked (at least in my case):

ssh sammy@ipaddress “psql -c "alter user ‘sammydbuser’ with encrypted password 'some_string’;"”
ssh sammy@ipaddress “psql -c "grant all privileges on database 'sammydb’ to 'sammydbuser’;"”

note: the quote goes before psql command, then after the switch -c, you have to quote the actual command, but with an escape character. same applies to closing the quotes …

I sincerely hope this might help someone else …

Have another answer? Share your knowledge.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!