New – Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS

PostgreSQL has grow to be the most popular open-source relational database for a lot of enterprises and start-ups with its extensible design for builders. One of the explanations builders use PostgreSQL is it permits them so as to add database performance by constructing extensions with their most popular programming languages.

You can already set up and use PostgreSQL extensions in Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service for PostgreSQL. We help greater than 85 PostgreSQL extensions in Amazon Aurora and Amazon RDS, such because the pgAudit extension for logging your database exercise. While many workloads use these extensions, we heard our prospects asking for flexibility to construct and run the extensions of their selecting for his or her PostgreSQL database cases.

Today, we’re saying the final availability of Trusted Language Extensions for PostgreSQL (pg_tle), a brand new open-source growth package for constructing PostgreSQL extensions. With Trusted Language Extensions for PostgreSQL, builders can construct high-performance extensions that run safely on PostgreSQL.

Trusted Language Extensions for PostgreSQL supplies database directors management over who can set up extensions and a permissions mannequin for working them, letting utility builders ship new performance as quickly as they decide an extension meets their wants.

To begin constructing with Trusted Language Extensions, you should use trusted languages similar to JavaScript, Perl, and PL/pgSQL. These trusted languages have security attributes, together with proscribing direct entry to the file system and stopping undesirable privilege escalations. You can simply set up extensions written in a trusted language on Amazon Aurora PostgreSQL-Compatible Edition 14.5 and Amazon RDS for PostgreSQL 14.5 or a more recent model.

Trusted Language Extensions for PostgreSQL is an open-source undertaking licensed underneath Apache License 2.0 on GitHub. You can remark or counsel objects on the Trusted Language Extensions for PostgreSQL roadmap and assist us help this undertaking throughout a number of programming languages, and extra. Doing this as a neighborhood will assist us make it simpler for builders to make use of the very best elements of PostgreSQL to construct extensions.

Let’s discover how we are able to use Trusted Language Extensions for PostgreSQL to construct a brand new PostgreSQL extension for Amazon Aurora and Amazon RDS.

Setting up Trusted Language Extensions for PostgreSQL
To use pg_tle with Amazon Aurora or Amazon RDS for PostgreSQL, it’s essential to arrange a parameter group that hundreds pg_tle within the PostgreSQL shared_preload_libraries setting. Choose Parameter teams within the left navigation pane within the Amazon RDS console and Create parameter group to make a brand new parameter group.

Choose Create after you choose postgres14 with Amazon RDS for PostgreSQL within the Parameter group household and pg_tle within the Group Name. You can choose aurora-postgresql14 for an Amazon Aurora PostgreSQL-Compatible cluster.

Choose a created pgtle parameter group and Edit within the Parameter group actions dropbox menu. You can search shared_preload_library within the search field and select Edit parameter. You can add your most popular values, together with pg_tle, and select Save adjustments.

You can even do the identical job within the AWS Command Line Interface (AWS CLI).

$ aws rds create-db-parameter-group 
  --region us-east-1 
  --db-parameter-group-name pgtle 
  --db-parameter-group-family aurora-postgresql14 
  --description "pgtle group"

$ aws rds modify-db-parameter-group 
  --region us-east-1 
  --db-parameter-group-name pgtle 
  --parameters "ParameterName=shared_preload_libraries,ParameterWorth=pg_tle,ApplyMethod=pending-reboot"

Now, you may add the pgtle parameter group to your Amazon Aurora or Amazon RDS for PostgreSQL database. If you have got a database occasion referred to as testing-pgtle, you may add the pgtle parameter group to the database occasion utilizing the command beneath. Please observe that this may trigger an lively occasion to reboot.

$ aws rds modify-db-instance 
  --region us-east-1 
  --db-instance-identifier testing-pgtle 
  --db-parameter-group-name pgtle-pg 
  --apply-immediately

Verify that the pg_tle library is accessible in your Amazon Aurora or Amazon RDS for PostgreSQL occasion. Run the next command in your PostgreSQL occasion:

SHOW shared_preload_libraries;

pg_tle ought to seem within the output.

Now, we have to create the pg_tle extension in your present database to run the command:

 CREATE EXTENSION pg_tle;

You can now create and set up Trusted Language Extensions for PostgreSQL in your present database. If you create a brand new extension, it is best to grant the pgtle_admin position to your main person (e.g., postgres) with the next command:

GRANT pgtle_admin TO postgres;

Let’s now see the way to create our first pg_tle extension!

Building a Trusted Language Extension for PostgreSQL
For this instance, we’re going to construct a pg_tle extension to validate {that a} person just isn’t setting a password that’s present in a standard password dictionary. Many groups have guidelines across the complexity of passwords, significantly for database customers. PostgreSQL permits builders to assist implement password complexity utilizing the check_password_hook.

In this instance, you’ll construct a password verify hook utilizing PL/pgSQL. In the hook, you may verify to see if the user-supplied password is in a dictionary of 10 of the commonest password values:

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Do not let customers use the ten mostly 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 textual content, password textual content, password_type pgtle.password_types, valid_until timestamptz, 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 should not be discovered on a standard 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 should not be discovered on a standard 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_$
);

You have to allow the hook by way of the pgtle.enable_password_check configuration parameter. On Amazon Aurora and Amazon RDS for PostgreSQL, you are able to do so with the next command:

$ aws rds modify-db-parameter-group 
    --region us-east-1 
    --db-parameter-group-name pgtle 
    --parameters "ParameterName=pgtle.enable_password_check,ParameterWorth=on,ApplyMethod=quick"

It could take a number of minutes for these adjustments to propagate. You can verify that the worth is ready utilizing the SHOW command:

SHOW pgtle.enable_password_check;

If the worth is on, you will note the next output:

 pgtle.enable_password_check
-----------------------------
 on

Now you may create this extension in your present database and take a look at setting your password to one of many dictionary passwords and observe how the hook rejects it:

CREATE EXTENSION my_password_check_rules;

CREATE ROLE test_role PASSWORD '123456';
ERROR:  password should not be discovered on a standard password dictionary

CREATE ROLE test_role;
SET SESSION AUTHORIZATION test_role;
SET password_encryption TO 'md5';
password
-- set to "password"
ERROR:  password should not be discovered on a standard password dictionary

To disable the hook, set the worth of pgtle.enable_password_check to off:

$ aws rds modify-db-parameter-group 
    --region us-east-1 
    --db-parameter-group-name pgtle 
    --parameters "ParameterName=pgtle.enable_password_check,ParameterWorth=off,ApplyMethod=quick"

You can uninstall this pg_tle extension out of your database and forestall anybody else from working CREATE EXTENSION on my_password_check_rules with the next command:

DROP EXTENSION my_password_check_rules;
SELECT pgtle.uninstall_extension('my_password_check_rules');

You can discover extra pattern extensions and provides them a attempt. To construct and take a look at your Trusted Language Extensions in your native PostgreSQL database, you may construct from our supply code after cloning the repository.

Join Our Community!
The Trusted Language Extensions for PostgreSQL neighborhood is open to everybody. Give it a attempt, and provides us suggestions on what you want to see in future releases. We welcome any contributions, similar to new options, instance extensions, further documentation, or any bug experiences in GitHub.

To study extra about utilizing Trusted Language Extensions for PostgreSQL within the AWS Cloud, see the Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL documentation.

Give it a attempt, and please ship suggestions to AWS re:Post for PostgreSQL or by way of your standard AWS help contacts.

– Channy