First Try...

As i move along the development and start using more and more this blog, i see the need to do some changes on my small development process/environment.

Up until now, i have been doing most of the small development in some of my machines, and kinda of duplicated PostgreSQL server installations, with this also different versions, and there has been a bit of back and forth with database backups from one machine, restoring it on another and together with the code development.

When comes to the code development that is more or less sorted as i am using version control and with a bit of discipline and a simple branch strategy i am able to correctly move between machines.

The part that was still a bit of challenge was the database to use, and the data in it. Of course there is a production database where this blog is built from, but i wanted to have also a database for development in the same server, so any of my machines could access it without having to move databases and data around and removing some of the needs for PostgreSQL local installations.

So i needed the following to be done in my home lab:

1. allow remote connections to PostgreSQL
2. create a copy of the prod database into a dev database

With a bit of searching on the web about the above this is what i have ended up doing for my setup.

On this post we will focus on the first point from the above list.

Allow remote connections fo PostgreSQL

For this we need to do two things, one we need to configure PostgreSQL server to accept connections not just from the localhost, but from other IPs and second we need to to tell PostgreSQL which IPs can will be permitted to authenticate from.

To configure the server to accept connections from other IPs, we need to modify the option listen_addresses that controls which IP addresses it will answer to. This option is present in the postgresql.conf file that is normally present in the data directory of the PostgreSQL installation, in my case under /var/db/postgres/data16:

# cd /var/db/postgres/data16
# vi postgresql.conf

And around line 60 you will find this:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

And it should be changed to look like this:

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

Save and quit from vi. The '*' will just allow to any IP address to connect, but we will control on more detail which IPs are allowed to authenticate.

The control of the access and which IPs the server will accept logins from is done via pg_hba.conf file that is also present in the same place as the postgresql.conf file:
# cd /var/db/postgres/data16
# vi pg_hba.conf

Scroll to the bottom of the file and you will find something like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust

The default configuration is as above, and where we are going to focus is the IPv4 local connections, which as defined by host all all 127.0.0.1/32 trust it is only allowing login from the localhost for all users and access to tall databases.
So we duplicate that line and change it to look like this> here is where we are going to

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.1.1/8            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust

As seen we have added a similar line, and added the local network, 192.168.1.x to be able to login for all users and access to all databases.
We have now just to save the changes and exit vi.

These are the changes needed to allow remote connections and remote logins, and as you seen, we have limited the access to only the local network.

The last thing we need is to restart the PostgreSQL server so the changes are applied.