0001-Add-facility-to-store-multiple-password-verifiers.patch
binary/octet-stream
Filename: 0001-Add-facility-to-store-multiple-password-verifiers.patch
Type: binary/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch 0001
Subject: Add facility to store multiple password verifiers
| File | + | − |
|---|---|---|
| contrib/passwordcheck/passwordcheck.c | 73 | 65 |
| doc/src/sgml/catalogs.sgml | 75 | 28 |
| doc/src/sgml/config.sgml | 13 | 4 |
| doc/src/sgml/ref/create_role.sgml | 20 | 3 |
| src/backend/catalog/catalog.c | 4 | 0 |
| src/backend/catalog/Makefile | 2 | 2 |
| src/backend/catalog/system_views.sql | 1 | 1 |
| src/backend/commands/user.c | 225 | 107 |
| src/backend/libpq/crypt.c | 57 | 15 |
| src/backend/nodes/copyfuncs.c | 14 | 0 |
| src/backend/nodes/equalfuncs.c | 12 | 0 |
| src/backend/parser/gram.y | 90 | 8 |
| src/backend/utils/cache/catcache.c | 1 | 0 |
| src/backend/utils/cache/relcache.c | 15 | 8 |
| src/backend/utils/cache/syscache.c | 23 | 0 |
| src/backend/utils/misc/guc.c | 53 | 13 |
| src/backend/utils/misc/postgresql.conf.sample | 1 | 1 |
| src/bin/initdb/initdb.c | 3 | 2 |
| src/bin/pg_dump/pg_dumpall.c | 75 | 2 |
| src/include/catalog/indexing.h | 5 | 0 |
| src/include/catalog/pg_authid.h | 3 | 5 |
| src/include/catalog/pg_auth_verifiers.h | 68 | 0 |
| src/include/commands/user.h | 6 | 5 |
| src/include/nodes/nodes.h | 1 | 0 |
| src/include/nodes/parsenodes.h | 11 | 0 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/include/utils/syscache.h | 2 | 0 |
| src/test/regress/expected/password.out | 104 | 0 |
| src/test/regress/expected/roleattributes.out | 96 | 96 |
| src/test/regress/expected/rules.out | 1 | 1 |
| src/test/regress/expected/sanity_check.out | 1 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/serial_schedule | 1 | 0 |
| src/test/regress/sql/password.sql | 72 | 0 |
From 664348a3afc0d35e227ec43a5558a87e53fe7333 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Fri, 18 Mar 2016 22:15:46 +0900
Subject: [PATCH 1/9] Add facility to store multiple password verifiers
This commit adds a new cluster-wide catalog table called pg_auth_verifiers
extending the existing one-password value per role approach into a facility
able to store multiple passwords formats for one user. This makes easier to
add additional password format support in the future and is a requirement
for the additional of SCRAM-SHA1.
CREATE ROLE and ALTER ROLE are extended with PASSWORD VERIFIERS that allow
a user to set a list of password identifiers at will, something particularly
useful for pg_dump that makes use of it with this commit.
password_encryption is transformed into a list able to use "md5" or "plain",
or even both when CREATE/ALTER ROLE uses neither ENCRYPTED/UNENCRYPTED.
pg_shadown, which had up to now the concept of storing the password plain
or md5 value is now clobbered. Users and client applications are advised to
switch to pg_auth_verifiers instead.
The password check hook has been redesigned to be able to check a list
of passwords instead of a single entry, and the related contrib module
passwordcheck/ is updated respecting the new format.
Extra facility for protocol aging and upgrades will be added in a different
commit.
Regression tests and documentation are added accordingly.
---
contrib/passwordcheck/passwordcheck.c | 138 ++++++-----
doc/src/sgml/catalogs.sgml | 103 +++++---
doc/src/sgml/config.sgml | 17 +-
doc/src/sgml/ref/create_role.sgml | 23 +-
src/backend/catalog/Makefile | 4 +-
src/backend/catalog/catalog.c | 4 +
src/backend/catalog/system_views.sql | 2 +-
src/backend/commands/user.c | 332 +++++++++++++++++---------
src/backend/libpq/crypt.c | 72 ++++--
src/backend/nodes/copyfuncs.c | 14 ++
src/backend/nodes/equalfuncs.c | 12 +
src/backend/parser/gram.y | 98 +++++++-
src/backend/utils/cache/catcache.c | 1 +
src/backend/utils/cache/relcache.c | 23 +-
src/backend/utils/cache/syscache.c | 23 ++
src/backend/utils/misc/guc.c | 66 ++++-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/bin/initdb/initdb.c | 5 +-
src/bin/pg_dump/pg_dumpall.c | 77 +++++-
src/include/catalog/indexing.h | 5 +
src/include/catalog/pg_auth_verifiers.h | 68 ++++++
src/include/catalog/pg_authid.h | 8 +-
src/include/commands/user.h | 11 +-
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 11 +
src/include/parser/kwlist.h | 1 +
src/include/utils/syscache.h | 2 +
src/test/regress/expected/password.out | 104 ++++++++
src/test/regress/expected/roleattributes.out | 192 +++++++--------
src/test/regress/expected/rules.out | 2 +-
src/test/regress/expected/sanity_check.out | 1 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/password.sql | 72 ++++++
34 files changed, 1130 insertions(+), 367 deletions(-)
create mode 100644 src/include/catalog/pg_auth_verifiers.h
create mode 100644 src/test/regress/expected/password.out
create mode 100644 src/test/regress/sql/password.sql
diff --git a/contrib/passwordcheck/passwordcheck.c b/contrib/passwordcheck/passwordcheck.c
index b4c1ce0..13ad053 100644
--- a/contrib/passwordcheck/passwordcheck.c
+++ b/contrib/passwordcheck/passwordcheck.c
@@ -20,9 +20,11 @@
#include <crack.h>
#endif
+#include "catalog/pg_auth_verifiers.h"
#include "commands/user.h"
#include "fmgr.h"
#include "libpq/md5.h"
+#include "nodes/parsenodes.h"
PG_MODULE_MAGIC;
@@ -50,87 +52,93 @@ extern void _PG_init(void);
*/
static void
check_password(const char *username,
- const char *password,
- int password_type,
+ List *passwordVerifiers,
Datum validuntil_time,
bool validuntil_null)
{
int namelen = strlen(username);
- int pwdlen = strlen(password);
+ int pwdlen;
char encrypted[MD5_PASSWD_LEN + 1];
int i;
bool pwd_has_letter,
pwd_has_nonletter;
+ ListCell *l;
- switch (password_type)
+ foreach(l, passwordVerifiers)
{
- case PASSWORD_TYPE_MD5:
-
- /*
- * Unfortunately we cannot perform exhaustive checks on encrypted
- * passwords - we are restricted to guessing. (Alternatively, we
- * could insist on the password being presented non-encrypted, but
- * that has its own security disadvantages.)
- *
- * We only check for username = password.
- */
- if (!pg_md5_encrypt(username, username, namelen, encrypted))
- elog(ERROR, "password encryption failed");
- if (strcmp(password, encrypted) == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("password must not contain user name")));
- break;
-
- case PASSWORD_TYPE_PLAINTEXT:
-
- /*
- * For unencrypted passwords we can perform better checks
- */
-
- /* enforce minimum length */
- if (pwdlen < MIN_PWD_LENGTH)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("password is too short")));
-
- /* check if the password contains the username */
- if (strstr(password, username))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("password must not contain user name")));
-
- /* check if the password contains both letters and non-letters */
- pwd_has_letter = false;
- pwd_has_nonletter = false;
- for (i = 0; i < pwdlen; i++)
- {
+ AuthVerifierSpec *spec = (AuthVerifierSpec *) lfirst(l);
+
+ switch (spec->veriftype)
+ {
+ case AUTH_VERIFIER_MD5:
+
+ /*
+ * Unfortunately we cannot perform exhaustive checks on encrypted
+ * passwords - we are restricted to guessing. (Alternatively, we
+ * could insist on the password being presented non-encrypted, but
+ * that has its own security disadvantages.)
+ *
+ * We only check for username = password.
+ */
+ if (!pg_md5_encrypt(username, username, namelen, encrypted))
+ elog(ERROR, "password encryption failed");
+ if (strcmp(spec->value, encrypted) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("password must not contain user name")));
+ break;
+
+ case AUTH_VERIFIER_PLAIN:
+
/*
- * isalpha() does not work for multibyte encodings but let's
- * consider non-ASCII characters non-letters
+ * For unencrypted passwords we can perform better checks
*/
- if (isalpha((unsigned char) password[i]))
- pwd_has_letter = true;
- else
- pwd_has_nonletter = true;
- }
- if (!pwd_has_letter || !pwd_has_nonletter)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("password must contain both letters and nonletters")));
+ pwdlen = strlen(spec->value);
+
+ /* enforce minimum length */
+ if (pwdlen < MIN_PWD_LENGTH)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("password is too short")));
+
+ /* check if the password contains the username */
+ if (strstr(spec->value, username))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("password must not contain user name")));
+
+ /* check if the password contains both letters and non-letters */
+ pwd_has_letter = false;
+ pwd_has_nonletter = false;
+ for (i = 0; i < pwdlen; i++)
+ {
+ /*
+ * isalpha() does not work for multibyte encodings but let's
+ * consider non-ASCII characters non-letters
+ */
+ if (isalpha((unsigned char) spec->value[i]))
+ pwd_has_letter = true;
+ else
+ pwd_has_nonletter = true;
+ }
+ if (!pwd_has_letter || !pwd_has_nonletter)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("password must contain both letters and nonletters")));
#ifdef USE_CRACKLIB
- /* call cracklib to check password */
- if (FascistCheck(password, CRACKLIB_DICTPATH))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("password is easily cracked")));
+ /* call cracklib to check password */
+ if (FascistCheck(spec->value, CRACKLIB_DICTPATH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("password is easily cracked")));
#endif
- break;
+ break;
- default:
- elog(ERROR, "unrecognized password type: %d", password_type);
- break;
+ default:
+ elog(ERROR, "unrecognized password type: %d", spec->veriftype);
+ break;
+ }
}
/* all checks passed, password is ok */
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 951f59b..cee0c42 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1161,13 +1161,6 @@
</para>
<para>
- Since this catalog contains passwords, it must not be publicly readable.
- <link linkend="view-pg-roles"><structname>pg_roles</structname></link>
- is a publicly readable view on
- <structname>pg_authid</structname> that blanks out the password field.
- </para>
-
- <para>
<xref linkend="user-manag"> contains detailed information about user and
privilege management.
</para>
@@ -1270,21 +1263,6 @@
</row>
<row>
- <entry><structfield>rolpassword</structfield></entry>
- <entry><type>text</type></entry>
- <entry>
- Password (possibly encrypted); null if none. If the password
- is encrypted, this column will begin with the string <literal>md5</>
- followed by a 32-character hexadecimal MD5 hash. The MD5 hash
- will be of the user's password concatenated to their user name.
- For example, if user <literal>joe</> has password <literal>xyzzy</>,
- <productname>PostgreSQL</> will store the md5 hash of
- <literal>xyzzyjoe</>. A password that does not follow that
- format is assumed to be unencrypted.
- </entry>
- </row>
-
- <row>
<entry><structfield>rolvaliduntil</structfield></entry>
<entry><type>timestamptz</type></entry>
<entry>Password expiry time (only used for password authentication);
@@ -1296,6 +1274,77 @@
</sect1>
+ <sect1 id="catalog-pg-auth-verifiers">
+ <title><structname>pg_auth_verifiers</structname></title>
+
+ <indexterm zone="catalog-pg-auth-verifiers">
+ <primary>pg_auth_verifiers</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_auth_verifiers</structname> contains password
+ information for database authorization identifiers (roles).
+ </para>
+
+ <para>
+ Since this catalog contains passwords, it must not be publicly readable.
+ </para>
+
+ <para>
+ Because user identities are cluster-wide,
+ <structname>pg_auth_verifiers</structname>
+ is shared across all databases of a cluster: there is only one
+ copy of <structname>pg_auth_verifiers</structname> per cluster, not
+ one per database.
+ </para>
+
+ <table>
+ <title><structname>pg_auth_verifiers</> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+
+ <row>
+ <entry><structfield>oid</structfield></entry>
+ <entry><type>verroleid</type></entry>
+ <entry>Role identifier OID</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vermethod</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry>
+ <literal>p</> = plain format,
+ <literal>m</> = MD5-encrypted
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>text</structfield></entry>
+ <entry><type>vervalue</type></entry>
+ <entry>
+ Password (possibly encrypted with format defined in
+ <structfield>vermethod</>). If the password
+ is MD5-encrypted, this column will begin with the string <literal>md5</>
+ followed by a 32-character hexadecimal MD5 hash. The MD5 hash
+ will be of the user's password concatenated to their user name.
+ For example, if user <literal>joe</> has password <literal>xyzzy</>,
+ <productname>PostgreSQL</> will store the md5 hash of
+ <literal>xyzzyjoe</>.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
<sect1 id="catalog-pg-auth-members">
<title><structname>pg_auth_members</structname></title>
@@ -9299,9 +9348,7 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
<entry><structfield>passwd</structfield></entry>
<entry><type>text</type></entry>
<entry></entry>
- <entry>Password (possibly encrypted); null if none. See
- <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>
- for details of how encrypted passwords are stored.</entry>
+ <entry>Not the password (always reads as <literal>********</>)</entry>
</row>
<row>
@@ -9771,9 +9818,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</row>
<row>
- <entry><structfield>passwd</structfield></entry>
- <entry><type>text</type></entry>
- <entry>Not the password (always reads as <literal>********</>)</entry>
+ <entry><structfield>passwd</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>Not the password (always reads as <literal>********</>)</entry>
</row>
<row>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7695ec1..80fc479 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1163,20 +1163,29 @@ include_dir 'conf.d'
</varlistentry>
<varlistentry id="guc-password-encryption" xreflabel="password_encryption">
- <term><varname>password_encryption</varname> (<type>boolean</type>)
+ <term><varname>password_encryption</varname> (<type>string</type>)
<indexterm>
<primary><varname>password_encryption</> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
+ Specifies a comma-separated list of password encryption formats.
+ Supported formats are <literal>plain</> and <literal>md5</>.
+ </para>
+
+ <para>
When a password is specified in <xref
linkend="sql-createuser"> or
<xref linkend="sql-alterrole">
without writing either <literal>ENCRYPTED</> or
- <literal>UNENCRYPTED</>, this parameter determines whether the
- password is to be encrypted. The default is <literal>on</>
- (encrypt the password).
+ <literal>UNENCRYPTED</>, this parameter determines the list of
+ encryption formats this password is to be stored as.
+ </para>
+
+ <para>
+ The default is <literal>md5</> (encrypt the password with MD5
+ encryption).
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 38cd4c8..d58431f 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -34,6 +34,7 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
+ | PASSWORD VERIFIERS ( <replaceable class="PARAMETER">verifier_type</replaceable> = '<replaceable class="PARAMETER">password</replaceable>' [, ...] )
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
| IN ROLE <replaceable class="PARAMETER">role_name</replaceable> [, ...]
| IN GROUP <replaceable class="PARAMETER">role_name</replaceable> [, ...]
@@ -211,9 +212,9 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
roles having the <literal>LOGIN</literal> attribute, but you
can nonetheless define one for roles without it.) If you do
not plan to use password authentication you can omit this
- option. If no password is specified, the password will be set
- to null and password authentication will always fail for that
- user. A null password can optionally be written explicitly as
+ option. If no password is specified, no password will be set
+ and password authentication will always fail for that user.
+ A null password can optionally be written explicitly as
<literal>PASSWORD NULL</literal>.
</para>
</listitem>
@@ -245,6 +246,22 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
</varlistentry>
<varlistentry>
+ <term><literal>PASSWORD VERIFIERS</literal> ( <replaceable class="PARAMETER">verifier_type</replaceable> = '<replaceable class="PARAMETER">password</replaceable>' )</term>
+ <listitem>
+ <para>
+ Sets the list of password verifiers for the role. Currently only
+ <literal>md5</>, for MD5-encrypted format, and <literal>plain</>
+ for unencrypted format, can be specified as verifier format type
+ for <literal>verifier_type</>. If the password defined with
+ <literal>plain</> type is already in MD5-encrypted format
+ the password will be stored as MD5-encrypted. If the password defined
+ is in plain-format and that <literal>verifier_type</> is set
+ to <literal>md5</>, the password will be stored as MD5-encrypted.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
<listitem>
<para>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 25130ec..2e695b8 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -35,8 +35,8 @@ POSTGRES_BKI_SRCS = $(addprefix $(top_srcdir)/src/include/catalog/,\
pg_statistic.h pg_rewrite.h pg_trigger.h pg_event_trigger.h pg_description.h \
pg_cast.h pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
pg_database.h pg_db_role_setting.h pg_tablespace.h pg_pltemplate.h \
- pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
- pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
+ pg_auth_verifiers.h pg_authid.h pg_auth_members.h pg_shdepend.h \
+ pg_shdescription.h pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
pg_foreign_table.h pg_policy.h pg_replication_origin.h \
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index bead2c1..7406d2f 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -27,6 +27,7 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
@@ -218,6 +219,7 @@ IsSharedRelation(Oid relationId)
{
/* These are the shared catalogs (look for BKI_SHARED_RELATION) */
if (relationId == AuthIdRelationId ||
+ relationId == AuthVerifRelationId ||
relationId == AuthMemRelationId ||
relationId == DatabaseRelationId ||
relationId == PLTemplateRelationId ||
@@ -231,6 +233,8 @@ IsSharedRelation(Oid relationId)
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
relationId == AuthIdOidIndexId ||
+ relationId == AuthVerifRoleMethodIndexId ||
+ relationId == AuthVerifMethodRoleIndexId ||
relationId == AuthMemRoleMemIndexId ||
relationId == AuthMemMemRoleIndexId ||
relationId == DatabaseNameIndexId ||
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fef67bd..f1a719e 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -40,7 +40,7 @@ CREATE VIEW pg_shadow AS
rolsuper AS usesuper,
rolreplication AS userepl,
rolbypassrls AS usebypassrls,
- rolpassword AS passwd,
+ '********'::text as passwd,
rolvaliduntil::abstime AS valuntil,
setconfig AS useconfig
FROM pg_authid LEFT JOIN pg_db_role_setting s
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 4baeaa2..e8f23e6 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -21,9 +21,11 @@
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
#include "commands/seclabel.h"
@@ -42,9 +44,6 @@
Oid binary_upgrade_next_pg_authid_oid = InvalidOid;
-/* GUC parameter */
-extern bool Password_encryption;
-
/* Hook to check passwords in CreateRole() and AlterRole() */
check_password_hook_type check_password_hook = NULL;
@@ -54,6 +53,10 @@ static void AddRoleMems(const char *rolename, Oid roleid,
static void DelRoleMems(const char *rolename, Oid roleid,
List *memberSpecs, List *memberIds,
bool admin_opt);
+static void FlattenPasswordIdentifiers(List *verifiers, char *rolname);
+static void InsertPasswordIdentifiers(Oid roleid, List *verifiers,
+ char *rolname);
+static void DeletePasswordVerifiers(Oid roleid);
/* Check if current user has createrole privileges */
@@ -78,9 +81,7 @@ CreateRole(CreateRoleStmt *stmt)
Oid roleid;
ListCell *item;
ListCell *option;
- char *password = NULL; /* user password */
- bool encrypt_password = Password_encryption; /* encrypt password? */
- char encrypted_password[MD5_PASSWD_LEN + 1];
+ List *passwordVerifiers = NIL; /* password verifiers */
bool issuper = false; /* Make the user a superuser? */
bool inherit = true; /* Auto inherit privileges? */
bool createrole = false; /* Can this user create roles? */
@@ -96,7 +97,7 @@ CreateRole(CreateRoleStmt *stmt)
char *validUntil = NULL; /* time the login is valid until */
Datum validUntil_datum; /* same, as timestamptz Datum */
bool validUntil_null;
- DefElem *dpassword = NULL;
+ DefElem *dpasswordVerifiers = NULL;
DefElem *dissuper = NULL;
DefElem *dinherit = NULL;
DefElem *dcreaterole = NULL;
@@ -128,19 +129,13 @@ CreateRole(CreateRoleStmt *stmt)
{
DefElem *defel = (DefElem *) lfirst(option);
- if (strcmp(defel->defname, "password") == 0 ||
- strcmp(defel->defname, "encryptedPassword") == 0 ||
- strcmp(defel->defname, "unencryptedPassword") == 0)
+ if (strcmp(defel->defname, "passwordVerifiers") == 0)
{
- if (dpassword)
+ if (dpasswordVerifiers)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
- dpassword = defel;
- if (strcmp(defel->defname, "encryptedPassword") == 0)
- encrypt_password = true;
- else if (strcmp(defel->defname, "unencryptedPassword") == 0)
- encrypt_password = false;
+ dpasswordVerifiers = defel;
}
else if (strcmp(defel->defname, "sysid") == 0)
{
@@ -248,8 +243,8 @@ CreateRole(CreateRoleStmt *stmt)
defel->defname);
}
- if (dpassword && dpassword->arg)
- password = strVal(dpassword->arg);
+ if (dpasswordVerifiers && dpasswordVerifiers->arg)
+ passwordVerifiers = (List *) dpasswordVerifiers->arg;
if (dissuper)
issuper = intVal(dissuper->arg) != 0;
if (dinherit)
@@ -340,12 +335,16 @@ CreateRole(CreateRoleStmt *stmt)
}
/*
- * Call the password checking hook if there is one defined
+ * Flatten list of password verifiers.
+ */
+ FlattenPasswordIdentifiers(passwordVerifiers, stmt->role);
+
+ /*
+ * Call the password checking hook if there is one defined.
*/
- if (check_password_hook && password)
+ if (check_password_hook && passwordVerifiers != NIL)
(*check_password_hook) (stmt->role,
- password,
- isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT,
+ passwordVerifiers,
validUntil_datum,
validUntil_null);
@@ -365,24 +364,6 @@ CreateRole(CreateRoleStmt *stmt)
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
-
- if (password)
- {
- if (!encrypt_password || isMD5(password))
- new_record[Anum_pg_authid_rolpassword - 1] =
- CStringGetTextDatum(password);
- else
- {
- if (!pg_md5_encrypt(password, stmt->role, strlen(stmt->role),
- encrypted_password))
- elog(ERROR, "password encryption failed");
- new_record[Anum_pg_authid_rolpassword - 1] =
- CStringGetTextDatum(encrypted_password);
- }
- }
- else
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
-
new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
@@ -411,6 +392,10 @@ CreateRole(CreateRoleStmt *stmt)
roleid = simple_heap_insert(pg_authid_rel, tuple);
CatalogUpdateIndexes(pg_authid_rel, tuple);
+ /* store password verifiers */
+ if (passwordVerifiers)
+ InsertPasswordIdentifiers(roleid, passwordVerifiers, stmt->role);
+
/*
* Advance command counter so we can see new record; else tests in
* AddRoleMems may fail.
@@ -479,9 +464,7 @@ AlterRole(AlterRoleStmt *stmt)
Form_pg_authid authform;
ListCell *option;
char *rolename = NULL;
- char *password = NULL; /* user password */
- bool encrypt_password = Password_encryption; /* encrypt password? */
- char encrypted_password[MD5_PASSWD_LEN + 1];
+ List *passwordVerifiers = NIL; /* password verifiers */
int issuper = -1; /* Make the user a superuser? */
int inherit = -1; /* Auto inherit privileges? */
int createrole = -1; /* Can this user create roles? */
@@ -494,7 +477,7 @@ AlterRole(AlterRoleStmt *stmt)
Datum validUntil_datum; /* same, as timestamptz Datum */
bool validUntil_null;
int bypassrls = -1;
- DefElem *dpassword = NULL;
+ DefElem *dpasswordVerifiers = NULL;
DefElem *dissuper = NULL;
DefElem *dinherit = NULL;
DefElem *dcreaterole = NULL;
@@ -512,19 +495,13 @@ AlterRole(AlterRoleStmt *stmt)
{
DefElem *defel = (DefElem *) lfirst(option);
- if (strcmp(defel->defname, "password") == 0 ||
- strcmp(defel->defname, "encryptedPassword") == 0 ||
- strcmp(defel->defname, "unencryptedPassword") == 0)
+ if (strcmp(defel->defname, "passwordVerifiers") == 0)
{
- if (dpassword)
+ if (dpasswordVerifiers)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
- dpassword = defel;
- if (strcmp(defel->defname, "encryptedPassword") == 0)
- encrypt_password = true;
- else if (strcmp(defel->defname, "unencryptedPassword") == 0)
- encrypt_password = false;
+ dpasswordVerifiers = defel;
}
else if (strcmp(defel->defname, "superuser") == 0)
{
@@ -612,8 +589,8 @@ AlterRole(AlterRoleStmt *stmt)
defel->defname);
}
- if (dpassword && dpassword->arg)
- password = strVal(dpassword->arg);
+ if (dpasswordVerifiers && dpasswordVerifiers->arg)
+ passwordVerifiers = (List *) dpasswordVerifiers->arg;
if (dissuper)
issuper = intVal(dissuper->arg);
if (dinherit)
@@ -687,7 +664,7 @@ AlterRole(AlterRoleStmt *stmt)
!dconnlimit &&
!rolemembers &&
!validUntil &&
- dpassword &&
+ dpasswordVerifiers &&
roleid == GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -712,12 +689,16 @@ AlterRole(AlterRoleStmt *stmt)
}
/*
- * Call the password checking hook if there is one defined
+ * Flatten list of password verifiers.
*/
- if (check_password_hook && password)
+ FlattenPasswordIdentifiers(passwordVerifiers, rolename);
+
+ /*
+ * Call the password checking hook if there is one defined.
+ */
+ if (check_password_hook && passwordVerifiers)
(*check_password_hook) (rolename,
- password,
- isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT,
+ passwordVerifiers,
validUntil_datum,
validUntil_null);
@@ -773,30 +754,6 @@ AlterRole(AlterRoleStmt *stmt)
new_record_repl[Anum_pg_authid_rolconnlimit - 1] = true;
}
- /* password */
- if (password)
- {
- if (!encrypt_password || isMD5(password))
- new_record[Anum_pg_authid_rolpassword - 1] =
- CStringGetTextDatum(password);
- else
- {
- if (!pg_md5_encrypt(password, rolename, strlen(rolename),
- encrypted_password))
- elog(ERROR, "password encryption failed");
- new_record[Anum_pg_authid_rolpassword - 1] =
- CStringGetTextDatum(encrypted_password);
- }
- new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
- }
-
- /* unset password */
- if (dpassword && dpassword->arg == NULL)
- {
- new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
- }
-
/* valid until */
new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
@@ -821,6 +778,21 @@ AlterRole(AlterRoleStmt *stmt)
heap_freetuple(new_tuple);
/*
+ * Update password verifiers. The old entries are completely cleaned up
+ * and are updated by what is wanted through this command.
+ */
+ if (passwordVerifiers)
+ {
+ DeletePasswordVerifiers(roleid);
+ CommandCounterIncrement();
+ InsertPasswordIdentifiers(roleid, passwordVerifiers, rolename);
+ }
+
+ /* remove password verifiers */
+ if (dpasswordVerifiers && dpasswordVerifiers->arg == NULL)
+ DeletePasswordVerifiers(roleid);
+
+ /*
* Advance command counter so we can see new record; else tests in
* AddRoleMems may fail.
*/
@@ -1070,8 +1042,10 @@ DropRole(DropRoleStmt *stmt)
systable_endscan(sscan);
/*
- * Remove any comments or security labels on this role.
+ * Remove any comments, password verifiers or security labels on this
+ * role.
*/
+ DeletePasswordVerifiers(roleid);
DeleteSharedComments(roleid, AuthIdRelationId);
DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
@@ -1106,11 +1080,11 @@ ObjectAddress
RenameRole(const char *oldname, const char *newname)
{
HeapTuple oldtuple,
- newtuple;
+ newtuple,
+ authtuple;
TupleDesc dsc;
- Relation rel;
- Datum datum;
- bool isnull;
+ Relation pg_authid_rel,
+ pg_auth_verifiers_rel;
Datum repl_val[Natts_pg_authid];
bool repl_null[Natts_pg_authid];
bool repl_repl[Natts_pg_authid];
@@ -1118,8 +1092,10 @@ RenameRole(const char *oldname, const char *newname)
Oid roleid;
ObjectAddress address;
- rel = heap_open(AuthIdRelationId, RowExclusiveLock);
- dsc = RelationGetDescr(rel);
+ pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock);
+ pg_auth_verifiers_rel = heap_open(AuthVerifRelationId, RowExclusiveLock);
+
+ dsc = RelationGetDescr(pg_authid_rel);
oldtuple = SearchSysCache1(AUTHNAME, CStringGetDatum(oldname));
if (!HeapTupleIsValid(oldtuple))
@@ -1179,22 +1155,10 @@ RenameRole(const char *oldname, const char *newname)
CStringGetDatum(newname));
repl_null[Anum_pg_authid_rolname - 1] = false;
- datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull);
-
- if (!isnull && isMD5(TextDatumGetCString(datum)))
- {
- /* MD5 uses the username as salt, so just clear it on a rename */
- repl_repl[Anum_pg_authid_rolpassword - 1] = true;
- repl_null[Anum_pg_authid_rolpassword - 1] = true;
-
- ereport(NOTICE,
- (errmsg("MD5 password cleared because of role rename")));
- }
-
newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
- simple_heap_update(rel, &oldtuple->t_self, newtuple);
+ simple_heap_update(pg_authid_rel, &oldtuple->t_self, newtuple);
- CatalogUpdateIndexes(rel, newtuple);
+ CatalogUpdateIndexes(pg_authid_rel, newtuple);
InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
@@ -1202,10 +1166,23 @@ RenameRole(const char *oldname, const char *newname)
ReleaseSysCache(oldtuple);
+ /* look for md5 entry in pg_auth_verifiers and remove it if it exists */
+ authtuple = SearchSysCache2(AUTHVERIFROLEMETH,
+ ObjectIdGetDatum(roleid),
+ CharGetDatum(AUTH_VERIFIER_MD5));
+ if (HeapTupleIsValid(authtuple))
+ {
+ simple_heap_delete(pg_auth_verifiers_rel, &authtuple->t_self);
+ ereport(NOTICE,
+ (errmsg("MD5 password cleared because of role rename")));
+ ReleaseSysCache(authtuple);
+ }
+
/*
- * Close pg_authid, but keep lock till commit.
+ * Close pg_authid and pg_auth_verifiers, but keep lock till commit.
*/
- heap_close(rel, NoLock);
+ heap_close(pg_auth_verifiers_rel, NoLock);
+ heap_close(pg_authid_rel, NoLock);
return address;
}
@@ -1611,3 +1588,144 @@ DelRoleMems(const char *rolename, Oid roleid,
*/
heap_close(pg_authmem_rel, NoLock);
}
+
+/*
+ * FlattenPasswordIdentifiers
+ * Make list of password verifier types and values consistent with input.
+ */
+static void
+FlattenPasswordIdentifiers(List *verifiers, char *rolname)
+{
+ ListCell *l;
+
+ /*
+ * This machinery is here for for sanity checks and backward
+ * compatibility with pre-9.6 instances of Postgres, an md5
+ * hash passed as a plain verifier should still be treated as
+ * an MD5 entry.
+ */
+ foreach(l, verifiers)
+ {
+ AuthVerifierSpec *spec = (AuthVerifierSpec *) lfirst(l);
+
+ if (spec->value == NULL)
+ continue;
+
+ switch (spec->veriftype)
+ {
+ case AUTH_VERIFIER_MD5:
+ /*
+ * Check if given value for an MD5 verifier is adapted and
+ * do conversion as needed. If an MD5 password is provided
+ * but that the verifier has a plain format switch type of
+ * verifier accordingly.
+ */
+ if (!isMD5(spec->value))
+ {
+ char encrypted_passwd[MD5_PASSWD_LEN + 1];
+
+ if (!pg_md5_encrypt(spec->value, rolname,
+ strlen(rolname),
+ encrypted_passwd))
+ elog(ERROR, "password encryption failed");
+ spec->value = pstrdup(encrypted_passwd);
+ }
+ break;
+
+ case AUTH_VERIFIER_PLAIN:
+ if (isMD5(spec->value))
+ spec->veriftype = AUTH_VERIFIER_MD5;
+ break;
+
+ default:
+ Assert(0); /* should not happen */
+ }
+ }
+}
+
+/*
+ * InsertPasswordIdentifiers
+ * Add list of given identifiers into pg_auth_verifiers for given role.
+ */
+static void
+InsertPasswordIdentifiers(Oid roleid, List *verifiers, char *rolname)
+{
+ ListCell *l;
+ Relation pg_auth_verifiers_rel;
+ TupleDesc pg_auth_verifiers_dsc;
+
+ pg_auth_verifiers_rel = heap_open(AuthVerifRelationId, RowExclusiveLock);
+ pg_auth_verifiers_dsc = RelationGetDescr(pg_auth_verifiers_rel);
+
+ foreach(l, verifiers)
+ {
+ Datum new_record[Natts_pg_auth_verifiers];
+ bool new_record_nulls[Natts_pg_auth_verifiers];
+ HeapTuple tuple;
+ AuthVerifierSpec *spec = (AuthVerifierSpec *) lfirst(l);
+
+ /* Move on if no verifier value define */
+ if (spec->value == NULL)
+ continue;
+
+ /* Build tuple and insert it */
+ MemSet(new_record, 0, sizeof(new_record));
+ MemSet(new_record_nulls, false, sizeof(new_record_nulls));
+
+ new_record[Anum_pg_auth_verifiers_verroleid - 1] = ObjectIdGetDatum(roleid);
+ new_record[Anum_pg_auth_verifiers_vermethod - 1] =
+ CharGetDatum(spec->veriftype);
+
+ new_record[Anum_pg_auth_verifiers_vervalue - 1] =
+ CStringGetTextDatum(spec->value);
+
+ tuple = heap_form_tuple(pg_auth_verifiers_dsc,
+ new_record, new_record_nulls);
+
+ simple_heap_insert(pg_auth_verifiers_rel, tuple);
+ CatalogUpdateIndexes(pg_auth_verifiers_rel, tuple);
+
+ /* CCI after each change */
+ CommandCounterIncrement();
+ }
+
+ /* Keep locks until the end of transaction */
+ heap_close(pg_auth_verifiers_rel, NoLock);
+}
+
+/*
+ * DeletePasswordVerifiers
+ * Remove all password identifiers for given role.
+ */
+static void
+DeletePasswordVerifiers(Oid roleid)
+{
+ Relation pg_auth_verifiers_rel;
+ ScanKeyData scankey;
+ SysScanDesc sscan;
+ HeapTuple tmp_tuple;
+
+ pg_auth_verifiers_rel = heap_open(AuthVerifRelationId, RowExclusiveLock);
+ /*
+ * Remove role entries from pg_auth_verifiers table. All the tuples that
+ * are similar to the role dropped need to be removed.
+ */
+ ScanKeyInit(&scankey,
+ Anum_pg_auth_verifiers_verroleid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ sscan = systable_beginscan(pg_auth_verifiers_rel,
+ AuthVerifRoleMethodIndexId,
+ true, NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
+ {
+ simple_heap_delete(pg_auth_verifiers_rel, &tmp_tuple->t_self);
+ }
+
+ systable_endscan(sscan);
+
+ /* keep lock until the end of transaction */
+ heap_close(pg_auth_verifiers_rel, NoLock);
+}
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index d79f5a2..e41b837 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -20,14 +20,46 @@
#include <crypt.h>
#endif
+#include "access/htup_details.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_type.h"
#include "libpq/crypt.h"
#include "libpq/md5.h"
#include "miscadmin.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
+/*
+ * Get verifier stored in pg_auth_verifiers tuple, for given authentication
+ * method.
+ */
+static char *
+get_role_verifier(Oid roleid, const char method)
+{
+ HeapTuple tuple;
+ Datum verifier_datum;
+ char *verifier;
+ bool isnull;
+
+ /* Now attempt to grab the verifier value */
+ tuple = SearchSysCache2(AUTHVERIFROLEMETH,
+ ObjectIdGetDatum(roleid),
+ CharGetDatum(method));
+ if (!HeapTupleIsValid(tuple))
+ return NULL; /* no verifier available */
+
+ verifier_datum = SysCacheGetAttr(AUTHVERIFROLEMETH, tuple,
+ Anum_pg_auth_verifiers_vervalue,
+ &isnull);
+ verifier = TextDatumGetCString(verifier_datum);
+
+ ReleaseSysCache(tuple);
+
+ return verifier;
+}
/*
* Check given password for given user, and return STATUS_OK or STATUS_ERROR.
@@ -39,13 +71,15 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
char **logdetail)
{
int retval = STATUS_ERROR;
- char *shadow_pass,
+ char *verifier,
*crypt_pwd;
TimestampTz vuntil = 0;
+ bool verifier_is_md5;
char *crypt_client_pass = client_pass;
HeapTuple roleTup;
Datum datum;
bool isnull;
+ Oid roleid;
/* Get role info from pg_authid */
roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -56,16 +90,24 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
return STATUS_ERROR; /* no such user */
}
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolpassword, &isnull);
- if (isnull)
+ verifier_is_md5 = true;
+
+ roleid = HeapTupleGetOid(roleTup);
+ verifier = get_role_verifier(roleid, AUTH_VERIFIER_MD5);
+ if (verifier == NULL)
{
- ReleaseSysCache(roleTup);
- *logdetail = psprintf(_("User \"%s\" has no password assigned."),
- role);
- return STATUS_ERROR; /* user has no password */
+ /* we can also use a plaintext password, by creating the hash from it */
+ verifier_is_md5 = false;
+ verifier = get_role_verifier(roleid, AUTH_VERIFIER_PLAIN);
+
+ if (verifier == NULL)
+ {
+ *logdetail = psprintf(_("User \"%s\" has no password assigned for authentication method \"%s\"."),
+ role, AUTH_VERIFIER_FULL_MD5);
+ ReleaseSysCache(roleTup);
+ return STATUS_ERROR;
+ }
}
- shadow_pass = TextDatumGetCString(datum);
datum = SysCacheGetAttr(AUTHNAME, roleTup,
Anum_pg_authid_rolvaliduntil, &isnull);
@@ -74,7 +116,7 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
ReleaseSysCache(roleTup);
- if (*shadow_pass == '\0')
+ if (*verifier == '\0')
{
*logdetail = psprintf(_("User \"%s\" has an empty password."),
role);
@@ -92,10 +134,10 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
{
case uaMD5:
crypt_pwd = palloc(MD5_PASSWD_LEN + 1);
- if (isMD5(shadow_pass))
+ if (verifier_is_md5)
{
/* stored password already encrypted, only do salt */
- if (!pg_md5_encrypt(shadow_pass + strlen("md5"),
+ if (!pg_md5_encrypt(verifier + strlen("md5"),
port->md5Salt,
sizeof(port->md5Salt), crypt_pwd))
{
@@ -108,7 +150,7 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
/* stored password is plain, double-encrypt */
char *crypt_pwd2 = palloc(MD5_PASSWD_LEN + 1);
- if (!pg_md5_encrypt(shadow_pass,
+ if (!pg_md5_encrypt(verifier,
port->user_name,
strlen(port->user_name),
crypt_pwd2))
@@ -130,7 +172,7 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
}
break;
default:
- if (isMD5(shadow_pass))
+ if (verifier_is_md5)
{
/* Encrypt user-supplied password to match stored MD5 */
crypt_client_pass = palloc(MD5_PASSWD_LEN + 1);
@@ -143,7 +185,7 @@ md5_crypt_verify(const Port *port, const char *role, char *client_pass,
return STATUS_ERROR;
}
}
- crypt_pwd = shadow_pass;
+ crypt_pwd = verifier;
break;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index df7c2fa..a2cb4b5 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2696,6 +2696,17 @@ _copyRoleSpec(const RoleSpec *from)
return newnode;
}
+static AuthVerifierSpec *
+_copyAuthVerifierSpec(const AuthVerifierSpec *from)
+{
+ AuthVerifierSpec *newnode = makeNode(AuthVerifierSpec);
+
+ COPY_SCALAR_FIELD(veriftype);
+ COPY_STRING_FIELD(value);
+
+ return newnode;
+}
+
static Query *
_copyQuery(const Query *from)
{
@@ -5011,6 +5022,9 @@ copyObject(const void *from)
case T_RoleSpec:
retval = _copyRoleSpec(from);
break;
+ case T_AuthVerifierSpec:
+ retval = _copyAuthVerifierSpec(from);
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(from));
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b9c3959..8087c39 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2594,6 +2594,15 @@ _equalRoleSpec(const RoleSpec *a, const RoleSpec *b)
return true;
}
+static bool
+_equalAuthVerifierSpec(const AuthVerifierSpec *a, const AuthVerifierSpec *b)
+{
+ COMPARE_SCALAR_FIELD(veriftype);
+ COMPARE_STRING_FIELD(value);
+
+ return true;
+}
+
/*
* Stuff from pg_list.h
*/
@@ -3338,6 +3347,9 @@ equal(const void *a, const void *b)
case T_RoleSpec:
retval = _equalRoleSpec(a, b);
break;
+ case T_AuthVerifierSpec:
+ retval = _equalAuthVerifierSpec(a, b);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b9aeb31..e1039c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -51,15 +51,18 @@
#include "catalog/index.h"
#include "catalog/namespace.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_trigger.h"
#include "commands/defrem.h"
#include "commands/trigger.h"
+#include "commands/user.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/gramparse.h"
#include "parser/parser.h"
#include "parser/parse_expr.h"
#include "storage/lmgr.h"
+#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/datetime.h"
#include "utils/numeric.h"
@@ -145,6 +148,7 @@ static Node *makeNullAConst(int location);
static Node *makeAConst(Value *v, int location);
static Node *makeBoolAConst(bool state, int location);
static Node *makeRoleSpec(RoleSpecType type, int location);
+static Node *makeAuthVerifierSpec(char type, char *password);
static void check_qualified_name(List *names, core_yyscan_t yyscanner);
static List *check_func_name(List *names, core_yyscan_t yyscanner);
static List *check_indirection(List *indirection, core_yyscan_t yyscanner);
@@ -370,6 +374,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
create_generic_options alter_generic_options
relation_expr_list dostmt_opt_list
transform_element_list transform_type_list
+ auth_verifier_list
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
@@ -497,6 +502,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> createdb_opt_name
%type <node> var_value zone_value
%type <node> auth_ident RoleSpec opt_granted_by
+%type <node> AuthVerifierSpec
%type <keyword> unreserved_keyword type_func_name_keyword
%type <keyword> col_name_keyword reserved_keyword
@@ -638,7 +644,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VERBOSE VERIFIERS VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -918,25 +924,86 @@ AlterOptRoleList:
| /* EMPTY */ { $$ = NIL; }
;
+auth_verifier_list:
+ AuthVerifierSpec
+ { $$ = list_make1((Node*)$1); }
+ | auth_verifier_list ',' AuthVerifierSpec
+ { $$ = lappend($1, (Node *)$3); }
+
+AuthVerifierSpec:
+ NonReservedWord '=' Sconst
+ {
+ char type;
+
+ if (strcmp($1, AUTH_VERIFIER_FULL_MD5) == 0)
+ type = AUTH_VERIFIER_MD5;
+ else if (strcmp($1, AUTH_VERIFIER_FULL_PLAIN) == 0)
+ type = AUTH_VERIFIER_PLAIN;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized authorization verifier option \"%s\"", $1),
+ parser_errposition(@1)));
+ $$ = (Node *) makeAuthVerifierSpec(type, $3);
+ }
+
AlterOptRoleElem:
PASSWORD Sconst
{
- $$ = makeDefElem("password",
- (Node *)makeString($2));
+ char *rawstring = pstrdup(Password_encryption);
+ List *elemlist;
+ ListCell *l;
+ List *result = NIL;
+
+ if (!SplitIdentifierString(rawstring, ',', &elemlist))
+ Assert(false); /* should not happen */
+
+ foreach(l, elemlist)
+ {
+ char *meth_name = (char *) lfirst(l);
+ char veriftype;
+ AuthVerifierSpec *n;
+
+ if (strcmp(meth_name, AUTH_VERIFIER_FULL_MD5) == 0)
+ veriftype = AUTH_VERIFIER_MD5;
+ else if (strcmp(meth_name, AUTH_VERIFIER_FULL_PLAIN) == 0)
+ veriftype = AUTH_VERIFIER_PLAIN;
+ else
+ Assert(false); /* should not happen */
+ n = (AuthVerifierSpec *)
+ makeAuthVerifierSpec(veriftype, $2);
+ result = lappend(result, (Node *)n);
+ }
+ pfree(rawstring);
+ list_free(elemlist);
+
+ $$ = makeDefElem("passwordVerifiers",
+ (Node *) result);
}
| PASSWORD NULL_P
{
- $$ = makeDefElem("password", NULL);
+ AuthVerifierSpec *n = (AuthVerifierSpec *)
+ makeAuthVerifierSpec(AUTH_VERIFIER_PLAIN, NULL);
+ $$ = makeDefElem("passwordVerifiers",
+ (Node *)list_make1((Node *)n));
}
| ENCRYPTED PASSWORD Sconst
{
- $$ = makeDefElem("encryptedPassword",
- (Node *)makeString($3));
+ AuthVerifierSpec *n = (AuthVerifierSpec *)
+ makeAuthVerifierSpec(AUTH_VERIFIER_MD5, $3);
+ $$ = makeDefElem("passwordVerifiers",
+ (Node *)list_make1((Node *)n));
}
| UNENCRYPTED PASSWORD Sconst
{
- $$ = makeDefElem("unencryptedPassword",
- (Node *)makeString($3));
+ AuthVerifierSpec *n = (AuthVerifierSpec *)
+ makeAuthVerifierSpec(AUTH_VERIFIER_PLAIN, $3);
+ $$ = makeDefElem("passwordVerifiers",
+ (Node *)list_make1((Node *)n));
+ }
+ | PASSWORD VERIFIERS '(' auth_verifier_list ')'
+ {
+ $$ = makeDefElem("passwordVerifiers", (Node *)$4);
}
| INHERIT
{
@@ -13902,6 +13969,7 @@ unreserved_keyword:
| VALIDATOR
| VALUE_P
| VARYING
+ | VERIFIERS
| VERSION_P
| VIEW
| VIEWS
@@ -14296,6 +14364,20 @@ makeRoleSpec(RoleSpecType type, int location)
return (Node *) spec;
}
+/* makeAuthVerifierSpec
+ * Create a AuthVerifierSpec for the given type.
+ */
+static Node *
+makeAuthVerifierSpec(char type, char *password)
+{
+ AuthVerifierSpec *spec = makeNode(AuthVerifierSpec);
+
+ spec->veriftype = type;
+ spec->value = password;
+
+ return (Node *) spec;
+}
+
/* check_qualified_name --- check the result of qualified_name production
*
* It's easiest to let the grammar production for qualified_name allow
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index e929616..bb83df2 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1073,6 +1073,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
case AUTHNAME:
case AUTHOID:
case AUTHMEMMEMROLE:
+ case AUTHVERIFROLEMETH:
/*
* Protect authentication lookups occurring before relcache has
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 130c06d..c1b6330 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -45,6 +45,7 @@
#include "catalog/pg_attrdef.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
@@ -98,6 +99,7 @@ static const FormData_pg_attribute Desc_pg_type[Natts_pg_type] = {Schema_pg_type
static const FormData_pg_attribute Desc_pg_database[Natts_pg_database] = {Schema_pg_database};
static const FormData_pg_attribute Desc_pg_authid[Natts_pg_authid] = {Schema_pg_authid};
static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_members] = {Schema_pg_auth_members};
+static const FormData_pg_attribute Desc_pg_auth_verifiers[Natts_pg_auth_verifiers] = {Schema_pg_auth_verifiers};
static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] = {Schema_pg_index};
static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel] = {Schema_pg_shseclabel};
@@ -1567,8 +1569,8 @@ LookupOpclassInfo(Oid operatorClassOid,
* catalogs.
*
* formrdesc is currently used for: pg_database, pg_authid, pg_auth_members,
- * pg_shseclabel, pg_class, pg_attribute, pg_proc, and pg_type
- * (see RelationCacheInitializePhase2/3).
+ * pg_auth_verifiers pg_shseclabel, pg_class, pg_attribute, pg_proc, and
+ * pg_type (see RelationCacheInitializePhase2/3).
*
* Note that these catalogs can't have constraints (except attnotnull),
* default values, rules, or triggers, since we don't cope with any of that.
@@ -2871,6 +2873,7 @@ RelationBuildLocalRelation(const char *relname,
case DatabaseRelationId:
case AuthIdRelationId:
case AuthMemRelationId:
+ case AuthVerifRelationId:
case RelationRelationId:
case AttributeRelationId:
case ProcedureRelationId:
@@ -3257,10 +3260,12 @@ RelationCacheInitializePhase2(void)
true, Natts_pg_authid, Desc_pg_authid);
formrdesc("pg_auth_members", AuthMemRelation_Rowtype_Id, true,
false, Natts_pg_auth_members, Desc_pg_auth_members);
+ formrdesc("pg_auth_verifiers", AuthVerifRelation_Rowtype_Id, true,
+ false, Natts_pg_auth_verifiers, Desc_pg_auth_verifiers);
formrdesc("pg_shseclabel", SharedSecLabelRelation_Rowtype_Id, true,
false, Natts_pg_shseclabel, Desc_pg_shseclabel);
-#define NUM_CRITICAL_SHARED_RELS 4 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_RELS 5 /* fix if you change list above */
}
MemoryContextSwitchTo(oldcxt);
@@ -3380,10 +3385,10 @@ RelationCacheInitializePhase3(void)
* initial lookup of MyDatabaseId, without which we'll never find any
* non-shared catalogs at all. Autovacuum calls InitPostgres with a
* database OID, so it instead depends on DatabaseOidIndexId. We also
- * need to nail up some indexes on pg_authid and pg_auth_members for use
- * during client authentication. SharedSecLabelObjectIndexId isn't
- * critical for the core system, but authentication hooks might be
- * interested in it.
+ * need to nail up some indexes on pg_authid, pg_auth_verifiers and
+ * pg_auth_members for use during client authentication.
+ * SharedSecLabelObjectIndexId isn't critical for the core system, but
+ * authentication hooks might be interested in it.
*/
if (!criticalSharedRelcachesBuilt)
{
@@ -3397,10 +3402,12 @@ RelationCacheInitializePhase3(void)
AuthIdRelationId);
load_critical_index(AuthMemMemRoleIndexId,
AuthMemRelationId);
+ load_critical_index(AuthVerifRoleMethodIndexId,
+ AuthVerifRelationId);
load_critical_index(SharedSecLabelObjectIndexId,
SharedSecLabelRelationId);
-#define NUM_CRITICAL_SHARED_INDEXES 6 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_INDEXES 7 /* fix if you change list above */
criticalSharedRelcachesBuilt = true;
}
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 65ffe84..17390b4 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -28,6 +28,7 @@
#include "catalog/pg_amop.h"
#include "catalog/pg_amproc.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_verifiers.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_cast.h"
#include "catalog/pg_collation.h"
@@ -248,6 +249,28 @@ static const struct cachedesc cacheinfo[] = {
},
8
},
+ {AuthVerifRelationId, /* AUTHVERIFMETHROLE */
+ AuthVerifMethodRoleIndexId,
+ 2,
+ {
+ Anum_pg_auth_verifiers_vermethod,
+ Anum_pg_auth_verifiers_verroleid,
+ 0,
+ 0
+ },
+ 4
+ },
+ {AuthVerifRelationId, /* AUTHVERIFROLEMETH */
+ AuthVerifRoleMethodIndexId,
+ 2,
+ {
+ Anum_pg_auth_verifiers_verroleid,
+ Anum_pg_auth_verifiers_vermethod,
+ 0,
+ 0
+ },
+ 4
+ },
{
CastRelationId, /* CASTSOURCETARGET */
CastSourceTargetIndexId,
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a325943..ae880cb 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -32,6 +32,7 @@
#include "access/twophase.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/pg_auth_verifiers.h"
#include "commands/async.h"
#include "commands/prepare.h"
#include "commands/vacuum.h"
@@ -179,6 +180,8 @@ static void assign_pgstat_temp_directory(const char *newval, void *extra);
static bool check_application_name(char **newval, void **extra, GucSource source);
static void assign_application_name(const char *newval, void *extra);
static bool check_cluster_name(char **newval, void **extra, GucSource source);
+static bool check_password_encryption(char **newval, void **extra,
+ GucSource source);
static const char *show_unix_socket_permissions(void);
static const char *show_log_file_mode(void);
@@ -422,8 +425,6 @@ bool check_function_bodies = true;
bool default_with_oids = false;
bool SQL_inheritance = true;
-bool Password_encryption = true;
-
int log_min_error_statement = ERROR;
int log_min_messages = WARNING;
int client_min_messages = NOTICE;
@@ -435,6 +436,8 @@ int temp_file_limit = -1;
int num_temp_buffers = 1024;
+char *Password_encryption;
+
char *cluster_name = "";
char *ConfigFileName;
char *HbaFileName;
@@ -1309,17 +1312,6 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
- {"password_encryption", PGC_USERSET, CONN_AUTH_SECURITY,
- gettext_noop("Encrypt passwords."),
- gettext_noop("When a password is specified in CREATE USER or "
- "ALTER USER without writing either ENCRYPTED or UNENCRYPTED, "
- "this parameter determines whether the password is to be encrypted.")
- },
- &Password_encryption,
- true,
- NULL, NULL, NULL
- },
- {
{"transform_null_equals", PGC_USERSET, COMPAT_OPTIONS_CLIENT,
gettext_noop("Treats \"expr=NULL\" as \"expr IS NULL\"."),
gettext_noop("When turned on, expressions of the form expr = NULL "
@@ -3395,6 +3387,19 @@ static struct config_string ConfigureNamesString[] =
},
{
+ {"password_encryption", PGC_USERSET, CONN_AUTH_SECURITY,
+ gettext_noop("List of password encryption methods."),
+ gettext_noop("When a password is specified in CREATE USER or "
+ "ALTER USER without writing either ENCRYPTED or UNENCRYPTED, "
+ "this parameter determines how the password is to be encrypted."),
+ GUC_LIST_INPUT
+ },
+ &Password_encryption,
+ "md5",
+ check_password_encryption, NULL, NULL
+ },
+
+ {
{"ssl_cert_file", PGC_POSTMASTER, CONN_AUTH_SECURITY,
gettext_noop("Location of the SSL server certificate file."),
NULL
@@ -10229,6 +10234,41 @@ check_cluster_name(char **newval, void **extra, GucSource source)
return true;
}
+static bool
+check_password_encryption(char **newval, void **extra, GucSource source)
+{
+ char *rawstring = pstrdup(*newval); /* get copy of list string */
+ List *elemlist;
+ ListCell *l;
+
+ if (!SplitIdentifierString(rawstring, ',', &elemlist))
+ {
+ /* syntax error in list */
+ pfree(rawstring);
+ list_free(elemlist);
+ Assert(false);
+ return false; /* GUC machinery should have already complained */
+ }
+
+ /* Check that only supported formats are listed */
+ foreach(l, elemlist)
+ {
+ char *encryption_name = (char *) lfirst(l);
+
+ if (strcmp(encryption_name, AUTH_VERIFIER_FULL_MD5) != 0 &&
+ strcmp(encryption_name, AUTH_VERIFIER_FULL_PLAIN) != 0)
+ {
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+ }
+
+ pfree(rawstring);
+ list_free(elemlist);
+ return true;
+}
+
static const char *
show_unix_socket_permissions(void)
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 773b4e8..bff25ca 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -87,7 +87,7 @@
#ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
-#password_encryption = on
+#password_encryption = 'md5'
#db_user_namespace = off
#row_security = on
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ed3ba7b..5315db7 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1530,10 +1530,11 @@ setup_auth(FILE *cmdfd)
const char *const * line;
static const char *const pg_authid_setup[] = {
/*
- * The authid table shouldn't be readable except through views, to
- * ensure passwords are not publicly visible.
+ * The authorization tables shouldn't be readable except through
+ * views, to ensure password data are not publicly visible.
*/
"REVOKE ALL on pg_authid FROM public;\n\n",
+ "REVOKE ALL on pg_auth_verifiers FROM public;\n\n",
NULL
};
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index be6b4a8..4bf36c4 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -663,8 +663,22 @@ dumpRoles(PGconn *conn)
i_is_current_user;
int i;
- /* note: rolconfig is dumped later */
- if (server_version >= 90500)
+ /*
+ * Note: rolconfig is dumped later. In 9.6 and above, password
+ * information is dumped later on.
+ */
+ if (server_version >= 90600)
+ printfPQExpBuffer(buf,
+ "SELECT oid, rolname, rolsuper, rolinherit, "
+ "rolcreaterole, rolcreatedb, "
+ "rolcanlogin, rolconnlimit, "
+ "null::text as rolpassword, "
+ "rolvaliduntil, rolreplication, rolbypassrls, "
+ "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
+ "rolname = current_user AS is_current_user "
+ "FROM pg_authid "
+ "ORDER BY 2");
+ else if (server_version >= 90500)
printfPQExpBuffer(buf,
"SELECT oid, rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, "
@@ -869,6 +883,65 @@ dumpRoles(PGconn *conn)
PQclear(res);
+ /*
+ * Dump password configuration for all roles.
+ */
+ if (server_version >= 90600)
+ {
+ char *current_user = NULL;
+ bool first_elt = true;
+ res = executeQuery(conn,
+ "SELECT a.rolname, v.vermethod, v.vervalue "
+ "FROM pg_auth_verifiers AS v "
+ "LEFT JOIN pg_authid AS a ON (v.verroleid = a.oid) "
+ "ORDER BY rolname;");
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ char *user_name = PQgetvalue(res, i, 0);
+ char verifier_meth = *PQgetvalue(res, i, 1);
+ char *verifier_value = PQgetvalue(res, i, 2);
+
+ /* Switch to new ALTER ROLE query when a different user is found */
+ if (current_user == NULL ||
+ strcmp(user_name, current_user) != 0)
+ {
+ /* Finish last query */
+ if (current_user != NULL)
+ {
+ appendPQExpBufferStr(buf, ");\n");
+ fprintf(OPF, "%s", buf->data);
+ }
+
+ resetPQExpBuffer(buf);
+
+ if (current_user)
+ pg_free(current_user);
+ current_user = pg_strdup(user_name);
+ first_elt = true;
+ appendPQExpBuffer(buf, "ALTER ROLE %s PASSWORD VERIFIERS (",
+ current_user);
+ }
+
+ if (first_elt)
+ first_elt = false;
+ else
+ appendPQExpBufferStr(buf, ", ");
+
+ if (verifier_meth == 'm')
+ appendPQExpBufferStr(buf, "md5 = ");
+ else if (verifier_meth == 'p')
+ appendPQExpBufferStr(buf, "plain = ");
+ appendStringLiteralConn(buf, verifier_value, conn);
+ }
+ if (current_user != NULL)
+ {
+ appendPQExpBufferStr(buf, ");\n");
+ fprintf(OPF, "%s", buf->data);
+ }
+ }
+
+
fprintf(OPF, "\n\n");
destroyPQExpBuffer(buf);
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index ab2c1a8..5a9dbd6 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -97,6 +97,11 @@ DECLARE_UNIQUE_INDEX(pg_auth_members_role_member_index, 2694, on pg_auth_members
DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, on pg_auth_members using btree(member oid_ops, roleid oid_ops));
#define AuthMemMemRoleIndexId 2695
+DECLARE_UNIQUE_INDEX(pg_auth_verifiers_role_method_index, 3337, on pg_auth_verifiers using btree(verroleid oid_ops, vermethod char_ops));
+#define AuthVerifRoleMethodIndexId 3337
+DECLARE_UNIQUE_INDEX(pg_auth_verifiers_method_role_index, 3338, on pg_auth_verifiers using btree(vermethod char_ops, verroleid oid_ops));
+#define AuthVerifMethodRoleIndexId 3338
+
DECLARE_UNIQUE_INDEX(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
#define CastOidIndexId 2660
DECLARE_UNIQUE_INDEX(pg_cast_source_target_index, 2661, on pg_cast using btree(castsource oid_ops, casttarget oid_ops));
diff --git a/src/include/catalog/pg_auth_verifiers.h b/src/include/catalog/pg_auth_verifiers.h
new file mode 100644
index 0000000..86461d7
--- /dev/null
+++ b/src/include/catalog/pg_auth_verifiers.h
@@ -0,0 +1,68 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_auth_verifiers.h
+ * definition of the system "authorization password hashes" relation
+ * (pg_auth_verifiers) along with the relation's initial contents.
+ *
+ *
+ * Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/catalog/pg_auth_verifiers.h
+ *
+ * NOTES
+ * the genbki.pl script reads this file and generates .bki
+ * information from the DATA() statements.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_AUTH_VERIFIERS_H
+#define PG_AUTH_VERIFIERS_H
+
+#include "catalog/genbki.h"
+
+/* ----------------
+ * pg_auth_verifiers definition. cpp turns this into
+ * typedef struct FormData_pg_auth_verifiers
+ * ----------------
+ */
+#define AuthVerifRelationId 3335
+#define AuthVerifRelation_Rowtype_Id 3336
+
+CATALOG(pg_auth_verifiers,3335) BKI_SHARED_RELATION BKI_WITHOUT_OIDS BKI_ROWTYPE_OID(3336) BKI_SCHEMA_MACRO
+{
+ Oid verroleid; /* OID of the role using this *
+ * verifier */
+ char vermethod; /* Method used to generate the hash *
+ * See AUTH_VERIFIER_xxx below */
+
+#ifdef CATALOG_VARLEN /* variable-length fields start here */
+ text vervalue BKI_FORCE_NOT_NULL; /* Hash value */
+#endif
+} FormData_pg_auth_verifiers;
+
+/* ----------------
+ * Form_pg_auth_verifiers corresponds to a pointer to a tuple with
+ * the format of pg_auth_verifiers relation.
+ * ----------------
+ */
+typedef FormData_pg_auth_verifiers *Form_pg_auth_verifiers;
+
+/* ----------------
+ * compiler constants for pg_auth_verifiers
+ * ----------------
+ */
+#define Natts_pg_auth_verifiers 3
+#define Anum_pg_auth_verifiers_verroleid 1
+#define Anum_pg_auth_verifiers_vermethod 2
+#define Anum_pg_auth_verifiers_vervalue 3
+
+/* catalog-level verifier identifiers */
+#define AUTH_VERIFIER_PLAIN 'p' /* plain verifier */
+#define AUTH_VERIFIER_MD5 'm' /* md5 verifier */
+
+/* full-name verifier identifiers */
+#define AUTH_VERIFIER_FULL_PLAIN "plain"
+#define AUTH_VERIFIER_FULL_MD5 "md5"
+
+#endif /* PG_AUTH_VERIFIERS_H */
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index c163083..35f74d0 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -56,7 +56,6 @@ CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
/* remaining fields may be null; use heap_getattr to read them! */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
- text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
#endif
} FormData_pg_authid;
@@ -75,7 +74,7 @@ typedef FormData_pg_authid *Form_pg_authid;
* compiler constants for pg_authid
* ----------------
*/
-#define Natts_pg_authid 11
+#define Natts_pg_authid 10
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
@@ -85,8 +84,7 @@ typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolreplication 7
#define Anum_pg_authid_rolbypassrls 8
#define Anum_pg_authid_rolconnlimit 9
-#define Anum_pg_authid_rolpassword 10
-#define Anum_pg_authid_rolvaliduntil 11
+#define Anum_pg_authid_rolvaliduntil 10
/* ----------------
* initial contents of pg_authid
@@ -95,7 +93,7 @@ typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
-DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_));
#define BOOTSTRAP_SUPERUSERID 10
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index d35cb0c..636e8ac 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -14,12 +14,13 @@
#include "catalog/objectaddress.h"
#include "nodes/parsenodes.h"
+/* GUC parameter */
+extern char *Password_encryption;
-/* Hook to check passwords in CreateRole() and AlterRole() */
-#define PASSWORD_TYPE_PLAINTEXT 0
-#define PASSWORD_TYPE_MD5 1
-
-typedef void (*check_password_hook_type) (const char *username, const char *password, int password_type, Datum validuntil_time, bool validuntil_null);
+typedef void (*check_password_hook_type) (const char *username,
+ List *passwordVerifiers,
+ Datum validuntil_time,
+ bool validuntil_null);
extern PGDLLIMPORT check_password_hook_type check_password_hook;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 42c9582..ccd05e0 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -449,6 +449,7 @@ typedef enum NodeTag
T_OnConflictClause,
T_CommonTableExpr,
T_RoleSpec,
+ T_AuthVerifierSpec,
/*
* TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2fd0629..a14969c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -312,6 +312,17 @@ typedef struct RoleSpec
} RoleSpec;
/*
+ * AuthVerifierSpec - a password verifier with a some dedicated values.
+ */
+typedef struct AuthVerifierSpec
+{
+ NodeTag type;
+ char veriftype; /* type of this verifier, as listed in *
+ * pg_auth_verifiers.h */
+ char *value; /* value specified by user */
+} AuthVerifierSpec;
+
+/*
* FuncCall - a function or aggregate invocation
*
* agg_order (if not NIL) indicates we saw 'foo(... ORDER BY ...)', or if
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 6e1e820..56635d5 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -416,6 +416,7 @@ PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
+PG_KEYWORD("verifiers", VERIFIERS, UNRESERVED_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 256615b..300ebaa 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -43,6 +43,8 @@ enum SysCacheIdentifier
AUTHMEMROLEMEM,
AUTHNAME,
AUTHOID,
+ AUTHVERIFMETHROLE,
+ AUTHVERIFROLEMETH,
CASTSOURCETARGET,
CLAAMNAMENSP,
CLAOID,
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
new file mode 100644
index 0000000..5e6f04d
--- /dev/null
+++ b/src/test/regress/expected/password.out
@@ -0,0 +1,104 @@
+--
+-- Tests for password verifiers
+--
+-- Tests for GUC password_encryption
+SET password_encryption = 'novalue'; -- error
+ERROR: invalid value for parameter "password_encryption": "novalue"
+SET password_encryption = true; -- error
+ERROR: invalid value for parameter "password_encryption": "true"
+SET password_encryption = 'md5'; -- ok
+SET password_encryption = 'plain'; -- ok
+SET password_encryption = 'md5,plain'; -- ok
+-- consistency of password entries
+SET password_encryption = 'plain';
+CREATE ROLE role_passwd1 PASSWORD 'role_pwd1';
+SET password_encryption = 'md5';
+CREATE ROLE role_passwd2 PASSWORD 'role_pwd2';
+SET password_encryption = 'md5,plain';
+CREATE ROLE role_passwd3 PASSWORD 'role_pwd3';
+SET password_encryption = '';
+CREATE ROLE role_passwd4 PASSWORD 'role_pwd4';
+SET password_encryption = 'plain';
+CREATE ROLE role_passwd5 PASSWORD NULL;
+-- check list of created entries
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+ rolname | vermethod | substr
+--------------+-----------+--------
+ role_passwd1 | p | rol
+ role_passwd2 | m | md5
+ role_passwd3 | m | md5
+ role_passwd3 | p | rol
+(4 rows)
+
+-- Rename a role
+ALTER ROLE role_passwd3 RENAME TO role_passwd3_new;
+NOTICE: MD5 password cleared because of role rename
+-- md5 entry should have been removed
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname = 'role_passwd3_new'
+ ORDER BY a.rolname, v.vermethod;
+ rolname | vermethod | substr
+------------------+-----------+--------
+ role_passwd3_new | p | rol
+(1 row)
+
+ALTER ROLE role_passwd3_new RENAME TO role_passwd3;
+-- ENCRYPTED and UNENCRYPTED passwords
+ALTER ROLE role_passwd1 UNENCRYPTED PASSWORD 'foo'; -- unencrypted
+ALTER ROLE role_passwd2 UNENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5
+ALTER ROLE role_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5
+ALTER ROLE role_passwd4 ENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+ rolname | vermethod | substr
+--------------+-----------+--------
+ role_passwd1 | p | foo
+ role_passwd2 | m | md5
+ role_passwd3 | m | md5
+ role_passwd4 | m | md5
+(4 rows)
+
+-- PASSWORD VERIFIERS
+ALTER ROLE role_passwd1 PASSWORD VERIFIERS (unexistent_verif = 'foo'); -- error
+ERROR: unrecognized authorization verifier option "unexistent_verif"
+LINE 1: ALTER ROLE role_passwd1 PASSWORD VERIFIERS (unexistent_verif...
+ ^
+ALTER ROLE role_passwd1 PASSWORD VERIFIERS (md5 = 'md5deaeed29b1cf796ea981d53e82cd5856'); -- ok, as md5
+ALTER ROLE role_passwd2 PASSWORD VERIFIERS (plain = 'foo'); -- ok, as plain
+ALTER ROLE role_passwd3 PASSWORD VERIFIERS (md5 = 'foo'); -- ok, as md5
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+ rolname | vermethod | substr
+--------------+-----------+--------
+ role_passwd1 | m | md5
+ role_passwd2 | p | foo
+ role_passwd3 | m | md5
+ role_passwd4 | m | md5
+(4 rows)
+
+DROP ROLE role_passwd1;
+DROP ROLE role_passwd2;
+DROP ROLE role_passwd3;
+DROP ROLE role_passwd4;
+DROP ROLE role_passwd5;
+-- all entries should have been removed
+SELECT a.rolname, v.vermethod
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%' ORDER BY a.rolname, v.vermethod;
+ rolname | vermethod
+---------+-----------
+(0 rows)
+
diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out
index aa5f42a..66ea550 100644
--- a/src/test/regress/expected/roleattributes.out
+++ b/src/test/regress/expected/roleattributes.out
@@ -1,233 +1,233 @@
-- default for superuser is false
CREATE ROLE test_def_superuser;
SELECT * FROM pg_authid WHERE rolname = 'test_def_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_superuser | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_superuser | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_superuser WITH SUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_superuser | t | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_superuser | t | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_superuser WITH NOSUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_superuser | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_superuser | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_superuser WITH SUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_superuser | t | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_superuser | t | t | f | f | f | f | f | -1 |
(1 row)
-- default for inherit is true
CREATE ROLE test_def_inherit;
SELECT * FROM pg_authid WHERE rolname = 'test_def_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_inherit | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_inherit | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_inherit WITH NOINHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_inherit | f | f | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_inherit | f | f | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_inherit WITH INHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_inherit | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_inherit | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_inherit WITH NOINHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_inherit | f | f | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_inherit | f | f | f | f | f | f | f | -1 |
(1 row)
-- default for create role is false
CREATE ROLE test_def_createrole;
SELECT * FROM pg_authid WHERE rolname = 'test_def_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_createrole | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_createrole | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_createrole WITH CREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createrole | f | t | t | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createrole | f | t | t | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_createrole WITH NOCREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createrole | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createrole | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_createrole WITH CREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createrole | f | t | t | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createrole | f | t | t | f | f | f | f | -1 |
(1 row)
-- default for create database is false
CREATE ROLE test_def_createdb;
SELECT * FROM pg_authid WHERE rolname = 'test_def_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_createdb | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_createdb | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_createdb WITH CREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createdb | f | t | f | t | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createdb | f | t | f | t | f | f | f | -1 |
(1 row)
ALTER ROLE test_createdb WITH NOCREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createdb | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createdb | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_createdb WITH CREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_createdb | f | t | f | t | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_createdb | f | t | f | t | f | f | f | -1 |
(1 row)
-- default for can login is false for role
CREATE ROLE test_def_role_canlogin;
SELECT * FROM pg_authid WHERE rolname = 'test_def_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_role_canlogin | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_role_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_role_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_role_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
ALTER ROLE test_role_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_role_canlogin | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_role_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_role_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_role_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
-- default for can login is true for user
CREATE USER test_def_user_canlogin;
SELECT * FROM pg_authid WHERE rolname = 'test_def_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_user_canlogin | f | t | f | f | t | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_user_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
CREATE USER test_user_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_user_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER USER test_user_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_user_canlogin | f | t | f | f | t | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_user_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
ALTER USER test_user_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_user_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
-- default for replication is false
CREATE ROLE test_def_replication;
SELECT * FROM pg_authid WHERE rolname = 'test_def_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_replication | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_replication | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_replication WITH REPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_replication | f | t | f | f | f | t | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_replication | f | t | f | f | f | t | f | -1 |
(1 row)
ALTER ROLE test_replication WITH NOREPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_replication | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_replication | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_replication WITH REPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_replication | f | t | f | f | f | t | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_replication | f | t | f | f | f | t | f | -1 |
(1 row)
-- default for bypassrls is false
CREATE ROLE test_def_bypassrls;
SELECT * FROM pg_authid WHERE rolname = 'test_def_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_def_bypassrls | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_def_bypassrls | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE test_bypassrls WITH BYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_bypassrls | f | t | f | f | f | f | t | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_bypassrls | f | t | f | f | f | f | t | -1 |
(1 row)
ALTER ROLE test_bypassrls WITH NOBYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_bypassrls | f | t | f | f | f | f | f | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_bypassrls | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE test_bypassrls WITH BYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- test_bypassrls | f | t | f | f | f | f | t | -1 | |
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ test_bypassrls | f | t | f | f | f | f | t | -1 |
(1 row)
-- clean up roles
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 79f9b23..f69e026 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1631,7 +1631,7 @@ pg_shadow| SELECT pg_authid.rolname AS usename,
pg_authid.rolsuper AS usesuper,
pg_authid.rolreplication AS userepl,
pg_authid.rolbypassrls AS usebypassrls,
- pg_authid.rolpassword AS passwd,
+ '********'::text AS passwd,
(pg_authid.rolvaliduntil)::abstime AS valuntil,
s.setconfig AS useconfig
FROM (pg_authid
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index eb0bc88..d4b3f36 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -91,6 +91,7 @@ pg_amproc|t
pg_attrdef|t
pg_attribute|t
pg_auth_members|t
+pg_auth_verifiers|t
pg_authid|t
pg_cast|t
pg_class|t
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index bec0316..bbe969b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -84,7 +84,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: brin gin gist spgist privileges security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets
+test: brin gin gist spgist privileges security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets password
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 7e9b319..cd724b6 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -111,6 +111,7 @@ test: matview
test: lock
test: replica_identity
test: rowsecurity
+test: password
test: object_address
test: tablesample
test: alter_generic
diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql
new file mode 100644
index 0000000..c6addf9
--- /dev/null
+++ b/src/test/regress/sql/password.sql
@@ -0,0 +1,72 @@
+--
+-- Tests for password verifiers
+--
+
+-- Tests for GUC password_encryption
+SET password_encryption = 'novalue'; -- error
+SET password_encryption = true; -- error
+SET password_encryption = 'md5'; -- ok
+SET password_encryption = 'plain'; -- ok
+SET password_encryption = 'md5,plain'; -- ok
+
+-- consistency of password entries
+SET password_encryption = 'plain';
+CREATE ROLE role_passwd1 PASSWORD 'role_pwd1';
+SET password_encryption = 'md5';
+CREATE ROLE role_passwd2 PASSWORD 'role_pwd2';
+SET password_encryption = 'md5,plain';
+CREATE ROLE role_passwd3 PASSWORD 'role_pwd3';
+SET password_encryption = '';
+CREATE ROLE role_passwd4 PASSWORD 'role_pwd4';
+SET password_encryption = 'plain';
+CREATE ROLE role_passwd5 PASSWORD NULL;
+-- check list of created entries
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+
+-- Rename a role
+ALTER ROLE role_passwd3 RENAME TO role_passwd3_new;
+-- md5 entry should have been removed
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname = 'role_passwd3_new'
+ ORDER BY a.rolname, v.vermethod;
+ALTER ROLE role_passwd3_new RENAME TO role_passwd3;
+
+-- ENCRYPTED and UNENCRYPTED passwords
+ALTER ROLE role_passwd1 UNENCRYPTED PASSWORD 'foo'; -- unencrypted
+ALTER ROLE role_passwd2 UNENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5
+ALTER ROLE role_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5
+ALTER ROLE role_passwd4 ENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+
+-- PASSWORD VERIFIERS
+ALTER ROLE role_passwd1 PASSWORD VERIFIERS (unexistent_verif = 'foo'); -- error
+ALTER ROLE role_passwd1 PASSWORD VERIFIERS (md5 = 'md5deaeed29b1cf796ea981d53e82cd5856'); -- ok, as md5
+ALTER ROLE role_passwd2 PASSWORD VERIFIERS (plain = 'foo'); -- ok, as plain
+ALTER ROLE role_passwd3 PASSWORD VERIFIERS (md5 = 'foo'); -- ok, as md5
+SELECT a.rolname, v.vermethod, substr(v.vervalue, 1, 3)
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%'
+ ORDER BY a.rolname, v.vermethod;
+
+DROP ROLE role_passwd1;
+DROP ROLE role_passwd2;
+DROP ROLE role_passwd3;
+DROP ROLE role_passwd4;
+DROP ROLE role_passwd5;
+
+-- all entries should have been removed
+SELECT a.rolname, v.vermethod
+ FROM pg_auth_verifiers v
+ LEFT JOIN pg_authid a ON (v.verroleid = a.oid)
+ WHERE a.rolname LIKE 'role_passwd%' ORDER BY a.rolname, v.vermethod;
--
2.7.3