Combined Metadata and Query Engine

Audience: System Administrators

Content Summary: In some instances it makes sense to run the Metadata Database and Query Engine on a single PostgreSQL instance. This page details the installation and initialization of the required packages and the initialization of the Metadata Database.

Environment Setup

The following environment variables will be used throughout the installation of combined databases.

IMMUTA_METADATA_PASSWORD: Password for the Immuta bometadata user.
IMMUTA_FEATURE_PASSWORD: Password for the Immuta feature_service user.
IMMUTA_BASE_URL: Base URL for the Immuta Web Service. See determining the Immuta base URL.
PATH_TO_CA: Path to the CA certificate.
PATH_TO_PRIVATE_KEY: Path to the private key that will be used for TLS.
PATH_TO_CERT: Path to the certificate that will be used for TLS.

Export these as environment variables before beginning the setup.

export PATH_TO_CA=/etc/pki/tls/certs/ca-bundle.crt
export PATH_TO_PRIVATE_KEY=/etc/immuta/tls/immuta.key
export PATH_TO_CERT=/etc/immuta/tls/immuta.crt

Before proceeding, ensure the following:

You should have exported the database passwords according to secure password generation.
You should have exported the Immuta base URL according to determining the Immuta base URL.
If the CA certificate used to sign the Immuta Web Service nodes is not located in the system CA bundle (/etc/pki/tls/certs/ca-bundle.crt), then export the path to the custom CA certificate location.
The PATH_TO_PRIVATE_KEY and PATH_TO_CERT shown are the defaults used by the instructions provided in tls-certificates. Please update paths as appropriate.

Package Installation

Install the Immuta security extension. It has dependencies that will also install PostgreSQL. Immuta provided RPM's for PostgreSQL must be used, and PostgreSQL must not be pre-installed on the system.

Note: The following command must be run as root.

yum install immuta-query-engine

Setup and Configuration

First, the PostgreSQL database must be initialized, and the Immuta database tables must be created.

Note: The following commands must be run as immutaqe. Be sure to export all of the required variables in the immutaqe user's shell.

pg_ctl init -o'-U postgres -E UTF8 --locale=en_US.utf8'
 pg_ctl start -w -D"${PGDATA}" \
    -o "-c listen_addresses='localhost' -c allow_system_table_mods=on"
init_db.sh -r "immuta,bometadata" -p env
pg_ctl stop -w -m fast

cat <<EOF >> "${PGDATA}/postgresql.conf"
listen_addresses = '*'
port = 5432
ssl = 'on'
max_connections = 250
shared_preload_libraries = 'immuta_sql_fdw'
lock_timeout = 5000
immuta_security.user_auth_endpoint = '${IMMUTA_BASE_URL}'
immuta_security.ca_file = '${PATH_TO_CA}'
immuta_security.ssl_peer_verification = true
immuta_security.ssl_host_verification = true
immuta_fdw.show_remote_sql_explain = false
immuta_fdw.advanced_pushdown = true
immuta_fdw.window_pushdown = true
immuta_fdw.driver_iseriesdb2_max_page_queries = 0
immuta_fdw.driver_mariadb_window_pushdown = false
immuta_fdw.driver_mysql_window_pushdown = false
immuta_fdw.driver_sybase_ase_window_pushdown = false
immuta_fdw.driver_kdb_window_pushdown = false
immuta_fdw.driver_kdb_advanced_pushdown = false
immuta_fdw.driver_kdb_max_page_queries = 0
immuta_fdw.driver_postgresql_connection_sharing = true
immuta_fdw.driver_elastic_window_pushdown = false
immuta_fdw.driver_elastic_max_page_queries = 0
immuta_fdw.driver_postgresql_connection_sharing = true
immuta_fdw.driver_staging_directory = '/var/lib/immutaqe/odbc/install/'
immuta_fdw.driver_install_directory = '/var/lib/immutaqe/odbc/drivers/'
EOF

cat <<EOF > "${PGDATA}/pg_hba.conf"
local all postgres trust
host bometadata bometa 0.0.0.0/0 md5
host bometadata bometa ::/0 md5
host immuta feature_service,+bodata_user,+bodata_impersonate_user 0.0.0.0/0 md5
host immuta feature_service,+bodata_user,+bodata_impersonate_user ::/0 md5
EOF

immuta-odbc-install

If the certificate used by the Immuta Web Service does not match the hostname of the IMMUTA_BASE_URL, host validation must be disabled. Do this by setting immuta_security.ssl_host_verification to false in ${PGDATA}/postgresql.conf.

Copy the TLS certificates for PostgreSQL into the PGDATA directory, and make sure they are owned by immutaqe.

Note: The following commands must be run as root.

export PGDATA=/var/lib/immutaqe/postgresql/data
cp "${PATH_TO_PRIVATE_KEY}" "${PGDATA}/server.key"
cp "${PATH_TO_CERT}" "${PGDATA}/server.crt"
chmod 600 "${PGDATA}/server.key"
chown immutaqe: "${PGDATA}/server.key"
chown immutaqe: "${PGDATA}/server.crt"

Finally, enable and start the immuta-query-engine service.

Note: The following commands must be run as root.

systemctl enable immuta-query-engine
systemctl start  immuta-query-engine