repost.aws
Open in
urlscan Pro
13.224.189.105
Public Scan
URL:
https://repost.aws/knowledge-center/rds-postgresql-password-policy
Submission: On November 19 via api from DE — Scanned from DE
Submission: On November 19 via api from DE — Scanned from DE
Form analysis 1 forms found in the DOM
<form><label for="CommentCreate_md" class="PostBody_label__pfzPS">Comment on this article</label>
<div class="Editor_wrapper__DGWud">
<div class="Editor_editor__Zwi9K PostBody_editor__uVYDf">
<div id="CommentCreate" class="rc-md-editor ">
<div class="rc-md-navigation visible">
<div class="navigation-nav left">
<div class="button-wrap"><span class="button button-type-bold" title="Bold" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-bold"></i></span><span class="button button-type-italic" title="Italic" role="button"
tabindex="0"><i class="rmel-iconfont rmel-icon-italic"></i></span><span class="button button-type-strikethrough" title="Strikethrough" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-strikethrough"></i></span><span
class="button button-type-unordered" title="Unordered List" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-list-unordered"></i></span><span class="button button-type-order" title="Ordered List" role="button"
tabindex="0"><i class="rmel-iconfont rmel-icon-list-ordered"></i></span><span class="button button-type-block-quote" title="Quote" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-quote"></i></span><span
class="button button-type-block-code-block" title="Code" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-code-block"></i></span><span class="button button-type-code-inline" title="Inline Code" role="button"
tabindex="0"><i class="rmel-iconfont rmel-icon-code"></i></span><span role="button" class="button button-type-table" title="Table"><i class="rmel-iconfont rmel-icon-grid"></i>
<div class="drop-wrap hidden">
<ul class="table-list wrap" style="width: 135px; height: 112px;">
<li class="list-item " style="top: 0px; left: 0px;"></li>
<li class="list-item " style="top: 0px; left: 23px;"></li>
<li class="list-item " style="top: 0px; left: 46px;"></li>
<li class="list-item " style="top: 0px; left: 69px;"></li>
<li class="list-item " style="top: 0px; left: 92px;"></li>
<li class="list-item " style="top: 0px; left: 115px;"></li>
<li class="list-item " style="top: 23px; left: 0px;"></li>
<li class="list-item " style="top: 23px; left: 23px;"></li>
<li class="list-item " style="top: 23px; left: 46px;"></li>
<li class="list-item " style="top: 23px; left: 69px;"></li>
<li class="list-item " style="top: 23px; left: 92px;"></li>
<li class="list-item " style="top: 23px; left: 115px;"></li>
<li class="list-item " style="top: 46px; left: 0px;"></li>
<li class="list-item " style="top: 46px; left: 23px;"></li>
<li class="list-item " style="top: 46px; left: 46px;"></li>
<li class="list-item " style="top: 46px; left: 69px;"></li>
<li class="list-item " style="top: 46px; left: 92px;"></li>
<li class="list-item " style="top: 46px; left: 115px;"></li>
<li class="list-item " style="top: 69px; left: 0px;"></li>
<li class="list-item " style="top: 69px; left: 23px;"></li>
<li class="list-item " style="top: 69px; left: 46px;"></li>
<li class="list-item " style="top: 69px; left: 69px;"></li>
<li class="list-item " style="top: 69px; left: 92px;"></li>
<li class="list-item " style="top: 69px; left: 115px;"></li>
<li class="list-item " style="top: 92px; left: 0px;"></li>
<li class="list-item " style="top: 92px; left: 23px;"></li>
<li class="list-item " style="top: 92px; left: 46px;"></li>
<li class="list-item " style="top: 92px; left: 69px;"></li>
<li class="list-item " style="top: 92px; left: 92px;"></li>
<li class="list-item " style="top: 92px; left: 115px;"></li>
</ul>
</div>
</span><span class="button button-type-link" title="Link" role="button" tabindex="0"><i class="rmel-iconfont rmel-icon-link"></i></span><span role="button" data-testid="button-undo" class="button button-type-undo disabled" title="Undo"
tabindex="-1"><i class="rmel-iconfont rmel-icon-undo"></i></span><span role="button" data-testid="button-redo" class="button button-type-redo disabled" title="Redo"
tabindex="-1"><i class="rmel-iconfont rmel-icon-redo"></i></span><span></span></div>
</div>
<div class="navigation-nav right">
<div class="button-wrap">
<span><a title="Preview" role="button" tabindex="0">Preview</a></span><span><a title="Formatting guide" href="https://www.markdownguide.org/basic-syntax/" target="_blank" rel="noopener noreferrer">Formatting guide</a></span></div>
</div>
</div>
<div class="editor-container">
<section class="section sec-md visible"><textarea id="CommentCreate_md" name="textarea" placeholder="Start writing your comment" class="section-container input " wrap="hard" maxlength="15000" aria-invalid="false"
aria-describedby="CommentCreate-error" style="height: 200px;"></textarea></section>
<section class="section sec-html visible">
<div id="CommentCreate_html" class="section-container html-wrap" style="height: 200px;">
<div class="custom-html-style"></div>
</div>
</section>
</div>
</div>
</div>
</div>
<div id="CommentCreate-error" class="PostBody_errors__ylDGI"></div>
<div class="ReplyCreate_actionContainer__Hqd27"><button title="Clear" type="button" class="ant-btn ant-btn-default Button_secondary__pFIlL Button_disabled__Cu6k9 Button_large__AqiVe " disabled=""><span>Clear</span></button><span
style="display:inline-block;cursor:not-allowed" class="ant-tooltip-disabled-compatible-wrapper"><button title="Post comment" style="pointer-events:none" type="button"
class="ant-btn ant-btn-default Button_primary__swzAa Button_disabled__Cu6k9 Button_large__AqiVe " disabled=""><span>Post comment</span></button></span></div>
</form>Text Content
SELECT YOUR COOKIE PREFERENCES
We use essential cookies and similar tools that are necessary to provide our
site and services. We use performance cookies to collect anonymous statistics,
so we can understand how customers use our site and make improvements. Essential
cookies cannot be deactivated, but you can choose “Customize” or “Decline” to
decline performance cookies.
If you agree, AWS and approved third parties will also use cookies to provide
useful site features, remember your preferences, and display relevant content,
including relevant advertising. To accept or decline all non-essential cookies,
choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”
AcceptDeclineCustomize
CUSTOMIZE COOKIE PREFERENCES
We use cookies and similar tools (collectively, "cookies") for the following
purposes.
ESSENTIAL
Essential cookies are necessary to provide our site and services and cannot be
deactivated. They are usually set in response to your actions on the site, such
as setting your privacy preferences, signing in, or filling in forms.
PERFORMANCE
Performance cookies provide anonymous statistics about how customers navigate
our site so we can improve site experience and performance. Approved third
parties may perform analytics on our behalf, but they cannot use the data for
their own purposes.
Allow performance category
Allowed
FUNCTIONAL
Functional cookies help us provide useful site features, remember your
preferences, and display relevant content. Approved third parties may set these
cookies to provide certain site features. If you do not allow these cookies,
then some or all of these services may not function properly.
Allow functional category
Allowed
ADVERTISING
Advertising cookies may be set through our site by us or our advertising
partners and help us deliver relevant marketing content. If you do not allow
these cookies, you will experience less relevant advertising.
Allow advertising category
Allowed
Blocking some types of cookies may impact your experience of our sites. You may
review and change your choices at any time by selecting Cookie preferences in
the footer of this site. We and selected third-parties use cookies or similar
technologies as specified in the AWS Cookie Notice.
CancelSave preferences
UNABLE TO SAVE COOKIE PREFERENCES
We will only store essential cookies at this time, because we were unable to
save your cookie preferences.
If you want to change your cookie preferences, try again later using the link in
the AWS console footer, or contact support if the problem persists.
Dismiss
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
re:Post
Search
English
EnglishDeutschEspañolFrançaisItaliano日本語한국어Português中文 (简体)中文 (繁體)
Resources
Sign in
* Home
* Questions
* Knowledge Center
* Articles
* Selections
* Tags
* Topics
* Community Groups
* More…
Ask question
Get 2 months of Business Support trial with a hassle-free credit refund
Get 24x7 email/chat/phone access to Cloud Support Engineers, 1 hour response
time and Unlimited case contacts. Click here to start a 60 day trial today.
1. /
2. Knowledge Center/
3. How do I create a password policy in Amazon RDS for PostgreSQL?/
HOW DO I CREATE A PASSWORD POLICY IN AMAZON RDS FOR POSTGRESQL?
5 minute read
0
I want to create a password policy in Amazon Relational Database Service (Amazon
RDS) for PostgreSQL.
SHORT DESCRIPTION
By default, Amazon RDS for PostgreSQL doesn't have functionality to enforce a
password policy. However, you can use PostgreSQL hooks and TLE extensions to
extend PostgreSQL's core capabilities. To customize how PostgreSQL handles
passwords when you create or change passwords for users or roles, use the
passcheck hook.
Note: Trusted Language Extensions (TLE) supports Amazon RDS for PostgreSQL
versions 16.1 and newer, 15.2 and newer, 14.5 and newer, and 13.12 and newer.
For more information, see Requirements for using Trusted Language Extensions for
PostgreSQL.
RESOLUTION
Before you begin, set up the TLE extension for your Amazon RDS for PostgreSQL
instance. To set up the TLE extension, complete the following steps:
1. Update the shared_preload_libraries parameter to include pg_tle in your
custom parameter group that's associated with your Amazon RDS for PostgreSQL
instance.
2. Reboot your RDS instance for the update to the shared_preload_libraries
parameter to take effect.
3. Log in to your instance, and then confirm that the shared_preload_libraries
parameter is updated.
postgres=> SHOW shared_preload_libraries;
shared_preload_libraries
rdsutils,pg_tle,pg_stat_statements
(1 row)
postgres=>
4. Create the TLE extension:
Note: To set up and configure the pg_tle extension, your database user role
must have rds_superuser role permissions.
CREATE EXTENSION pg_tle;
5. Grant the pgtle_admin role to your Amazon RDS for PostgreSQL instance
primary user. If you used a default user, then it's the postgres user.
Note: Replace example-user with your Amazon RDS for PostgreSQL instance
primary user.
GRANT pgtle_admin TO example-user;
SET UP THE PASSCHECK HOOK
A PostgreSQL passcheck hook checks passwords for SQL operations and doesn't
allow users to set passwords listed in the password_check.bad_passwords table.
The passcheck hook also checks password length and confirms that passwords
contain uppercase and lowercase letters, numbers, and special characters.
Note: A PostgreSQL hook's function can be modified to your specific needs. You
can add more passwords to the bad_passwords table, change the password length
required, or modify the function to check the password complexity.
To set up the passcheck hook, complete the following steps:
1. Run the pgtle.install_extension SQL code. Modify the SQL code to your
specific needs.
Note: Replace example-password-check-rules with the name of your password
check rules.
SELECT pgtle.install_extension (
'example-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
-- Check password length
IF length(password) < 8 THEN
RAISE EXCEPTION 'Password must be at least 8 characters long.';
END IF;
-- Check common passwords from password from bad_passwords table
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 'Cannot use passwords from the 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 'Cannot use passwords from the common password dictionary';
END IF;
END IF;
-- Check password contains uppercase lowercase number and special character
IF NOT (password ~ '[A-Z]' AND password ~ '[a-z]' AND password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]') THEN
RAISE EXCEPTION 'Password must contain uppercase letters, lowercase letters, numbers, and special characters';
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:
Note: Replace example-password-check-rules with the name of your password
check rules.
CREATE EXTENSION example-password-check-rules;
3. Modify your custom parameter group that's associated with your instance, and
then turn on the pgtle.enable_password_check parameter.
4. Test your password check rules.
Example:
Note: The following example indicates an error because you can't use
passwords from the common password dictionary.
postgres=> CREATE ROLE t_role PASSWORD 'password';
ERROR: Cannot use passwords from the common password dictionary
CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 25 at RAISE
SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"
Example:
Note: The following example indicates an error because the password must be
at least 8 characters long.
postgres=> CREATE ROLE t_role PASSWORD 'pass';
ERROR: Password must be at least 8 characters long.
CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 7 at RAISE
SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"
Example:
Note: The following example indicates an error because the password must
contain uppercase letters, lowercase letters, numbers, and special
characters.
postgres=> CREATE ROLE t_role PASSWORD 'passwordd';
ERROR: Password must contain uppercase letters, lowercase letters, numbers, and special characters
CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 31 at RAISE
SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"
Note: For versions of Amazon RDS for PostgreSQL that don't support TLE, use AWS
Identity and Access Management (IAM) for Amazon RDS or Kerberos authentication.
Also, to restrict password creation to a set of roles or a specific role, see
Delegating and controlling user password management.
RELATED INFORMATION
Working with Trusted Language Extensions for PostgreSQL
Hooks reference for Trusted Language Extensions for PostgreSQL.
FollowShare
Topics
DatabaseAnalyticsMigration & Modernization
Tags
PostgreSQLAmazon Relational Database Service
Language
English
AWS OFFICIALUpdated 3 months ago
No comments
Comment on this article
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
PreviewFormatting guide
ClearPost comment
RELEVANT CONTENT
* AWS RDS Redshift Password Policy
fran keenan
asked 2 months ago
* [IAM/RDS] rds-db:connect authentication to Postgresql with ${aws:username} in
the IAM policy
mbpix4d
asked a month ago
* Amazon RDS for PostgreSQL: Configuration differences with PostgreSQL on EC2
Accepted Answer
Jesus Bernal
asked 4 years ago
* RDS postgresql as the destination for Amazon AppFlow
supriya
asked 9 months ago
* Amazon RDS for PostgreSQL 11.x deprecation
Mario
asked a year ago
* How do I configure the password policy for my Amazon RDS for SQL Server
instance?
AWS OFFICIALUpdated a month ago
* How can I stop Amazon RDS for PostgreSQL from logging my passwords in clear
text in the log files?
AWS OFFICIALUpdated a year ago
* How do I manage large objects in an Amazon RDS for PostgreSQL table?
AWS OFFICIALUpdated 3 months ago
* How do I create another user with the same privileges as a default user for
my Amazon RDS or Aurora DB instance with PostgreSQL?
AWS OFFICIALUpdated a year ago
* AWS re:Post Knowledge Center Spotlight: Amazon RDS for PostgreSQL
EXPERT
Henry Fuentes Jr
published 3 months ago
FEEDBACK
* Privacy|
* Site Terms|
* Cookie Preferences|
* Sitemap|
* Legal|
* © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Community Guidelines