Ok lets go...

These are notes on getting PHP & PostgresSQL up and running without any MySQL installation.

Last Updated: 25 June 2019

This was last tested with: FreeBSD (11.2), Perl (5.28), Apache (2.4), PHP (7.3) & PostgreSQL (10.8), I tend to update based on my own needs, which is when I do a new install / setup and will keep any updates and changes noted within text.

The FreeBSD Diary is a very useful site for various FreeBSD installation and configuration tips and I found their PostgreSQL installation notes particularly helpful.

NOTE: This material is update from original posting: "Installing PostgreSQL on FreeBSD"

Installing PHP with PDO and PostgreSQL from Ports

Generally my preference with FreeBSD is to install applications/packages via the Ports tree. This ensures you have current versions and allows you to control the configuration of your installs. Like using all convienient installation procedures their are lots of default options that have been selected and you might not end up with exactly what you want. A way of controlling this is to understand the dependencies amougst the packages and building the various dependent modules seperarately before you build the main application. This results in the application then building using the installed component and its configured dependents and so you can control how the overall application is created.

PHP is a good case in point, as it is dependent on a lot of sub-systems and libraries, including Apache. Historically the default build did an Apache 1.3 installation, rather than an Apache 2 installation. Apache 2 has many advantages, including SSL and latest fixes and enhancements.

By default PHP will also build with MYSQLND MySQL driver embedded, which just bloats the image if you use PostgreSQL as your preferred RDBMS. Irrespective of whether you use MySQL, PostgreSQL or some other database I would strongly recommend that you use PHP Data Objects (PDO) library as this allows you to isolate your PHP code from the particular RDBMS and so saves the headache of worrying about converting code later should you need to change the underlying database.

The following instructions provide details of the build order to create a PHP installation which uses the desired RDBMS configuration with PDO support and Apache version.

The key ports packages involved are:

  1. Perl
  2. SSL
  3. PostgresSQL Client or Server depending on whether you running local or remote DB
  4. Apache Portable Runtime (APR) with PostgresSQL support (I also include Berkeley DB, but make sure not to include MySQL ...)
  5. Apache 2
  6. PHP without MYSQLND
  7. PHP PDO Library
  8. PostgreSQL Crypto Library (which should be in /usr/local/pgsql/data/ )
  9. Optionally: Install PHP & phpPgAdmin to provide web interface for generall DB admin tasks.
1

SSL, Apache and PHP are all dependent on Perl, an early decision you must make is whether to create a "Threaded" Perl library. The need to create thread safe libraries will propogate across all your builds. As thread are now ubiquitous and there is really no penalty for creating thread safe version of libraries this should be a simple decsion. Build your PHP and Apache solution Thread Safe!

You can achieve this by choicing Thread safe perl as part of SSL build (item 2 following) or explicity create a thread safe Perl instance. Also if you need to have a particular version of Perl installed then this can be achieved by do explicit Perl Ports install.

The Perl 5 libaries are found in /usr/ports/lang/perl-*.

NOTE 1: Current ports have moved all the perl 5 ports to /usr/ports/lang/perl5.x where x == the desired version

NOTE 2: Perl 5 version used with FreeBSD 11.2 build was: 5.28.2

This first step in optional, based on needing a particular Perl version, otherwise just go to step 2

2

If you are going to store encrypted password data in your PostgreSQL database, the you will need to have the SSL libraries installed on the same machine as the RDBMS server, so build the SSL library before you start compiling the PostgreSQL server, as this will ensure you pick up the right crypto libraries.

3a

If are going to have the RDBMS running on the same machine as the Apache/PHP server then you should build the appropriate version of the PostgreSQL server. If you intend to run the RDBMS server on a seperate machine then you should build the build the PostgresSQL client only. To ensure that the client can connect to the remote RDBMS server you need to set the permissions correctly on the server machines pg_hba.conf file and that the postgresql.conf has the machine listening on both local host and public socket (listen_addresses = '*', as by default PostgreSQL will only listen on localhost). Both pg_hba.conf and postgresql.conf are in /usr/local/pgsql/data. Note that these configuration files are generated within the ../data directory that is created when running initdb.

The other configuration item that must be completed is to do su to pgsql and create a new "user account" that can be used by remote host. Depending on your application code, this user will require different permission levels.

3b

For client only PostgreSQL installation build the client library only. The ports directory to find PostgreSQL is /usr/ports/databases and the following the naming convention is used for the versions and types: postgresql<ver>-server and postgres<ver>-client.

For the client you can provide client connection configuration information within the file /usr/local/etc/pg_service.conf

4

Build Apache Portable Runtime is built with PostgreSQL support. For FreeBSD, you can build a PostgreSQL APR library by going into /usr/ports/devel/apr1 and building from there).

NOTE: The APR PostgreSQL library is loaded dynamically as a result of the directive: "DBDriver pgsql"within httpd.conf file (see below). To check that you have the right available APR library, look for /usr/local/lib/apr-util-1/apr_dbd_pgql.so .

5

Build Apache version (now at 2.4 for stable)

6

Build PHP and optionally disable the MYSQLND libary module

NOTE 1: By default PHP always built with MYSQLND library module. However if you are going to use only PostgreSQL and do not have MYSQL installed then the only way to remove the MYSQL is by editing the Makefile. The relevant line to look for and remove is: "--enable-mysqlnd \"
NOTE 2: With PHP 7.3 port this is no longer the case, finally PHP has been liberated from MySQL.. so manual fix is not needed, just ensure you do not select MySQL inclusion as part of build

7

Build various PDO modules...

To build the PostgreSQL PDO module, go to /usr/ports/databases/php-pdo_pgsql and do make and make install from there. If you want to build other PDO modules then find the equivalent database specific libraries in /usr/ports/databases

8

Build and install the PHP Extensions (php-extensions 1.0) and Apache PHP Module (mod_php v7.3)

9

Install PostgreSQL pgcrypto module into the database that is being used to hold the users information. This is not required if you are directly putting encrypted data values into the tables, but if you want to create some identity and registration funtionality on top of your PostgresSQL credential repository, then you will need this module.

Its installation is a multi-step process. I followed the process documented here, as it describes what is needed to build the library from the PostgresSQL server ports area. The only difference I took from this was due to using PostgreSQL version 9.0 and it mentions changing the Makefile to pickup the OpenSSL crypto library. If you have done step (2) above and installed the OpenSSL libraries in /usr/local/lib then you do not need to change the Makefile as the /usr/local libraries will get picked up automatically.

To use the pgcrypto module you need to then add the SQL functions into each database that needs them. The specific steps were:

# cd /usr/ports/databases/<postgresqlver-server>/work/<postgresql-ver>/contrib/pgcrypto
# gmake
# gmake install /* Installs: /usr/local/lib/postgresql/pgcrypto.so & /usr/local/share/postgresql/contrib/pgcrypto.sql */
# su pgsql
> psql <my-user-database> < /usr/local/share/postgresql/contrib/pgcrypto.sql

NOTE: With PostgreSQL 9.1 this has changed. Now you should continue to do the gmake & gmake install as per the outlined steps, but to install the module into your database you should go into the psql intepreter and enter: "CREATE EXTENSION pgcrypto;"

10

Before running PHP you should ensure you have setup the php.ini files. This is in /usr/local/etc and there are two versions: php.ini-prodution and php.ini-development. Copy the appropriate version to phi.ini


NOTE: With FreeBSD 11.2 the PostgresSQL installation and configuration is now consistent with the online PostgreSQL documentation.