TLE extensions are created with the privilege level of the calling user and owned by them. Create a function with superuser privilege and call it in an extension. Install/create the extension as a non-superuser. Should fail.

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');

Grant public so it's one less things user have to worry about, still have to manually grant execute

Do best effort to prevent SQL injection

As we are using semantic version, the control version files should reflect that, i.e. 1.0 => 1.0.0.

REPRO

SELECT pgtle.install_extension
(
 'test5',
 '0.1',
 $$Distance functions for two points'
 directory = '/tmp/$$,
$_pg_tle_$
    CREATE FUNCTION dist(x1 numeric, y1 numeric, x2 numeric, y2 numeric, l numeric)
    RETURNS numeric
    AS $$
      SELECT ((x2 ^ l - x1 ^ l) ^ (1 / l)) + ((y2 ^ l - y1 ^ l) ^ (1 / l));
    $$ LANGUAGE SQL;
$_pg_tle_$
);

SELECT * FROM pgtle.available_extensions();

EXPECTED

1. The list of available extensions returns

ACTUAL

ERROR:  syntax error in file "(null)" line 1, near token "'"

ANALYSIS

There are a few approaches that we can take here:

1. Validate the "comment" string at input. We can 1/ prevent newlines, or 2/ validate that the comment string does not contain control file parameters at the start of a newline, or 3/ some combination of both.

2. Sanitize the comment string when the control file is parsed and loaded in This can also include 1/ preventing newlines or 2/ validating that the comment string does not contain control file parameters on the newline. It's likely easier to do 1, so that way we don't have to return a NULL or have some sort of error come up on the entire SELECT * FROM pgtle.available_extensions();

Add a GitHub workflow that tests against PostgreSQL 15

In get_extension_control_filename, there is

{
result = (char *) palloc(NAMEDATALEN);
snprintf(result, NAMEDATALEN, "%s.control", extname);
}

The extname parameter is allowed to be 64 chars long. snprintf also allows 64 chars. As snprintf will write until 64 chars is achieved and drop all remaining characters the “.control” part of the string can be completely avoided.

This is also a potential security problem because “.” is allowed in the string so an arbitrary extension can be returned from this function.

The full length that is required would be NAMEDATALEN + len(“.control\n”) Use psprintf or append ".control"

We need to verify that pg_upgrade executes correctly when pg_tle extensions are installed on PostgreSQL 14 when running pg_upgrade to PostgreSQL 15.

Install an extension with trusted param false. Non-superuser and non pgtle_admin user calls CREATE EXTENSION. Creation should fail.

Relates to issue #63
Non-superuser non pgtle_admin should not be able to create extensions that are not trusted

Arguments:

• extname text IN -- the extension name to uninstall
• result bool OUT -- true if it uninstalls, false if it doesn't.

This is similar to DROP EXTENSION IF EXISTS i.e. it does not fail if the extension does not exist. See related discussion in #27.

postgres=# SELECT pgtle.install_extension
(
 'test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALEN',
 '0.1',
 'comment',
$_pg_tle_$                                                                         
    CREATE FUNCTION dist(x1 numeric, y1 numeric, x2 numeric, y2 numeric, l numeric)
    RETURNS numeric
    AS $$                                                                  
      SELECT ((x2 ^ l - x1 ^ l) ^ (1 / l)) + ((y2 ^ l - y1 ^ l) ^ (1 / l));
    $$ LANGUAGE SQL;
$_pg_tle_$
);
NOTICE:  identifier "test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALEN.control" will be truncated to "test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAM"
NOTICE:  identifier "test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALEN--0.1.sql" will be truncated to "test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAM"
ERROR:  Extension 'test9greaterthanNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALENNAMEDATALEN' already installed.

It says the extension's already installed even though this is our first time installing it.

This should be the test to enforce the expected behavior related to #72

Define an interface to pgtle.update_extension.

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

update_extension lets users add an update to the extension. This has the following behaviors:

• Updates the control file with any updates provided above (e.g. trusted flag, requires, etc.)
• Automatically sets default_version to the version provided.
• Automatically dates an update_path from the "previous version" installed to this one

If requires is NULL, no action is taken on the requires field.
If encoding is NULL, no action is taken on the encoding field.

If the extension in name does not already exist, this returns an error.
If this version of the extension already exists, this returns an error.
If an update_path already exists, but the extension version does not, this will overwrite the update path from the previous version and provide a NOTICE that it is doing so.

This functions returns true on success and false on error.

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.
• trusted: If set to true, allows non-superusers with the pgtle_staff privilege to use CREATE EXTENSION for this Trusted-Language 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.
• encoding: An optional parameter that specifies the encoding of the contents of ext.

REPRO

1. Create an extension with a feature_info entry, e.g.:

SELECT pgtle.install_extension
(
 'test_sql',
 '1.0',
$_bcd_$
comment = 'Test BC SQL Functions'
default_version = '1.0'
module_pathname = 'pgbc_test_sql'
relocatable = false
superuser = false
trusted = false
requires = 'pg_tle'
$_bcd_$,
  false,
$_bcd_$
  CREATE SCHEMA password_check;
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  CREATE TABLE password_check.bad_passwords (plaintext) AS
  VALUES
    ('dallas'),
    ('austin'),
    ('thunder'),
    ('taylor'),
    ('matrix');
  CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);

  CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamp, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      invalid bool := false;
    BEGIN
      IF password_type = 'PASSWORD_TYPE_MD5' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE ('md5' || md5(bp.plaintext || username)) = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE bp.plaintext = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  SELECT pgtle.pg_tle_feature_info_sql_insert('password_check.passcheck_hook', 'passcheck');
$_bcd_$
);

2. CREATE and DROP the extension:

CREATE EXTENSION test_sql;
DROP EXTENSION test_sql;

EXPECTED

1. password_check.passcheck_hook is removed from feature_info

ACTUAL

TABLE pgtle.feature_info;

  feature  |  schema_name   |    proname     |                                                      obj_identity                                                       
-----------+----------------+----------------+-------------------------------------------------------------------------------------------------------------------------
 passcheck | password_check | passcheck_hook | password_check.passcheck_hook(pg_catalog.text,pg_catalog.text,pgtle.password_types,timestamp without time zone,boolean)

An extension is either install or not installed. We could either:

1. Return a bool

• true if the extension installs successfully.
• false if not. We would provide an error (likely at WARNING level).

2. Return void

In this case, if the function executes to completion, it will return. Otherwise, we would need to ensure we raise an ERROR and explain why it could not.

Called now pgtle.install_update_path

• name (text): name of the extension
• fromvers (text): from version
• tovers (text): to version
• ext (text): the contents of the extension

Should return false when extension does not exist.

After discussion with @jkatz on PR, changing spec a little. Should never return false, should return true or raise an error.

Currently we have uninstall_extension which will uninstall all versions. We may want to have a function that uninstalls only a single version.

For example, I constantly make mistakes in my code when installing a TLE extensions. I would like to uninstall just that specific version.

REPRO

1. Create a passcheck hook:

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Do not let users use the 10 most commonly used passwords',
$_pgtle_$
  CREATE SCHEMA password_check;
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  CREATE TABLE password_check.bad_passwords (plaintext) AS
  VALUES
    ('123456'),
    ('password'),
    ('12345678'),
    ('qwerty'),
    ('123456789'),
    ('12345'),
    ('1234'),
    ('111111'),
    ('1234567'),
    ('dragon');
  CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);

  CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamp, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      invalid bool := false;
    BEGIN
      IF password_type = 'PASSWORD_TYPE_MD5' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE ('md5' || md5(bp.plaintext || username)) = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE bp.plaintext = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;

  SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);

2. Create the extension:

CREATE EXTENSION my_password_check_rules;

3. Create an user without pgtle_admin role.

CREATE ROLE test_role;

4. Become that user. Set the password using \password to the value password

SET SESSION AUTHORIZATION test_role;
\password

EXPECTED

ERROR:  password must not be found on a common password dictionary

ACTUAL

ERROR:  permission denied for schema pgtle at character 34

NOTES

This is failing when pg_tle is trying to load the hook function via the pgtle.feature_info table:

• pg_tle_feature_info_sql_insert => register_feature
• pg_tle_feature_info_sql_delete => unregister_feature

Create two new functions:

• register_feature_if_not_exists -- return true/false based on outcome
• unregister_feature_if_exists --return true/false based on outcome

Update documentation accordingly.

Install extension with trusted param false. Non-superuser and non pgtle_admin user calls CREATE EXTENSION.
Expected - fails
Observed - succeeds

Test the following situations with pgtle.install_extension:

• If no requires parameter is used, pg_tle is added into the requires list.
• If requires is passed in but pg_tle is not added, then pg_tle is added into the requires list.
• If requires is passed in and pg_tle is added, then pg_tle is present and it's only present once.

In a follow-up to #36 add tests for all the definitions of pgtle.install_extension.

Currently you can only register features (e.g. passcheck hook) in pgtle.feature_info. The only way to remove features is to delete them directly.

Create a function to allow for un-registering the feature.

When installing an extension that has a 'requires' in the control file, the check is performed to make sure dependencies are installed first, but if using CREATE EXTENSION ... CASCADE, it fails.

SELECT pgbc.install_extension
(
 'test_depend',
 '0.1',
$_bcd_$
default_version = '0.1'
module_pathname = 'pgbc_string'
relocatable = false
superuser = false
trusted = true
requires = 'earthdistance'
$_bcd_$,
  false,
$_bcd_$
CREATE FUNCTION test_depend() 
  RETURNS numeric 
  LANGUAGE sql
BEGIN ATOMIC
  SELECT earth()::numeric;
END;
$_bcd_$
);

postgres=# create extension test_depend CASCADE;
NOTICE:  installing required extension "earthdistance"
2022-09-18 09:32:43.337 EDT [1429] ERROR:  extension "earthdistance" is not available
2022-09-18 09:32:43.337 EDT [1429] DETAIL:  Could not find extension control function "earthdistance.control": No such file or directory.
2022-09-18 09:32:43.337 EDT [1429] HINT:  The extension must first be installed in the current database.
2022-09-18 09:32:43.337 EDT [1429] STATEMENT:  create extension test_depend CASCADE;
ERROR:  extension "earthdistance" is not available
DETAIL:  Could not find extension control function "earthdistance.control": No such file or directory.
HINT:  The extension must first be installed in the current database.

postgres=# create extension earthdistance;
CREATE EXTENSION
postgres=# create extension test_depend CASCADE;
CREATE EXTENSION

I ran into an issue where I could register multiple times the same function for password_check hook and then pulling my hair on why inserts are happening multiple time. The right solution from a developer way is to make it a UNIQUE constraint so a function f for a feature can be only registered once and keep the usage simple to use and understand.

Also a cleaner way to drop hooks for specific function is preferable (this is referenced in #58)

pg_ is a reserved prefix for certain things in PostgreSQL, i.e. roles and schemas.

To avoid confusion, we should call everything pgtle. Currently we still prefix GUCs with pg_tle.

We should still call the extension pg_tle as this is a common pattern in PostgreSQL.

All pg_tle compatible extensions should require pg_tle. Whatever a user provides in requires should automatically include pg_tle.

REPRO

1. Try to create an extension with a NULL value for ctl_alt:

SELECT pgtle.install_extension(
 'pg_tle_test',
 '0.1',
$_pgtle_$
comment = 'My first pg_tle extension'
default_version = '0.1'
module_pathname = 'pg_tle_test'
relocatable = false
superuser = false
trusted = true
requires = 'pg_tle'
$_pgtle_$,
  NULL,
$_pgtle_$
  CREATE FUNCTION my_test()
  RETURNS INT
  AS $$
    SELECT 42;
  $$ LANGUAGE SQL IMMUTABLE;
$_pgtle_$
);

EXPECTED

1. The function call to fail or output some sort of error message

ACTUAL

1. I get this return:

 install_extension 
-------------------
 
(1 row)

Additionally, I do not see the extension files created in the pgtle schema.

Comments

We should raise an error on this, though this could change based on how we decide to proceed with the function behavior. See related discussion in #27

When a new pull request is opened, we should run make installcheck on it. This means we need to create a GitHub action that:

• Provision some base OS with PostgreSQL 14 and the build tools
• Ensure pg_tle + contents of the PR are loaded into said environ
• Run make and make install on the code
• Ensure pg_tle is in shared_preload_libraries
• run make installcheck

We would ultimately want to set this up against PostgreSQL 15 too, but we can start with 14.

This is to prevent pg_upgrade's from being stuck. At the same time, we should provide users with a function wrapper to uninstall an extension (if it hasn't already been installed/created)

REPRO

CREATE EXTENSION pg_tle;

SELECT pgtle.install_extension
(
 'test9',
 '0.1',
 'comment',
$_pg_tle_$
    CREATE FUNCTION dist(x1 numeric, y1 numeric, x2 numeric, y2 numeric, l numeric)
    RETURNS numeric
    AS $$
      SELECT ((x2 ^ l - x1 ^ l) ^ (1 / l)) + ((y2 ^ l - y1 ^ l) ^ (1 / l));
    $$ LANGUAGE SQL;
$_pg_tle_$
);

SELECT pgtle.install_extension
(
 'test9.control"(),pg_sleep(10),pgtle."test9',
 '0.1',
 'comment',
$_pg_tle_$
    CREATE FUNCTION dist(x1 numeric, y1 numeric, x2 numeric, y2 numeric, l numeric)
    RETURNS numeric
    AS $$
      SELECT ((x2 ^ l - x1 ^ l) ^ (1 / l)) + ((y2 ^ l - y1 ^ l) ^ (1 / l));
    $$ LANGUAGE SQL;
$_pg_tle_$
);

SELECT * FROM pgtle.available_extensions();

EXPECTED

1. Nothing out of the ordinary to happen. A list of extension names return

ACTUAL

1. You wait 10 seconds for everything to load.

ANALYSIS

There are several approaches to this. One recommendation is to use an "allow list" approach of what characters are acceptable in extension names when sanitizing the inputs.

Include index on feature and proc_name so we don't have the same function run multiple times

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 this version of the extension already exists, this returns an error.

This functions returns true on success and false on error.

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.

Currently, a pg_dump file will not put out TLE artifacts in the correct install order. For example, if we have a TLE extensions called "tle_test", pg_dump will generate something like this:

CREATE EXTENSION pg_tle;
CREATE EXTESNION tle_test;

CREATE FUNCTION pgtle."tle_test.control()" ...

This will fail, as the "control file" is not available yet in the DB.

One option is to provide a pg_tle_dump wrapper script around pg_dump to put the TLE artifacts in the correct order.

e.g. some are called extname and others are called name. Good idea to be consistent.

Functions are stored using the server_side encoding. We can remove the client_side v server_side encoding check in install_extension.

We should validate that by default the permissions to insert in the table are revoked from public, and only a privileged user can delegate the permission after the fact.

Let's test for both direct inserts into the table, and the one provided by the helper function pg_tle_feature_info_sql_insert

We do not want to expose the "control file" string in pgtle.install_extension. Thus a new definition for pgtle.install_extension:

• name (text): extension name. automatically sets module_pathname to the name. Sets relocatable to false.
• version (text): the initial version of the extension. automatically sets default_version
• trusted (bool): true if this is a trusted extension and can be installed by users with the pgtle_staff role. If true, sets trusted to true. If false, sets trusted to false. superuser is always set to false.
• description (text): sets comment
• sql_str (text): the contents of the extension
• requires (text[]): text array of any requirements. pg_tle automatically includes pg_tle as a requirement within the array.
• encoding (text): if set, includes the encoding of the sql_str

This is currently not the case. Users without being granted CREATE ON DATABASE can still create installed TLE extensions.

Add a regression test for this.

pg_tle_api.out has at least one incorrect line (L46) that is falsely passing pg_tle_api.sql. Postgres doesn't respond to the SIGHUP in time for enable_password_check to be updated by the next line. We could add a SELECT pg_sleep(0.1); after each reload.

Expected:

ALTER SYSTEM SET pgtle.enable_password_check = 'require';
SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

-- Expect an error for require if no entries are present
ALTER ROLE testuser with password 'pass';
ERROR:  pg_tle.enable_password_check feature is set to require, however no entries exist in pg_tle.feature_info with the feature passcheck
-- Insert a value into the feature table

Actual:

ALTER SYSTEM SET pgtle.enable_password_check = 'require';
SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

-- Expect an error for require if no entries are present
ALTER ROLE testuser with password 'pass';
-- Insert a value into the feature table

With #34 any user will be disallowed from installing/creating TLE extensions that require superuser privilege. So we don't need a GUC parameter to conditionally guard elevation to superuser privilege, and we don't need and shouldn't have a path to elevate to superuser privilege

PostgreSQL has this function

extern char *downcase_truncate_identifier(const char *ident, int len, bool warn);

That is used to take identifiers (e.g. extension name) and...downcase and truncate it.

We should call this function on install_extension prior to performing validations.

REPRO

SELECT pgtle.install_extension
(
 'new_ext',
 '1.0',
 'Test TLE Functions',
$_pgtle_$
  CREATE FUNCTION fun()
  RETURNS INT AS $$ SELECT 1; $$ LANGUAGE SQL;
$_pgtle_$
);

SELECT pgtle.install_update_path
(
 'new_ext',
 '1.0',
 '1.1',
$_pgtle_$
  CREATE OR REPLACE FUNCTION fun()
  RETURNS INT AS $$ SELECT 2; $$ LANGUAGE SQL;
$_pgtle_$
);

EXPECTED

1. The second call fails

ACTUAL

1. The second call succeeds

This is similar to #45

REPRO

1. Install an extension that registers a passcheck hook such as:

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Do not let users use the 10 most commonly used passwords',
$_pgtle_$
  CREATE SCHEMA password_check;
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  CREATE TABLE password_check.bad_passwords (plaintext) AS
  VALUES
    ('123456'),
    ('password'),
    ('12345678'),
    ('qwerty'),
    ('123456789'),
    ('12345'),
    ('1234'),
    ('111111'),
    ('1234567'),
    ('dragon');
  CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);

  CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamp, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      invalid bool := false;
    BEGIN
      IF password_type = 'PASSWORD_TYPE_MD5' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE ('md5' || md5(bp.plaintext || username)) = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE bp.plaintext = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql;

  GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;

  SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);

2. Call CREATE EXTENSION:

CREATE EXTENSION my_password_check_rules;

3. Call DROP EXTENSION:

DROP EXTENSION my_password_check_rules;

4. Unregister the feature:

SELECT pgtle.unregister_feature('password_check.passcheck_hook', 'passcheck');

EXPECTED

1. The feature is unregistered

ACTUAL

ERROR:  schema "password_check" does not exist

ANALYSIS

We should be less strict in our checking on unregister_feature since some artifacts may be removed. While it is better to be able to query by identity, we should fall back if it's not there and query by the provided schema/proc name.

Related #32

To avoid any issues with search_path, schema qualify all queries.

This is technically a "chore" but there is potential buggy behavior.

All of the other extension functions use text as name, so this appears to be an arbitrary restriction.

Consider "relaxing" the length of the name input.

We need a check to see if a TLE extension is already installed by a specific version.

Options

• Check for existence of TLE extension. Ensure it's case-insensitive
• Create a case-insensitive UNIQUE constraint on extension name + version