Thursday, February 9, 2023
HomeCloud ComputingNew – Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon...

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


Voiced by Polly

PostgreSQL has turn out 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 many causes builders use PostgreSQL is it permits them so as to add database performance by constructing extensions with their most popular programming languages.

You may already set up and use PostgreSQL extensions in Amazon Aurora PostgreSQL-Suitable Version 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. Whereas 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.

Immediately, we’re saying the overall 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 offers 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 start out constructing with Trusted Language Extensions, you should utilize trusted languages equivalent 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 may simply set up extensions written in a trusted language on Amazon Aurora PostgreSQL-Suitable Version 14.5 and Amazon RDS for PostgreSQL 14.5 or a more recent model.

Trusted Language Extensions for PostgreSQL is an open-source challenge licensed below Apache License 2.0 on GitHub. You may remark or counsel objects on the Trusted Language Extensions for PostgreSQL roadmap and assist us help this challenge 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 one of the best components of PostgreSQL to construct extensions.

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

Organising Trusted Language Extensions for PostgreSQL
To make use of pg_tle with Amazon Aurora or Amazon RDS for PostgreSQL, you could arrange a parameter group that masses pg_tle within the PostgreSQL shared_preload_libraries setting. Select Parameter teams within the left navigation pane within the Amazon RDS console and Create parameter group to make a brand new parameter group.

Select Create after you choose postgres14 with Amazon RDS for PostgreSQL within the Parameter group household and pg_tle within the Group Identify. You may choose aurora-postgresql14 for an Amazon Aurora PostgreSQL-Suitable cluster.

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

You can too 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,ParameterValue=pg_tle,ApplyMethod=pending-reboot"

Now, you possibly can add the pgtle parameter group to your Amazon Aurora or Amazon RDS for PostgreSQL database. In case you have a database occasion referred to as testing-pgtle, you possibly can add the pgtle parameter group to the database occasion utilizing the command under. 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

Confirm that the pg_tle library is out there 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;

Now you can create and set up Trusted Language Extensions for PostgreSQL in your present database. If you happen to create a brand new extension, you need 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 how you can create our first pg_tle extension!

Constructing 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 typical password dictionary. Many groups have guidelines across the complexity of passwords, notably for database customers. PostgreSQL permits builders to assist implement password complexity utilizing the check_password_hook.

On this instance, you’ll construct a password examine hook utilizing PL/pgSQL. Within the hook, you possibly can examine to see if the user-supplied password is in a dictionary of 10 of the most typical password values:

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Don't 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 typical 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 typical 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_$
);

It is advisable to allow the hook by 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,ParameterValue=on,ApplyMethod=speedy"

It might take a number of minutes for these adjustments to propagate. You may examine that the worth is ready utilizing the SHOW command:

SHOW pgtle.enable_password_check;

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

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

Now you possibly can create this extension in your present database and check out 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 typical 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 typical 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,ParameterValue=off,ApplyMethod=speedy"

You may uninstall this pg_tle extension out of your database and stop 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');

Yow will discover extra pattern extensions and provides them a strive. To construct and check your Trusted Language Extensions in your native PostgreSQL database, you possibly can construct from our supply code after cloning the repository.

Be part of Our Group!
The Trusted Language Extensions for PostgreSQL neighborhood is open to everybody. Give it a strive, and provides us suggestions on what you want to see in future releases. We welcome any contributions, equivalent to new options, instance extensions, further documentation, or any bug stories in GitHub.

To be taught extra about utilizing Trusted Language Extensions for PostgreSQL within the AWS Cloud, see the Amazon Aurora PostgreSQL-Suitable Version and Amazon RDS for PostgreSQL documentation.

Give it a strive, and please ship suggestions to AWS re:Publish for PostgreSQL or by your ordinary AWS help contacts.

Channy



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments