New – Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS
PostgreSQL has become the preferred open-source relational database for many enterprises and start-ups with its extensible design for developers. One of the reasons developers use PostgreSQL is it allows them to add database functionality by building extensions with their preferred programming languages. You can already install and use PostgreSQL extensions in Amazon Aurora PostgreSQL-Compatible Edition…
PostgreSQL has become the preferred open-source relational database for many enterprises and start-ups with its extensible design for developers. One of the reasons developers use PostgreSQL is it allows them to add database functionality by building extensions with their preferred programming languages.
Today, we are announcing the general availability of Trusted Language Extensions for PostgreSQL (pg_tle), a new open-source development kit for building PostgreSQL extensions. With Trusted Language Extensions for PostgreSQL, developers can build high-performance extensions that run safely on PostgreSQL.
Trusted Language Extensions for PostgreSQL provides database administrators control over who can install extensions and a permissions model for running them, letting application developers deliver new functionality as soon as they determine an extension meets their needs.
To start building with Trusted Language Extensions, you can use trusted languages such as JavaScript, Perl, and PL/pgSQL. These trusted languages have safety attributes, including restricting direct access to the file system and preventing unwanted privilege escalations. You can easily install extensions written in a trusted language on Amazon Aurora PostgreSQL-Compatible Edition 14.5 and Amazon RDS for PostgreSQL 14.5 or a newer version.
Trusted Language Extensions for PostgreSQL is an open-source project licensed under Apache License 2.0 on GitHub. You can comment or suggest items on the Trusted Language Extensions for PostgreSQL roadmap and help us support this project across multiple programming languages, and more. Doing this as a community will help us make it easier for developers to use the best parts of PostgreSQL to build extensions.
Let’s explore how we can use Trusted Language Extensions for PostgreSQL to build a 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, you need to set up a parameter group that loads pg_tle in the PostgreSQL shared_preload_libraries setting. Choose Parameter groups in the left navigation pane in the Amazon RDS console and Create parameter group to make a new parameter group.
Choose Create after you select postgres14 with Amazon RDS for PostgreSQL in the Parameter group family and pg_tle in the Group Name. You can select aurora-postgresql14 for an Amazon Aurora PostgreSQL-Compatible cluster.
Choose a created pgtle parameter group and Edit in the Parameter group actions dropbox menu. You can search shared_preload_library in the search box and choose Edit parameter. You can add your preferred values, including pg_tle, and choose Save changes.
You can also do the same job in the AWS Command Line Interface (AWS CLI).
Now, you can add the pgtle parameter group to your Amazon Aurora or Amazon RDS for PostgreSQL database. If you have a database instance called testing-pgtle, you can add the pgtle parameter group to the database instance using the command below. Please note that this will cause an active instance to reboot.
Verify that the pg_tle library is available on your Amazon Aurora or Amazon RDS for PostgreSQL instance. Run the following command on your PostgreSQL instance:
SHOW shared_preload_libraries;
pg_tle should appear in the output.
Now, we need to create the pg_tle extension in your current database to run the command:
CREATE EXTENSION pg_tle;
You can now create and install Trusted Language Extensions for PostgreSQL in your current database. If you create a new extension, you should grant the pgtle_admin role to your primary user (e.g., postgres) with the following command:
GRANT pgtle_admin TO postgres;
Let’s now see how to create our first pg_tle extension!
Building a Trusted Language Extension for PostgreSQL For this example, we are going to build a pg_tle extension to validate that a user is not setting a password that’s found in a common password dictionary. Many teams have rules around the complexity of passwords, particularly for database users. PostgreSQL allows developers to help enforce password complexity using the check_password_hook.
In this example, you will build a password check hook using PL/pgSQL. In the hook, you can check to see if the user-supplied password is in a dictionary of 10 of the most common password values:
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 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 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_$ );
You need to enable the hook through the pgtle.enable_password_check configuration parameter. On Amazon Aurora and Amazon RDS for PostgreSQL, you can do so with the following command:
It may take several minutes for these changes to propagate. You can check that the value is set using the SHOW command:
SHOW pgtle.enable_password_check;
If the value is on, you will see the following output:
pgtle.enable_password_check —————————– on
Now you can create this extension in your current database and try setting your password to one of the dictionary passwords and observe how the hook rejects it:
CREATE EXTENSION my_password_check_rules; CREATE ROLE test_role PASSWORD ‘123456’; ERROR: password must not be found on a common password dictionary CREATE ROLE test_role; SET SESSION AUTHORIZATION test_role; SET password_encryption TO ‘md5’; password — set to “password” ERROR: password must not be found on a common password dictionary
To disable the hook, set the value of pgtle.enable_password_check to off:
You can uninstall this pg_tle extension from your database and prevent anyone else from running CREATE EXTENSION on my_password_check_rules with the following command:
DROP EXTENSION my_password_check_rules; SELECT pgtle.uninstall_extension(‘my_password_check_rules’);
You can find more sample extensions and give them a try. To build and test your Trusted Language Extensions in your local PostgreSQL database, you can build from our source code after cloning the repository.
Join Our Community! The Trusted Language Extensions for PostgreSQL community is open to everyone. Give it a try, and give us feedback on what you would like to see in future releases. We welcome any contributions, such as new features, example extensions, additional documentation, or any bug reports in GitHub.
AWS Weekly Roundup: HIPAA eligible with Amazon Q Business, Amazon DCV, AWS re:Post Agent, and more (Oct 07, 2024)
Last Friday, I had the privilege of attending China Engineer’s Day 2024(CED 2024) in Hangzhou as the Amazon Web Services (AWS) speaker. The event was organized by the China Computer Federation (CCF), one of the most influential professional developer communities in China. At CED 2024, I spoke about how AI development tools can improve developer…
Last Friday, I had the privilege of attending China Engineer’s Day 2024(CED 2024) in Hangzhou as the Amazon Web Services (AWS) speaker. The event was organized by the China Computer Federation (CCF), one of the most influential professional developer communities in China. At CED 2024, I spoke about how AI development tools can improve developer […]
Build a generative AI Slack chat assistant using Amazon Bedrock and Amazon Kendra
In this post, we describe the development of a generative AI Slack application powered by Amazon Bedrock and Amazon Kendra. This is designed to be an internal-facing Slack chat assistant that helps answer questions related to the indexed content. Source
In this post, we describe the development of a generative AI Slack application powered by Amazon Bedrock and Amazon Kendra. This is designed to be an internal-facing Slack chat assistant that helps answer questions related to the indexed content.
Create your fashion assistant application using Amazon Titan models and Amazon Bedrock Agents
In this post, we implement a fashion assistant agent using Amazon Bedrock Agents and the Amazon Titan family models. The fashion assistant provides a personalized, multimodal conversational experience. Source
In this post, we implement a fashion assistant agent using Amazon Bedrock Agents and the Amazon Titan family models. The fashion assistant provides a personalized, multimodal conversational experience.