algrt.hm

PostgreSQL on FreeBSD

1 December 2019

I wanted to access PostgreSQL database running on FreeBSD over the LAN from macOS, and thought I would log this setup for reference, covering:

  • Installation of PostgreSQL on FreeBSD
  • Changing the password for the default postgres user
  • Installation of pgAdmin4 on FreeBSD
  • Allowing remote connections to PostgreSQL
  • Connecting remotely to PostgreSQL from macOS

For completeness, the following versions are referred to:
PostgreSQL 11.5
pgAdmin4 4.15
FreeBSD 12.0-RELEASE-p9
macOS Sierra 10.12.6

Installation of PostgreSQL on FreeBSD

Running pkg search 'PostgreSQL' | grep 'most advanced' will show what versions of PostgreSQL can be installed on FreeBSD. For me this provides the following output:

postgresql10-server-10.10      PostgreSQL is the most advanced open-source database available anywhere
postgresql11-server-11.5_1     PostgreSQL is the most advanced open-source database available anywhere
postgresql12-server-12.r1      PostgreSQL is the most advanced open-source database available anywhere
postgresql94-server-9.4.24     PostgreSQL is the most advanced open-source database available anywhere
postgresql95-server-9.5.19     PostgreSQL is the most advanced open-source database available anywhere
postgresql96-server-9.6.15     PostgreSQL is the most advanced open-source database available anywhere

In this case I went for 11.5 and installed by running the following as root:

  1. pkg install postgresql11-server-11.5_1 to install
  2. /usr/local/etc/rc.d/postgresql initdb to initialise the db
  3. echo 'postgresql_enable="YES"' >> /etc/rc.conf to start on startup
  4. /usr/local/etc/rc.d/postgresql start to start the daemon

Changing the password for the default postgres user

On installation, PostgreSQL creates one user with no password, called postgres. We should change this:

  1. Run psql postgres postgres and you will be greeted with the following prompt

    psql (11.5)
Type "help" for help.

postgres=#

  2. To change your password run \password postgres and put in the new one twice

Installation of pgAdmin4 on FreeBSD

  1. virtualenv-3.6 pgadmin4 to setup a new virtualenv
  2. source pgadmin4/bin/activate to activate our vitualenv
  3. pip install pyopenssl cryptography pyasn1 ndg-httpsclient will install some dependencies
  4. sudo pkg install py36-sqlite3 the binding for sqllite3, installed to the system
  5. To install pgAdmin4, first one should check to see what the latest pip-related release is at https://ftp.postgresql.org/pub/pgadmin/pgadmin4/ . Once a URL for the latest version for pip has been found this can be passed to pip with
    pip install 'https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.15/pip/pgadmin4-4.15-py2.py3-none-any.whl' and it will install, along with further dependencies
  6. The example config file can be copied with
    cp ./pgadmin4/lib/python3.6/site-packages/pgadmin4/config.py ./pgadmin4/lib/python3.6/site-packages/pgadmin4/config_local.py
  7. The example config should then be edited to have it bind to an IP address you can access outside of localhost with
    sudo vim ./pgadmin4/lib/python3.6/site-packages/pgadmin4/config_local.py or similar
    To do this we change DEFAULT_SERVER = ’127.0.0.1’ to DEFAULT_SERVER = ’0.0.0.0’
    Clearly, one should not do this on a machine with a publicly exposed IP address — the assumption here is that the environment is a safely firewalled LAN
  8. sudo python ./pgadmin4/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py will start pdAdmin4. Having put in an email and password to login with, the web interface is at http://192.168.x.x:5050/ where 192.168.x.x is the IP of your server
  9. If you are running pgAdmin4 on the same server as PostgreSQL, you can simply fill out localhost as the host, postgres as the user, and whatever the postgres password was changed to

Allowing remote connections to PostgreSQL

If we run e.g. pgcli -h 192.168.x.x (replace x.x as appropriate) we are greeted with the following error message:

could not connect to server: Connection refused
        Is the server running on host "192.168.x.x" and accepting
        TCP/IP connections on port 5432?

This is because by default PostgreSQL is not set up to allow remote connections. To allow this:

  1. Subnets that are allowed to send requests can be added to pg_hba.conf with sudo vim /var/db/postgres/data11/pg_hba.conf
    I added host all all 192.168.0.0/16 trust but you may want to go narrower on the subnet.
    https://www.dan.me.uk/ipsubnets is useful for looking up the CIDR notation.
    Again I would only suggest doing this on a completely locked down LAN.
  2. We also need to have PostgreSQL bind to an IP which is not the local loopback. To do this we can sudo vim /var/db/postgres/data11/postgresql.conf and change listen_addresses = ‘localhost’ to listen_addresses = ’*’
  3. sudo /usr/local/etc/rc.d/postgresql restart to restart

Connecting remotely to PostgreSQL from macOS

  1. On macOS, I wanted a quick PostgreSQL client so we can check our server is up. pgcli is ideal for this, and is installed conveniently with brew install pgcli

  2. The connection can be tested with pgcli -u postgres -h 192.168.x.x -d postgres (replace x.x as appropriate). This should yield the below prompt or similar:

    Server: PostgreSQL 11.5
Version: 2.1.1
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
postgres@192:postgres>