PostgreSQL 15 and pgAdmin4 on FreeBSD

Tested on FreeBSD 13.2. This article is also mirrored on the FreeBSD Wiki.

PostgreSQL

Install PostgreSQL:

# pkg install postgresql15-server postgresql15-client

Enable the PostgreSQL service:

# sysrc postgresql_enable="YES"

Initialize PostgreSQL and start the service:

# /usr/local/etc/rc.d/postgresql initdb
# service postgresql start

Make sure the service is running properly on IPv4 and IPv6 port 5432:

$ sockstat -46 | grep 5432

Set a password for the default postgres user:

# passwd postgres

Create an owner user for your database:

# su - postgres
$ createuser admin
$ createdb foo_db -O admin

Inside the psql prompt, set an encrypted password for admin and grant exclusive access to foo_db to the admin user:

$ psql foo_db
foo_db=# alter role admin with encrypted password 'yourpassword';
foo_db=# grant all privileges on database foo_db to admin;
foo_db=# exit
$ exit

Make PostgreSQL listen to all addresses, instead of just localhost.

# vi /var/db/postgres/data15/postgresql.conf

Uncomment the listen_addresses line and set it to:

listen_addresses = '*'

Change the security settings:

# vi /var/db/postgres/data15/pg_hba.conf

Go to the bottom of the file and set trust to md5 for all existing entries:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Also, allow remote IPv4 and IPv6 connections to our new database as admin only:

# Allow remote connections to foo_db as admin
host    foo_db          admin           0.0.0.0/0               md5
host    foo_db          admin           ::/0                    md5

Restart the service:

# serivce postgresql restart

pgAdmin4

Install the needed packages:

# pkg install python py39-pip py39-virtualenv py39-sqlite3 ca_root_nss openjpeg rust

Upgrade pip to the latest version:

$ pip install --upgrade pip

Initialize the virtualenv for pgAdmin4 in your home directory:

$ cd
$ virtualenv pgadmin4

Enter the virtualenv and install pgAdmin4. This will take a while:

$ . pgadmin4/bin/activate
(pgadmin4) $ pip install pgadmin4

Create a local config file:

(pgadmin4) # mkdir -p /var/lib/pgadmin /var/log/pgadmin
(pgadmin4) $ cp pgadmin4/lib/python3.9/site-packages/pgadmin4/config.py pgadmin4/lib/python3.9/site-packages/pgadmin4/config_local.py
(pgadmin4) $ vi pgadmin4/lib/python3.9/site-packages/pgadmin4/config_local.py

Inside config_local.py, edit the following values:

DEFAULT_SERVER = '0.0.0.0'
DEFAULT_SERVER_PORT = 5050

Start pgAdmin4 (always inside the virtualenv) and set up your account:

(pgadmin4) # pgadmin4
Email address: you@mail.com
Password: yourpassword
Retype password: yourpassword

Open a web browser and enter pgAdmin4 (replace the IP with the appropriate one) and log in with the email and password you just entered in the pgadmin4 prompt:

http://192.168.1.11:5050

Inside the web interface, go to File -> Preferences -> Binary Paths and under the PostgreSQL Binary Path section, check PostgreSQL 15 and set the binary path to /usr/local/bin/. Then click Save.

From now on, you can run pgadmin4 in the background like so:

(pgadmin4) # pgadmin4 &

Upgrading pgAdmin4

To upgrade pgAdmin4, you only have to delete and reinstall the virtualenv:

$ cd
$ rm -rf pgadmin4
$ virtualenv pgadmin4
$ . pgadmin4/bin/activate
(pgadmin4) $ pip install --upgrade pgadmin4

You also have to re-create config_local.py and set DEFAULT_SERVER (see above).