Skip to content

Latest commit

 

History

History
426 lines (259 loc) · 15.2 KB

03_managing_extensions.md

File metadata and controls

426 lines (259 loc) · 15.2 KB

Managing Trusted Language Extensions for PostgreSQL

Scope

The pg_tle extension is scoped to an individual PostgreSQL database (e.g. an object created using CREATE DATABASE). Any pg_tle-compatible extension installed into a database is also scoped to that database.

The behavior of certain hooks may be available globally, e.g. across all databases. You will need to ensure that you have enabled pg_tle and any extensions that use these hooks in all the databases that you allow users to access. See the documentation on hooks for more information.

Roles

pg_tle provides a special role for managing extensions:

  • pgtle_admin: A member of this role can install / uninstall pg_tle-compatible extensions. See the Functions section for which management functions require the pgtle_admin role.

A PostgreSQL superuser (e.g. the postgres user) has the privileges of pgtle_admin.

pgtle schema

The pgtle schema contains all of the helper functions used to manage a pg_tle-compatible extension. Additionally, the pgtle schema contains a protected table called pgtle.feature_info that contains information about functions used for hooks.

The only users that can create objects in the pgtle schema are:

  • superusers

  • pgtle_admin role and any users and roles with membership in the pgtle_admin role

  • any roles explicitly given CREATE privilege on the pgtle schema and roles with membership in those roles

If a schema is not specified in a pg_tle-compatible extension, all objects (e.g. functions) in a pg_tle-compatible extension are installed into the current schema (SELECT CURRENT_SCHEMA) by default. Different extensions that have objects of the same name that are installed into the same schema will fail to install when CREATE EXTENSION is called.

Functions

pgtle.available_extensions()

available_extensions is a set-returning functions that returns a list of all available Trusted Language Extensions in a database. Each row contains information about a single extension.

Role

None.

Arguments

None.

Output

  • name: The name of the extension.
  • default_version: The version of the extension to use when CREATE EXTENSION is called without a version.
  • comment: A more detailed description about the extension.

Example

SELECT * FROM pgtle.available_extensions();

pgtle.available_extension_versions()

available_extension_versions is a set-returning functions that returns a list of all available Trusted Language Extensions and their versions. Each row contains information about an individual version of an extension, including if it requires additional privileges for installation.

For more information on the output values, please read the extension files section in the PostgreSQL documentation.

Role

None.

Arguments

None.

Output

  • name: The name of the extension.
  • version: The version of the extension.
  • superuser: This is always false for a pg_tle-compatible extension.
  • trusted: This is always false for a pg_tle-compatible extension.
  • relocatable: This is always false for a pg_tle-compatible extension.
  • schema: This is set if the extension must be installed into a specific schema.
  • requires: An array of extension names that this extension depends on.
  • comment: A more detailed description about the extension.

Example

SELECT * FROM pgtle.available_extension_versions();

pgtle.extension_update_paths(name text)

extension_update_paths is a set-returning functions that returns a list of all the possible update paths for a Trusted Language Extension. Each row shows the path for how to upgrade/downgrade an extension.

Role

None.

Arguments

  • name: The name of the extension to display the upgrade paths for.

Output

  • source: The source version for an update.
  • target: The target version for an update.
  • path: The upgrade path used to update an extension from source version to target version, e.g. 0.1--0.2.

Example

SELECT * FROM pgtle.extension_update_paths('pg_tle_test');

pgtle.install_extension(name text, version text, description text, ext text, requires text[] DEFAULT NULL::text[])

install_extension lets users install a pg_tle-compatible extensions and make them available within a database.

This functions returns 'OK' on success and an error otherwise..

Role

pgtle_admin

Arguments

  • name: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • version: The version of the extension.
  • description: A detailed description about the extension. This is displayed in the comment field in pgtle.available_extensions().
  • ext: The contents of the extension. This contains objects such as functions.
  • requires: An optional parameter that specifies dependencies for this extension. pg_tle is automatically added as a dependency.

Many of the above values are part of the extension control file used to provide information about how to install a PostgreSQL extension. For more information about how each of these values work, please see the PostgreSQL documentation on extension control files.

Example

SELECT pgtle.install_extension(
 'pg_tle_test',
 '0.1',
 'My first pg_tle extension',
$_pgtle_$
  CREATE FUNCTION my_test()
  RETURNS INT
  AS $$
    SELECT 42;
  $$ LANGUAGE SQL IMMUTABLE;
$_pgtle_$
);

pgtle.install_extension_version_sql(name text, version text, ext text)

install_extension_version_sql lets users install a new version of a pg_tle-compatible extension that is already installed in a database and make the new version available within the database.

This functions returns 'OK' on success and an error otherwise.

Role

pgtle_admin

Arguments

  • name: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • version: The version of the extension. This is the value of used when calling 'CREATE EXTENSION VERSION '
  • ext: The contents of the extension version. This contains objects such as functions. This is a full extension script provided as a standalone and is not an update applied to an older version of the extension.

The extension control file for the specified extension must already be installed, and is unchanged by this function. The 'ext' parameter provides a value for the extension version sql file, and it is a standalone sql file and not an incremental update applied after a previously installed version.

Example

SELECT pgtle.install_extension(
 'pg_tle_test',
 '0.2',
 'A new version of my pg_tle extension',
$_pgtle_$
  CREATE FUNCTION my_test()
  RETURNS INT
  AS $$
    SELECT 4242;
  $$ LANGUAGE SQL IMMUTABLE;
$_pgtle_$
);

pgtle.install_update_path(name text, fromvers text, tovers text, ext text)

install_update_path provides an update path between two different version of an extension. This enables user to call ALTER EXTENSION ... UPDATE for a Trusted Language Extension.

Role

pgtle_admin

Arguments

  • name: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • fromvers: The source version of the extension for the upgrade.
  • tovers: The destination version of the extension for the upgrade.
  • ext: The contents of the update. This contains objects such as functions.

Example

SELECT pgtle.install_update_path('pg_tle_test', '0.1', '0.2',
  $_pgtle_$
    CREATE OR REPLACE FUNCTION my_test()
    RETURNS INT
    AS $$
      SELECT 21;
    $$ LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

pgtle.register_feature(proc regproc, feature pg_tle_features)

register_feature provides a way to catalog functions that use pg_tle features such as hooks.

The available features are:

  • passcheck

Role

pgtle_admin

Arguments

  • proc: The name of a stored function to register with a pg_tle feature.
  • feature: The name of the pg_tle feature to register the function with (e.g. passcheck)

Example

SELECT pgtle.register_feature('pw_hook', 'passcheck');

pgtle.register_feature_if_not_exists(proc regproc, feature pg_tle_features)

register_feature provides a way to catalog functions that use pg_tle features such as hooks. It returns true if the feature is registered, otherwise it returns false if the feature is already registered.

The available features are:

  • passcheck

Role

pgtle_admin

Arguments

  • proc: The name of a stored function to register with a pg_tle feature.
  • feature: The name of the pg_tle feature to register the function with (e.g. passcheck)

Example

SELECT pgtle.register_feature_if_not_exists('pw_hook', 'passcheck');

pgtle.set_default_version(name text, version text)

set_default_version lets users set a new default_version for an extension. This is helpful when adding a new upgrade path and wanting to make that version of the extension the default for CREATE EXTENSION calls or ALTER EXTENSION ... UPDATE;

If the extension in name does not already exist, this returns an error. If the version of the extension does not exist, it returns an error.

This functions returns true on success.

Role

pgtle_admin

Arguments

  • name: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • version: The version of the extension to set the default.

pgtle.uninstall_extension(extname text)

uninstall_extension removes all versions of an extension from a database. This prevents future calls of CREATE EXTENSION from installing the extension. If the extension does not exist in the database, then an error is raised.

If the extension is currently active within a database, uninstall_extension does not drop it. You must explicitly call DROP EXTENSION to remove the extension.

Role

pgtle_admin

Arguments

  • extname: The name of the extension. This is the value used when calling CREATE EXTENSION.

Example

SELECT pgtle.uninstall_extension('pg_tle_test');

pgtle.uninstall_extension(extname text, version text)

uninstall_extension removes the specific version of an extension from the database. This prevents CREATE EXTENSION and ALTER EXTENSION from installing or updating to this version of the extension. This also removes all update paths that use this extension version.

If this version is the default version of an extension and there are other versions of the extension, uninstall_extension does not remove the specific version of the extension from the database. It returns an error and hint to use set_default_verion to update the default version to another version and retry.

If this version is the default version and the only version of an extension, uninstall_extension removes the specified extension completely from the database.

If this version of the extension is currently active within a database, uninstall_extension does not drop it. You must explicitly call DROP EXTENSION to remove the extension.

Role

pgtle_admin

Arguments

  • extname: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • version: The version of the extension to uninstall.

Example

SELECT pgtle.uninstall_extension('pg_tle_test', '0.2');

pgtle.uninstall_extension_if_exists(extname text)

uninstall_extension_if_exists is similar to uninstall_extension in that it removes all versions of an extension from a database, but if the extension does not exist in the database, then no error is raised. uninstall_extension_if_exists returns true if the extension was uninstalled, and false if the extension did not exist.

If the extension is currently active within a database, uninstall_extension_if_exists does not drop it. You must explicitly call DROP EXTENSION to remove the extension.

Role

pgtle_admin

Arguments

  • extname: The name of the extension. This is the value used when calling CREATE EXTENSION.

Example

SELECT pgtle.uninstall_extension_if_exists('pg_tle_test');

pgtle.uninstall_update_path(extname text, fromvers text, tovers text)

uninstall_update_path removes the specific update path from an extension. This prevents ALTER EXTENSION ... UPDATE TO from using this as an update path.

If the extension is currently being used on one of the version on this update path, it will remain in the database.

If the update path does not exist, this function will raise an error.

Role

pgtle_admin

Arguments

  • extname: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • fromvers: The source version of the extension used on the update path.
  • tovers: The destination version of the extension used on the update path.

Example

SELECT pgtle.uninstall_update_path('pg_tle_test', '0.1', '0.2');

pgtle.uninstall_update_path_if_exists(extname text, fromvers text, tovers text)

uninstall_update_path_if_exists is similar to uninstall_update_path in that it removes removes the specific update path from an extension. However, if the update path does not exist, no error is raised. and the function returns false.

Role

pgtle_admin

Arguments

  • extname: The name of the extension. This is the value used when calling CREATE EXTENSION.
  • fromvers: The source version of the extension used on the update path.
  • tovers: The destination version of the extension used on the update path.

Example

SELECT pgtle.uninstall_update_path_if_exists('pg_tle_test', '0.1', '0.2');

pgtle.unregister_feature(proc regproc, feature pg_tle_features)

unregister_feature provides a way to remove functions that were registered to use pg_tle features such as hooks.

Role

pgtle_admin

Arguments

  • proc: The name of a stored function to register with a pg_tle feature.
  • feature: The name of the pg_tle feature to register the function with (e.g. passcheck)

Example

SELECT pgtle.unregister_feature('pw_hook', 'passcheck');

pgtle.unregister_feature_if_exists(proc regproc, feature pg_tle_features)

unregister_feature provides a way to remove functions that were registered to use pg_tle features such as hooks. Returns true if it succesfully unregisters the feature, and false if it does not because the feature does not exist.

Role

pgtle_admin

Arguments

  • proc: The name of a stored function to register with a pg_tle feature.
  • feature: The name of the pg_tle feature to register the function with (e.g. passcheck)

Example

SELECT pgtle.unregister_feature_if_exists('pw_hook', 'passcheck');

Next steps

Learn how you can use hooks to use more PostgreSQL capabilities in your Trusted Language Extensions.

Learn how you can create base data types to use more PostgreSQL capabilities in your Trusted Language Extensions.