I have recently, once again for the umpteenth time, been involved in
discussions around (paraphrasing) "why does Postgres leak the passwords
into the logs when they are changed". I know well that the canonical
advice is something like "use psql with \password if you care about that".
And while that works, it is a deeply unsatisfying answer for me to give
and for the OP to receive.
The alternative is something like "...well if you don't like that, use
PQencryptPasswordConn() to roll your own solution that meets your
security needs".
Again, not a spectacular answer IMHO. It amounts to "here is a
do-it-yourself kit, go put it together". It occurred to me that we can,
and really should, do better.
The attached patch set moves the guts of \password from psql into the
libpq client side -- PQchangePassword() (patch 0001).
The usage in psql serves as a ready built-in test for the libpq function
(patch 0002). Docs included too (patch 0003).
One thing I have not done but, considered, is adding an additional
optional parameter to allow "VALID UNTIL" to be set. Seems like it would
be useful to be able to set an expiration when setting a new password.
I will register this in the upcoming commitfest, but meantime
thought/comments/etc. would be gratefully received.
Thanks,
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index 850734a..28b861f 100644
*** a/src/interfaces/libpq/exports.txt
--- b/src/interfaces/libpq/exports.txt
*************** PQclosePrepared 188
*** 191,193 ****
--- 191,194 ----
PQclosePortal 189
PQsendClosePrepared 190
PQsendClosePortal 191
+ PQchangePassword 192
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 912aa14..3ee67ba 100644
*** a/src/interfaces/libpq/fe-auth.c
--- b/src/interfaces/libpq/fe-auth.c
*************** PQencryptPasswordConn(PGconn *conn, cons
*** 1372,1374 ****
--- 1372,1463 ----
return crypt_pwd;
}
+
+ /*
+ * PQchangePassword -- exported routine to change a password
+ *
+ * This is intended to be used by client applications that wish to
+ * change the password for a user. The password is not sent in
+ * cleartext because it is encrypted on the client side. This is
+ * good because it ensures the cleartext password is never known by
+ * the server, and therefore won't end up in logs, pg_stat displays,
+ * etc. We export the function so that clients won't be dependent
+ * on the implementation specific details with respect to how the
+ * server changes passwords.
+ *
+ * Arguments are a connection object, the cleartext password, the SQL
+ * name of the user it is for, and a string indicating the algorithm to
+ * use for encrypting the password. If algorithm is NULL,
+ * PQencryptPasswordConn() queries the server for the current
+ * 'password_encryption' value. If you wish to avoid that, e.g. to avoid
+ * blocking, you can execute 'show password_encryption' yourself before
+ * calling this function, and pass it as the algorithm.
+ *
+ * Return value is a boolean, true for success. On error, an error message
+ * is stored in the connection object, and returns false.
+ */
+ bool
+ PQchangePassword(PGconn *conn, const char *passwd, const char *user,
+ const char *algorithm)
+ {
+ char *encrypted_password = NULL;
+ PQExpBufferData buf;
+ bool success = true;
+
+ encrypted_password = PQencryptPasswordConn(conn, passwd, user, algorithm);
+
+ if (!encrypted_password)
+ {
+ /* PQencryptPasswordConn() already registered the error */
+ success = false;
+ }
+ else
+ {
+ char *fmtpw = NULL;
+
+ fmtpw = PQescapeLiteral(conn, encrypted_password,
+ strlen(encrypted_password));
+
+ /* no longer needed, so clean up now */
+ PQfreemem(encrypted_password);
+
+ if (!fmtpw)
+ {
+ /* PQescapeLiteral() already registered the error */
+ success = false;
+ }
+ else
+ {
+ char *fmtuser = NULL;
+
+ fmtuser = PQescapeIdentifier(conn, user, strlen(user));
+ if (!fmtuser)
+ {
+ /* PQescapeIdentifier() already registered the error */
+ success = false;
+ PQfreemem(fmtpw);
+ }
+ else
+ {
+ PGresult *res;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf, "ALTER USER %s PASSWORD %s",
+ fmtuser, fmtpw);
+
+ res = PQexec(conn, buf.data);
+ if (!res)
+ success = false;
+ else
+ PQclear(res);
+
+ /* clean up */
+ termPQExpBuffer(&buf);
+ PQfreemem(fmtuser);
+ PQfreemem(fmtpw);
+ }
+ }
+ }
+
+ return success;
+ }
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index 97762d5..f6e7207 100644
*** a/src/interfaces/libpq/libpq-fe.h
--- b/src/interfaces/libpq/libpq-fe.h
*************** extern int PQenv2encoding(void);
*** 659,664 ****
--- 659,665 ----
extern char *PQencryptPassword(const char *passwd, const char *user);
extern char *PQencryptPasswordConn(PGconn *conn, const char *passwd, const char *user, const char *algorithm);
+ extern bool PQchangePassword(PGconn *conn, const char *passwd, const char *user, const char *algorithm);
/* === in encnames.c === */
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 82cc091..a581b52 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command_password(PsqlScanState scan
*** 2158,2186 ****
}
else
{
! char *encrypted_password;
!
! encrypted_password = PQencryptPasswordConn(pset.db, pw1, user, NULL);
!
! if (!encrypted_password)
! {
pg_log_info("%s", PQerrorMessage(pset.db));
- success = false;
- }
- else
- {
- PGresult *res;
-
- printfPQExpBuffer(&buf, "ALTER USER %s PASSWORD ",
- fmtId(user));
- appendStringLiteralConn(&buf, encrypted_password, pset.db);
- res = PSQLexec(buf.data);
- if (!res)
- success = false;
- else
- PQclear(res);
- PQfreemem(encrypted_password);
- }
}
free(user);
--- 2158,2166 ----
}
else
{
! success = PQchangePassword(pset.db, pw1, user, NULL);
! if (!success)
pg_log_info("%s", PQerrorMessage(pset.db));
}
free(user);
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index ed88ac0..5d2f70c 100644
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*************** char *PQencryptPasswordConn(PGconn *conn
*** 7116,7121 ****
--- 7116,7167 ----
</listitem>
</varlistentry>
+ <varlistentry id="libpq-PQchangePassword">
+ <term><function>PQchangePassword</function><indexterm><primary>PQchangePassword</primary></indexterm></term>
+
+ <listitem>
+ <para>
+ Changes a <productname>PostgreSQL</productname> password.
+ <synopsis>
+ char *PQchangePassword(PGconn *conn, const char *passwd, const char *user, const char *algorithm);
+ </synopsis>
+ This function uses <function>PQencryptPasswordConn</function>
+ to build and execute the command <literal>ALTER USER ... PASSWORD
+ '...'</literal>, thereby changing the user's password. It exists for
+ the same reason as <function>PQencryptPasswordConn</function>, but
+ is more convenient as it both builds and runs the command for you.
+ </para>
+
+ <para>
+ The <parameter>passwd</parameter> and <parameter>user</parameter> arguments
+ are the cleartext password, and the SQL name of the user it is for.
+ <parameter>algorithm</parameter> specifies the encryption algorithm
+ to use to encrypt the password. Currently supported algorithms are
+ <literal>md5</literal> and <literal>scram-sha-256</literal> (<literal>on</literal> and
+ <literal>off</literal> are also accepted as aliases for <literal>md5</literal>, for
+ compatibility with older server versions). Note that support for
+ <literal>scram-sha-256</literal> was introduced in <productname>PostgreSQL</productname>
+ version 10, and will not work correctly with older server versions. If
+ <parameter>algorithm</parameter> is <symbol>NULL</symbol>, this function will query
+ the server for the current value of the
+ <xref linkend="guc-password-encryption"/> setting. That can block, and
+ will fail if the current transaction is aborted, or if the connection
+ is busy executing another query. If you wish to use the default
+ algorithm for the server but want to avoid blocking, query
+ <varname>password_encryption</varname> yourself before calling
+ <xref linkend="libpq-PQchangePassword"/>, and pass that value as the
+ <parameter>algorithm</parameter>.
+ </para>
+
+ <para>
+ The return value is a boolean indicating <symbol>true</symbol> for success
+ or <symbol>false</symbol> for failure. On failure a suitable message is
+ stored in the connection object.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-PQencryptPassword">
<term><function>PQencryptPassword</function><indexterm><primary>PQencryptPassword</primary></indexterm></term>
