PostgreSQL

This page prepares PostgreSQL for myDBR. The driver is pgsql (see Requirements). The wizard can do the steps below for you when you give it the credentials of a PostgreSQL administrator and use Create User, see Quick Start. For the features of PostgreSQL in myDBR see PostgreSQL.

Install into a schema of your data database

A PostgreSQL connection is bound to one database, and a query cannot read the tables of another database. myDBR must read your data in its reports, so install myDBR into its own schema (here mydbr) of the database that holds your data, instead of creating a separate mydbr database. The myDBR objects stay apart from your tables, and the reports can read both.

In this example your data is in the database mydata and the role is mydbr:

1. Create the role:

$ psql -U postgres
postgres=# create role mydbr login password 'mydbr_password';
postgres=# \q

2. Create the schema, owned by the role. The role needs the CREATE privilege on the database, because the installation creates the citext extension:

$ psql -U postgres -d mydata
mydata=# grant create on database mydata to mydbr;
mydata=# create schema mydbr authorization mydbr;
mydata=# alter role mydbr in database mydata set search_path = mydbr, public;
mydata=# \q

3. Give the role read access to the data your reports use. In this example the data is in the schema public. Repeat for every schema you use:

$ psql -U postgres -d mydata
mydata=# grant usage on schema public to mydbr;
mydata=# grant select on all tables in schema public to mydbr;
mydata=# alter default privileges in schema public grant select on tables to mydbr;
mydata=# \q

4. Create the myDBR objects in the mydbr schema. The script is in the install/db_creation directory of the myDBR package:

$ cd install/db_creation
$ PGOPTIONS="-c search_path=mydbr,public" psql -U mydbr -d mydata -f mydbr_create_pgsql.sql

The script creates the citext extension (trusted since PostgreSQL 13, so a user with CREATE on the database can create it) and runs as one transaction, so a failed installation leaves nothing behind. Nothing is created in the public schema and your tables are not touched.

5. In the wizard enter mydata as the Database and mydbr as the Schema. The configuration then contains:

define( "DB_VENDOR", 'pgsql' );
define( "DB_HOST", 'localhost' );
define( "DB_PORT", 5432 );
define( "DB_NAME", 'mydata' );
define( "DB_SCHEMA", 'mydbr' );
define( "DB_USER", 'mydbr' );
define( "DB_PASSWORD", '********' );
define( "SETUP_DONE", true );

The search path

myDBR places the schema first in the search path of every connection (mydbr, public), so the reports can use your tables without a schema name. Qualify the tables of other schemas (sales.orders) or add them to the search path in user/defaults.php:

$mydbr_defaults['db_connection']['pgsql_init'] = ['SET search_path TO mydbr, sales, public'];

A separate database

note

A separate mydbr database also works if your reports only use data in that database, but the reports then cannot read any other database on the server. Use a schema in the data database unless you have a reason not to.

For a separate database, create it owned by the role (create database mydbr owner mydbr;), leave the Schema field empty, and run the script without PGOPTIONS.

SSL and socket

The wizard and the configuration support SSL (SSL_USE_SSL, SSL_KEY, SSL_CERTIFICATE, SSL_CA_CERT) and a Unix socket directory (DB_SOCKET). With a CA certificate the server certificate is verified (sslmode=verify-full). To test the connection from the web server use tools/troubleshoot/pgsql.php.