Install and Allow Postgres in Google Cloud Instance to access from anywhere local machine

To install Postgres in Ubuntu you need a VM Instance on Compute Engine. Connect your VM Instance using the SSH option then follow the steps below.

Step 1:

First, you need to install the Postgres package and a -contrib package that adds some additional utilities and functionality.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Step 2:

Connect Postgres using Postgres user:

sudo -u postgres psql

Set a password for Postgres user:

\password postgres

Now for exit from PSQL prompt type \q.

Step 3: (Optional)

You can create new user apart from postgres user. This is a good approach to use different users for different projects.

sudo -u postgres createuser --interactive

Step 4:

Create a database for your project.

sudo -u postgres createdb devstudio

devstudio is the name of database.

Step 5:

Allow postgres to access from anywhere:

Edit pg_hba.conf file:

sudo nano /etc/postgresql/10/main/pg_hba.conf

10 is the version of postgres. Change it according to your postgres version.

Now scroll down to the bottom of the file and add the following lines:

# IPv4 remote connections for the demo:
host    all             all           0.0.0.0/0         md5

In nano, Press Control X.

Edit postgresql.conf file:

sudo nano /etc/postgresql/10/main/postgresql.conf

10 is the version of postgres. Change it according to your postgres version.

Scroll down to the line that begins with #listen_addresses = 'localhost'.

Delete the # character to uncomment the line.

Replace localhost with *:

listen_addresses = '*'

Restart the database service. In the SSH terminal, enter:

sudo service postgresql restart

Step 6:

PostgreSQL accepts remote connections on port 5432. Follow these steps to add a firewall rule that enables traffic on this port.

In the Cloud Console, navigate to the Create a firewall rule page.

Create a firewall rule

In the Network field, leave the network as default.

In the Name field, enter:

postgres-devstudio

In the Direction of traffic field, create a rule as Ingress and another rule as Egress.

In Source IP Ranges, enter the IP address 0.0.0.0/0

In Allowed protocols and ports, enter:

tcp:5432

Click Create.

Note that firewall rules are a global resource, so you'll only need to create this rule once for all instances.

Step 7: 

Now connect postgres using pgAdmin.