Postgres Pro Shardman comes with a set
of additional server extensions, or modules. On Linux, these extensions
are provided in the postgrespro-contrib package.
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 Shardman 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 Shardman installation, you can view the
pg_available_extensions
system catalog.
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 Shardman.
A DBMS Administrator,
represented by the PGPRO_DBMS_ADMIN role in
Postgres Pro Shardman, is responsible for configuration,
setup, and administration of Postgres Pro Shardman DBMS.
The steps that the administrators need to take to install a new extension are shown in Figure 16.1.
Figure 16.1. Steps taken by administrators to install an extension
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 GRANT clauses to the .sql
file, 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 trusted property in the .control
file 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:
GRANT USAGE ON FOREIGN DATA WRAPPER shardman_fdw TO PGPRO_DBMS_ADMIN;
The DBMS Administrator is allowed to modify Postgres Pro Shardman
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_libraries
variable of the postgresql.conf configuration file
and reloads the database server for changes to take effect.
Creates the extension using the CREATE EXTENSION command.
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 to
FALSE.