PostgreSQL is one of the world’s most advanced open-source relational database systems. Since its first release, it has continued to evolve and each and every time, it becomes better in almost every aspect. The latest version as of today, is PostgreSQL 16.2 and with it’s release, database administrators and developers have every right to look forward to a bunch of new features and improvements. PostgreSQL offers complex data types, and supports Java, Python, Perl, Ruby, Go, C, C#, and many more languages and has additional options for distribution of the workloads. In addition, database admins and users can come up with new approaches to extract valuable insights and improve their data management strategies.

Install and Configure PostgreSQL 17 on FreeBSD 14

In this guide, I will show you how to install PostgreSQL 17 on FreeBSD 14, for the best experience and performance you can get. Let’s dive in right away.

Step 1. Install PostgreSQL 17 on FreeBSD 14

On FreeBSD, you can easily install PostgreSQL via package index or repository. As of today, the FreeBSD 16 repository provides multiple versions of the PostgreSQL server, including PostgreSQL 17, 16, and 15.

To get started, you need to update update your system package index:

# pkg update

Now run the command below with y to install PostgreSQL 16:

## Installing PostgreSQL 17
# pkg install postgresql17-server postgresql17-client postgresql17-contrib

Sample Output:

Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 11 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	icu: 74.2_1,1
	libedit: 3.1.20240808,1
	liblz4: 1.10.0,1
	libxml2: 2.11.9
	llvm15: 15.0.7_10
	lua53: 5.3.6_1
	perl5: 5.36.3_2
	postgresql17-client: 17.2
	postgresql17-contrib: 17.2
	postgresql17-server: 17.2
	zstd: 1.5.6

Number of packages to be installed: 11

The process will require 1 GiB more space.
204 MiB to be downloaded.

Proceed with this action? [y/N]: Y

Once both packages are installed, enable the PostgreSQL service to start at system reboot:

# sysrc postgresql_enable="YES"

Next, initialize the PostgreSQL database with the following command:

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

Sample Output:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
  locale provider:   libc
  LC_COLLATE:  C
  LC_CTYPE:    C.UTF-8
  LC_MESSAGES: C.UTF-8
  LC_MONETARY: C.UTF-8
  LC_NUMERIC:  C.UTF-8
  LC_TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Africa/Asmara
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data17 -l logfile start

After you’ve initialized the PostgreSQL database, start the PostgreSQL service and then check it’s status:

# service postgresql start
# service postgresql status

By default, PostgreSQL listens on port 5432. Run the command below to check:

# sockstat -4 | grep 5432

Step 2. Configuring PostgreSQL 17

After installing PostgreSQL 17 on FreeBSD, you need to do some configuration before diving in to use it. First, you have to set up password authentication for the default PostgreSQL user and then configure the general PostgreSQL authentication methods.

Setting Up Password for PostgreSQL User

Setting up passwords for user postgres is important to prevent unauthorized access to your database. First, run the command below to access the psql or PostgreSQL interactive shell:

# su -m postgres -c psql

Once connected, your prompt will become such as postgres=>, indicating that you are connected to the database postgres. Now run the ALTER ROLE query below to create a password for the default user postgres.

postgre=> ALTER ROLE postgres WITH PASSWORD '****************';

Ensure to input your password in place of ************* in the above query.

Then run the following queries to check available users in PostgreSQL. You’ll be presented with the default postgres user in your PostgreSQL server.

# display list users
\du
# display list users from pg_user
SELECT * FROM pg_user;
quit

Sample Output:

postgres=# \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
(1 row)

postgres=# quit
could not save history to file "/root/.psql_history": Permission denied

Setting Up Password Authentication in PostgreSQL

After you’ve configured the password for the default postgres user, let’s enable and verify password authentication in your PostgreSQL server. Open the default PostgreSQL configuration file, /var/db/postgres/data17/postgresql.conf using nano and uncomment the password_encryption = scram-sha-256 parameter to enable password authentication via scram-sha-256.

# vim /var/db/postgres/data17/postgresql.conf
##uncomment the line
password_encryption = scram-sha-256

Save and file and exit.

Now open the configuration file, /var/db/postgres/data17/pg_hba.conf using your favorite editor, then change the default authentication for localhost or 127.0.0.1 to scram-sha-256.

# nano /var/db/postgres/data17/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Save the file and exit when done. Restart the postgresql service to apply the changes.

# service postgresql restart

Run the following command to log in to PostgreSQL via user postgres. Enter your password when prompted:

# su -m postgres -c "psql -h localhost -U postgres"

Once logged in, execute the \conninfo query below to verify your connection status to PostgreSQL.

\conninfo

Sample Output:

root@freebsd14:~ # su -m postgres -c "psql -h localhost -U postgres"
Password for user postgres:
psql (17.2)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
postgres=#

Step 3. Basic usage of PostgreSQL

After setting up the necessary authentication methods for the default PostgreSQL user, you can go ahead and create new users and connect t the database then start your operations.

Creating a User in PostgreSQL 16

Before proceeding, ensure you’re still in the psql interactive shell. Run the CREATE ROLE query below to create a new user in PostgreSQL. For example, we’ll create a new user techview with the “Password@01”.

postgres=> CREATE ROLE cloudspinx LOGIN PASSWORD 'Password@01';

Now run the following query to permit user techview to create a database with the CREATEDB attribute.

postgres=> ALTER ROLE cloudspinx WITH CREATEDB;

Next, run the \du query below to verify the list of users on PostgreSQL, then exit the psql.

postgres=> \du
postgres=> quit

Sample Output:

postgres=# CREATE ROLE cloudspinx LOGIN PASSWORD 'Password@01';
CREATE ROLE
postgres=# ALTER ROLE cloudspinx WITH CREATEDB;
ALTER ROLE
postgres=# \du
                              List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 cloudspinx | Create DB
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# quit
could not save history to file "/root/.psql_history": Permission denied

Now, connect to the PostgreSQL as the new user techview using the command below, input your password when prompted:

# su -m postgres -c "psql -h localhost -d postgres -U cloudspinx"

Verify your connection with the query below:

postgres=> \conninfo

Sample Output:

root@freebsd14:~ # su -m postgres -c "psql -h localhost -d postgres -U cloudspinx"
Password for user cloudspinx:
psql (17.2)
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "cloudspinx" on host "localhost" (address "127.0.0.1") at port "5432".
postgres=>

Creating Database in PostgreSQL

Now that you’ve created a PostgreSQL user, let’s create new databases in the PostgreSQL server. Run the CREATE DATABASE query below to create new databases testdb and appdb in PostgreSQL.

postgres=> CREATE DATABASE testdb WITH OWNER cloudspinx;
postgres=> CREATE DATABASE appdb WITH OWNER cloudspinx;
postgres=> \l
postgres=> quit

Next, connect to the PostgreSQL server as user techview to the specific database testdb. Input the password for user techview when asked.

# su -m postgres -c "psql -h localhost -d testdb -U cloudspinx"

Verify your connection:

testdb=> \conninfo

Sample Output:

root@freebsd14:~ # su -m postgres -c "psql -h localhost -d testdb -U cloudspinx"
Password for user cloudspinx:
psql (17.2)
Type "help" for help.

testdb=> \conninfo
You are connected to database "testdb" as user "cloudspinx" on host "localhost" (address "127.0.0.1") at port "5432".
testdb=>

To switch between databases in PostgreSQL, you can use the \c DBNAME or \connect DBNAME query.

# connect to database appdb
\connect appdb
# or use this shorter query
\c appdb

Creating Table in PostgreSQL

To create a table in PostgreSQL, use the CREATE TABLE query. For example, you will create a new table writers with columns id, name, age, and blog.

testdb=> CREATE TABLE writers(
ID INT PRIMARY KEY     NOT NULL,
name           TEXT    NOT NULL,
age            INT     NOT NULL,
blog          TEXT    NOT NULL
);

Now run the \d or \d TABLENAME query to show the list columns in your table.

testdb=> \d writers

Sample Output:

testdb=> CREATE TABLE writers(
ID INT PRIMARY KEY     NOT NULL,
name           TEXT    NOT NULL,
age            INT     NOT NULL,
blog          TEXT    NOT NULL
);
CREATE TABLE
testdb=> \d writers
              Table "public.writers"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           | not null |
 age    | integer |           | not null |
 blog   | text    |           | not null |
Indexes:
    "writers_pkey" PRIMARY KEY, btree (id)

testdb=>

Insert and Retrieve Data in PostgreSQL

To insert new data into tables, use the INSERT query. For example, to add new data to the table writers:

testdb=> INSERT INTO writers(id, name, age, blog)
VALUES (1, 'gedion', 22, 'cloudspinx.com'), (2, 'chiloba', 24, 'daylifetips.com');

After adding new data, run the SELECT query below to retrieve your data from table writers.

testdb=> SELECT * FROM writers;
testdb=> SELECT name, blog FROM writers;

Sample Output:

testdb=> INSERT INTO writers(id, name, age, blog)
VALUES (1, 'gedion', 22, 'cloudspinx.com'), (2, 'chiloba', 24, 'daylifetips.com');
INSERT 0 2
testdb=> SELECT * FROM writers;
 id |  name   | age |      blog
----+---------+-----+-----------------
  1 | gedion  |  22 | cloudspinx.com
  2 | chiloba |  24 | daylifetips.com
(2 rows)

testdb=> SELECT name, blog FROM writers;
  name   |      blog
---------+-----------------
 gedion  | cloudspinx.com
 chiloba | daylifetips.com
(2 rows)

testdb=>

Update and Delete Data in PostgreSQL

To update data in table, use the UPDATE query together with the WHERE filter to specify the target data to a specific column. For example:

testdb=> UPDATE writers 
SET age = 24 
WHERE name = 'gedion';

Now check for the changes:

testdb=> SELECT * FROM writers WHERE name = 'gedion';

Sample Output:

testdb=> UPDATE writers
SET age = 24
WHERE name = 'gedion';
UPDATE 1
testdb=> SELECT * FROM writers WHERE name = 'gedion';
 id |  name  | age |      blog
----+--------+-----+----------------
  1 | gedion |  24 | cloudspinx.com
(1 row)

testdb=>

To delete data from a table, run the DELETE FROM query:

testdb=> DELETE FROM writers
WHERE id = 2;
testdb=> SELECT * FROM writers;

Sample Output:

testdb=> DELETE FROM writers
WHERE id = 2;
DELETE 1
testdb=> SELECT * FROM writers;
 id |  name  | age |      blog
----+--------+-----+----------------
  1 | gedion |  24 | cloudspinx.com
(1 row)

testdb=>

Step 4. Enabling Remote Access to PostgreSQL

To enable remote access to PostgreSQL, you need to edit the listen address in the PostgreSQL configuration file and also edit the pg_hba.conf to allow connections from a client device.

Open the /var/db/postgres/data17/postgresql.conf file and look for the listen_addresses parameter and set it to '*' to listen on all available network interfaces.

# sudo vim /var/db/postgres/data17/postgresql.conf
# line 60 : uncomment and change
listen_addresses = '*'

The pg_hba.conf file controls client authentication. You’ll need to add an entry to allow connections from your client device.

# sudo vim /var/db/postgres/data17/pg_hba.conf
## add to last line
## specify network range you allow to connect on [ADDRESS] section
## if allow all, specify [0.0.0.0/0]
host    all             all             192.168.1.0/24             scram-sha-256

Now restart the postgresql service.

# service postgresql restart

On your client device, use psql to connect to the remote PostgreSQL server. Specify the host, port, username, and database name.

# psql -h <server_ip> -p <port> -U <username> -d <database_name>
# psql -h 192.168.1.179 -p 5432 -d testdb -U cloudspinx

Check the connection information:

testdb=> \conninfo

Sample Output:

Step 5. PostgreSQL over SSL/TLS

You can generate SSL/TLS certificates using tools like OpenSSL. For example:

# mkdir -p /usr/local/etc/ssl/
# cd /usr/local/etc/ssl/
# openssl genrsa -aes128 2048 > server.key
# openssl rsa -in server.key -out server.key
# openssl req -utf8 -new -key server.key -out server.csr
# openssl x509 -in server.csr -out server.crt -req -signkey server.key -days 3650
# chmod 600 server.key
# ls -l server.*

Sample Output:

Now run the following commands:

# cp /usr/local/etc/ssl/server.* /var/db/postgres/data17/
# chown postgres:postgres /var/db/postgres/data17/server.*
# chmod 600 /var/db/postgres/data17/server.*
# vim /var/db/postgres/data17/postgresql.conf
## line 108 : uncomment and change
ssl = on
# #line 110, 113 : uncomment and change to your own certs
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Update the pg_hba.conf file to require SSL/TLS connections for specific users or databases.

# vim /var/db/postgres/data17/pg_hba.conf
## add to last line
## [hostssl] ⇒ use TCP/IP connection only when enabling SSL/TLS
## [10.0.0.0/24] ⇒ allowed network to connect
## [scram-sha-256] ⇒ use SCRAM-SHA-256 password method
hostssl         all             all             192.168.1.0/24             scram-sha-256

After making these changes, restart the PostgreSQL server to apply the new SSL/TLS settings:

# service postgresql restart

On the client run the command below:

# psql -h 192.168.1.179 -p 5432 -d testdb -U cloudspinx
# on SSL/TLS connection, messages [SSL connection ***] is shown

Sample output from a macOS client:

Conclusion

This guide has been all about installing PostgreSQL 17 on FreeBSD 14 and we have done that successfully and enabled password authentication. We also managed to configure a password for the default postgres user. The basic database operations have also been covered, though not in detail but just enough. If you’ve come this far, then your installation is up and ready to go.

Check out other articles:

• How To Install PostgreSQL 17 on Debian 12 / Debian 11
• Run PostgreSQL 16,15,14,13 in Podman with pgAdmin
• How To Install PostgreSQL 16 on CentOS 9 / RHEL 9
• Configure Custom PostgreSQL ports with SELinux