16.2. Installing Additional Supplied Modules#
Postgres Pro comes with a set of additional server extensions, or modules. On Linux, these extensions are provided in thepostgrespro-contrib package. On Windows, these extensions are installed together with the server components.
Once you have the binary files installed, you have to enable additional extensions in the database in order to use them. In most cases, you only need to issue theCREATE EXTENSION command. However, some extensions also require shared libraries to be preloaded on server startup. If you want to use such extensions, you need to configure parameter
shared_preload_libraries = 'lib1,lib2,lib3'
in thepostgresql.conf file of yourPostgres Pro database instance and restart the server before executing theCREATE EXTENSION statement.
For the exact installation and configuration instructions for each particular extension, see the corresponding documentation underAppendix F.
To get the list of extensions available in yourPostgres Pro installation, you can view thepg_available_extensions system catalog.
16.2.1. Installing New Extensions in Certified Product Editions#
Working in a highly secured environment brings some restrictions. With a superuser prohibited due to its unlimited access rights, you can opt for regular users (for example, a DBMS Administrator) to handle operations.
While trusted extensions can be installed and manipulated with non-superuser rights, other extensions require an elevated security level. Installation of some extensions in a highly secured environment, for examplepg_proaudit, follows a special procedure.
This procedure involves an Infrastructure Administrator and a DBMS Administrator:
An Infrastructure Administrator is responsible for the overall system security and does not managePostgres Pro.
A DBMS Administrator, represented by the
PGPRO_DBMS_ADMINrole inPostgres Pro, is responsible for configuration, setup, and administration ofPostgres Pro DBMS.
The steps that the administrators need to take to install a new extension are shown inFigure 16.1.
Figure 16.1. Steps taken by administrators to install an extension
16.2.1.1. Infrastructure Administrator: Steps to be Taken#
Only the Infrastructure Administrator is allowed to modify configuration files of extensions in theshare/extension directory.
The.sql file of an extension contains an SQL interface functions declaration. By default, these functions can be used only by a superuser. Under the extension installation procedure, the Infrastructure Administrator takes the following steps:
Adds the
GRANTclauses to the.sqlfile, as shown below, to allow the DBMS Administrator to use these functions without requesting a superuser to grant such access:-- Create new versions of objectsCREATE FUNCTION pg_proaudit_show()RETURNS TABLE(db_name text, event_type text, object_type text, object_oid oid, role_name text)AS 'MODULE_PATHNAME', 'pg_proaudit_show_conf'LANGUAGE C VOLATILE;REVOKE ALL ON FUNCTION pg_proaudit_show() FROM public;
The Infrastructure Administrator grants rights to a non-superuser:
-- Create new versions of objectsCREATE FUNCTION pg_proaudit_show()RETURNS TABLE(db_name text, event_type text, object_type text, object_oid oid, role_name text)AS 'MODULE_PATHNAME', 'pg_proaudit_show_conf'LANGUAGE C VOLATILE;REVOKE ALL ON FUNCTION pg_proaudit_show() FROM public;GRANT ALL ON FUNCTION pg_proaudit_show() TO PGPRO_DBMS_ADMIN;
Allows a non-superuser to install the extension by changing or adding the
trustedproperty in the.controlfile of the extension, thereby granting a temporary installation permission:trusted = true
Enabling the usage of foreign data wrappers by thePGPRO_DBMS_ADMIN role requires a special security permission. To grant the permission, the Infrastructure Administrator adds theGRANT USAGE ON FOREIGN DATA WRAPPER command to a respective.sql file of the extension. Below is the example forpostgres_fdw:
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO PGPRO_DBMS_ADMIN;
16.2.1.2. DBMS Administrator: Steps to be Taken#
The DBMS Administrator is allowed to modifyPostgres Pro configuration files, except forpg_hba.conf, which stores security information. Only the Infrastructure Administrator is allowed to modify thepg_hba.conf configuration file. Under the extension installation procedure, the DBMS Administrator takes the following steps:
Adds a respective library file to the
shared_preload_librariesvariable of thepostgresql.confconfiguration file and reloads the database server for changes to take effect.Creates the extension using theCREATE EXTENSION command.
16.2.1.3. Infrastructure Administrator: Final Step#
To make further use of the extension secure, the Infrastructure Administrator reverts thetrusted property to the original state:
If it was not specified, it is deleted.
If it was
FALSE, it is changed back toFALSE.