How to connect DataGrip to postgres on google compute engine

Question

I've installed Postgres on Google Compute engine. I can create users, schemas and tables. I can connect to the Google Compute engine with PyCharm, remote configuration. But I can't connect DataGrip to the Postgres.

It looks like the Posgres port is open (port 5432):

...@instance-2:~$ netstat -ntpl
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::22                   :::*                    LISTEN      -                   
tcp6       0      0 ::1:5432                :::*                    LISTEN      -        

It looks like the connection is getting blocked. when trying telnet

telnet <GCP Compute engine IP> 5432 

was not successful

tried using "SSH Tunnel" in DataGrip. while the SSH Tunnel connects well, I still can't connect to the Postgres

Can someone advice how to make sure that the google cloud settings do not block connection or advice how to properly set the connection? Thanks

There was a suggestion to edit the "pg_hba.conf". From what I understand I need to add
my specific IP. Would I need to change it every time I change working location?
Other suggestions?

Answer 1

You PostgreSQL database access is controlled by the pg_hba.conf you mentioned and postgresql.conf files.

You're able to SSH into your VM because it listens on a port 22 and accepts connections from avery IP:

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

For the moment it looks like you have a default configuration that allows only connections from localhost (127.0.0.1).

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN

For the server to accept connections to your DB from every IP find a postgresql.conf file and change a line from listen_addresses = 'localhost' to listen_addresses = '*'.

Now restart the service sudo service postgresql reload and check if your server accepts connections from all IP's:

$ netstat -nlt
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN

We see that the port 5432 is open for any IP's.

Next edit your pg_hba.conf file and add this at the end:

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

Now restart postgresql server (to be sure the changes will be taken into account) and try to connect;

$ psql -h your-postgres-server-ip -U user
Password for user user:
psql (9.4.1, server 9.4.5)
Type "help" for help.

postgres=# \l

However this poses some security risk due to exposing your DB to the internet and allowing connections from any IP. So - if you know the IP's (or ranges) from which you will be connecting then you can put them in `pg_hba.conf'.

You may also have a look at the documentation describing pg_hba.conf structure here and how to configure PostgreSQL's incomming connections.

And - if you're super paranoid then you can change the IP in the pg_hba.conf every time you change your location/ip to connect to it.