17.2. Installing Additional Supplied Modules
Postgres Pro comes with a set of additional server extensions, or modules. On Linux, these extensions are provided in the postgrespro-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 the CREATE 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 the postgresql.conf file of your Postgres Pro database instance and restart the server before executing the CREATE EXTENSION statement.
For the exact installation and configuration instructions for each particular extension, see the corresponding documentation under Appendix F.
To get the list of extensions available in your Postgres Pro installation, you can view the pg_available_extensions system catalog.
17.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 example pg_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 manage Postgres Pro.
A DBMS Administrator, represented by the
PGPRO_DBMS_ADMINrole in Postgres Pro, is responsible for configuration, setup, and administration of Postgres Pro DBMS.
The steps that the administrators need to take to install a new extension are shown in Figure 17.1.
Figure 17.1. Steps taken by administrators to install an extension
17.2.1.1. Infrastructure Administrator: Steps to be Taken
Only the Infrastructure Administrator is allowed to modify configuration files of extensions in the share/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 objects CREATE 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 objects CREATE 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 the PGPRO_DBMS_ADMIN role requires a special security permission. To grant the permission, the Infrastructure Administrator adds the GRANT USAGE ON FOREIGN DATA WRAPPER command to a respective .sql file of the extension. Below is the example for postgres_fdw:
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO PGPRO_DBMS_ADMIN;
The in_memory extension not only creates the in_memory_fdw wrapper, but also automatically creates the in_memory server. To grant the usage permission, the Infrastructure Administrator adds the following command to the in_memory-- file: version_number.sql
GRANT USAGE ON FOREIGN SERVER in_memory TO PGPRO_DBMS_ADMIN;
17.2.1.2. DBMS Administrator: Steps to be Taken
The DBMS Administrator is allowed to modify Postgres Pro configuration files, except for pg_hba.conf, which stores security information. Only the Infrastructure Administrator is allowed to modify the pg_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 the CREATE EXTENSION command.
17.2.1.3. Infrastructure Administrator: Final Step
To make further use of the extension secure, the Infrastructure Administrator reverts the trusted property to the original state:
If it was not specified, it is deleted.
If it was
FALSE, it is changed back toFALSE.