postgresql-jdbc.git / summary / log / commit / refs
commit eae5e613641be1cb9aba13f454ae5fc63f8ed55a
Author: Sehrope Sarkuni <sehrope@jackdb.com>
Date: Thu Dec 28 00:04:24 2023 +0000
feat: Add PasswordUtil and PGConnection.alterUserPassword(...) for encrypting passwords client side
Add a PasswordUtil helper that provides methods for encoding a given password client
side so that it may be used for ALTER USER and CREATE USER statement without sending
the plaintext password over the wire.
Also adds a helper to PGConnection that generates the ALTER USER command necessary to
update a user's password and executes it.
The helpers default to encoding the password using SCRAM-SHA-256 if used directly or using the
server's default password_encryption setting if used via the PGConnection helper.
Co-authored-by: Sehrope Sarkuni <sehrope@jackdb.com>
Co-authored-by: Dave Cramer <davecramer@gmail.com>
CHANGELOG.md | 1 +
.../src/main/java/org/postgresql/PGConnection.java | 41 ++++
.../main/java/org/postgresql/util/MD5Digest.java | 2 +-
.../java/org/postgresql/util/PasswordUtil.java | 220 +++++++++++++++++++++
.../org/postgresql/test/util/PasswordUtilTest.java | 182 +++++++++++++++++
.../java/org/postgresql/test/TestUtil.java | 15 ++
6 files changed, 460 insertions(+), 1 deletion(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8038de6e..7d70b33d 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,6 +7,7 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
### Changed
### Added
+* feat: Add PasswordUtil for encrypting passwords client side [PR #3082](https://github.com/pgjdbc/pgjdbc/pull/3082)
### Fixed
## [42.7.1] (2023-12-06 08:34:00 -0500)
diff --git a/pgjdbc/src/main/java/org/postgresql/PGConnection.java b/pgjdbc/src/main/java/org/postgresql/PGConnection.java
index 0273cb2a..1338347a 100644
--- a/pgjdbc/src/main/java/org/postgresql/PGConnection.java
+++ b/pgjdbc/src/main/java/org/postgresql/PGConnection.java
@@ -11,11 +11,17 @@ import org.postgresql.jdbc.AutoSave;
import org.postgresql.jdbc.PreferQueryMode;
import org.postgresql.largeobject.LargeObjectManager;
import org.postgresql.replication.PGReplicationConnection;
+import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
+import org.postgresql.util.PSQLException;
+import org.postgresql.util.PSQLState;
+import org.postgresql.util.PasswordUtil;
import org.checkerframework.checker.nullness.qual.Nullable;
import java.sql.Array;
+import java.sql.Connection;
+import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
@@ -244,6 +250,41 @@ public interface PGConnection {
*/
PGReplicationConnection getReplicationAPI();
+ /**
+ * Change a user's password to the specified new password.
+ *
+ * <p>
+ * If the specific encryption type is not specified, this method defaults to querying the database server for the server's default password_encryption.
+ * This method does not send the new password in plain text to the server.
+ * Instead, it encrypts the password locally and sends the encoded hash so that the plain text password is never sent on the wire.
+ * </p>
+ *
+ * <p>
+ * Acceptable values for encryptionType are null, "md5", or "scram-sha-256".
+ * Users should avoid "md5" unless they are explicitly targeting an older server that does not support the more secure SCRAM.
+ * </p>
+ *
+ * @param user The username of the database user
+ * @param newPassword The new password for the database user
+ * @param encryptionType The type of password encryption to use or null if the database server default should be used.
+ * @throws SQLException If the password could not be altered
+ */
+ default void alterUserPassword(String user, char[] newPassword, @Nullable String encryptionType) throws SQLException {
+ try (Statement stmt = ((Connection) this).createStatement()) {
+ if (encryptionType == null) {
+ try (ResultSet rs = stmt.executeQuery("SHOW password_encryption")) {
+ if (!rs.next()) {
+ throw new PSQLException(GT.tr("Expected a row when reading password_encrypton but none was found"),
+ PSQLState.NO_DATA);
+ }
+ encryptionType = rs.getString(1);
+ }
+ }
+ String sql = PasswordUtil.genAlterUserPasswordSQL(user, newPassword, encryptionType);
+ stmt.execute(sql);
+ }
+ }
+
/**
* <p>Returns the current values of all parameters reported by the server.</p>
*
diff --git a/pgjdbc/src/main/java/org/postgresql/util/MD5Digest.java b/pgjdbc/src/main/java/org/postgresql/util/MD5Digest.java
index 80c30c71..f0e586ce 100644
--- a/pgjdbc/src/main/java/org/postgresql/util/MD5Digest.java
+++ b/pgjdbc/src/main/java/org/postgresql/util/MD5Digest.java
@@ -57,7 +57,7 @@ public class MD5Digest {
/*
* Turn 16-byte stream into a human-readable 32-byte hex string
*/
- private static void bytesToHex(byte[] bytes, byte[] hex, int offset) {
+ public static void bytesToHex(byte[] bytes, byte[] hex, int offset) {
int pos = offset;
for (int i = 0; i < 16; i++) {
//bit twiddling converts to int, so just do it once here for both operations
diff --git a/pgjdbc/src/main/java/org/postgresql/util/PasswordUtil.java b/pgjdbc/src/main/java/org/postgresql/util/PasswordUtil.java
new file mode 100644
index 00000000..cfd9d77c
--- /dev/null
+++ b/pgjdbc/src/main/java/org/postgresql/util/PasswordUtil.java
@@ -0,0 +1,220 @@
+/*
+ * Copyright (c) 2023, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.util;
+
+import org.postgresql.core.Utils;
+
+import com.ongres.scram.common.ScramFunctions;
+import com.ongres.scram.common.ScramMechanisms;
+import com.ongres.scram.common.bouncycastle.base64.Base64;
+import com.ongres.scram.common.stringprep.StringPreparations;
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.nio.ByteBuffer;
+import java.nio.CharBuffer;
+import java.nio.charset.StandardCharsets;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
+import java.security.SecureRandom;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Objects;
+
+public class PasswordUtil {
+ private static final String DEFAULT_PASSWORD_ENCRYPTION = "scram-sha-256";
+ private static final int DEFAULT_ITERATIONS = 4096;
+ private static final int DEFAULT_SALT_LENGTH = 16;
+
+ private static class SecureRandomHolder {
+ static final SecureRandom INSTANCE = new SecureRandom();
+ }
+
+ private static SecureRandom getSecureRandom() {
+ return SecureRandomHolder.INSTANCE;
+ }
+
+ /**
+ * Generate the encoded text representation of the given password for
+ * SCRAM-SHA-256 authentication. The return value of this method is the literal
+ * text that may be used when creating or modifying a user with the given
+ * password without the surrounding single quotes.
+ *
+ * @param password The plain text of the user's password
+ * @param iterations The number of iterations of the hashing algorithm to
+ * perform
+ * @param salt The random salt value
+ * @return The text representation of the password encrypted for SCRAM-SHA-256
+ * authentication
+ */
+ public static String encodeScramSha256(char[] password, int iterations, byte[] salt) {
+ Objects.requireNonNull(password, "password");
+ if (iterations <= 0) {
+ throw new IllegalArgumentException("iterations must be greater than zero");
+ }
+ if (salt.length == 0) {
+ throw new IllegalArgumentException("salt length must be greater than zero");
+ }
+ try {
+ String passwordText = String.valueOf(password);
+ byte[] saltedPassword = ScramFunctions.saltedPassword(ScramMechanisms.SCRAM_SHA_256,
+ StringPreparations.SASL_PREPARATION, passwordText, salt, iterations);
+ byte[] clientKey = ScramFunctions.clientKey(ScramMechanisms.SCRAM_SHA_256, saltedPassword);
+ byte[] storedKey = ScramFunctions.storedKey(ScramMechanisms.SCRAM_SHA_256, clientKey);
+ byte[] serverKey = ScramFunctions.serverKey(ScramMechanisms.SCRAM_SHA_256, saltedPassword);
+
+ return "SCRAM-SHA-256" //
+ + "$" + iterations //
+ + ":" + Base64.toBase64String(salt) //
+ + "$" + Base64.toBase64String(storedKey) //
+ + ":" + Base64.toBase64String(serverKey);
+ } finally {
+ Arrays.fill(password, ' ');
+ }
+ }
+
+ /**
+ * Encode the given password for SCRAM-SHA-256 authentication using the default
+ * iteration count and a random salt.
+ *
+ * @param password The plain text of the user's password
+ * @return The text representation of the password encrypted for SCRAM-SHA-256
+ * authentication
+ */
+ public static String encodeScramSha256(char[] password) {
+ Objects.requireNonNull(password, "password");
+ try {
+ SecureRandom rng = getSecureRandom();
+ byte[] salt = rng.generateSeed(DEFAULT_SALT_LENGTH);
+ return encodeScramSha256(password, DEFAULT_ITERATIONS, salt);
+ } finally {
+ Arrays.fill(password, ' ');
+ }
+ }
+
+ /**
+ * Encode the the given password for use with md5 authentication. The PostgreSQL
+ * server uses the username as the per-user salt so that must also be provided.
+ * The return value of this method is the literal text that may be used when
+ * creating or modifying a user with the given password without the surrounding
+ * single quotes.
+ *
+ * @param user The username of the database user
+ * @param password The plain text of the user's password
+ * @return The text representation of the password encrypted for md5
+ * authentication.
+ */
+ public static String encodeMd5(String user, char[] password) {
+ Objects.requireNonNull(password, "password");
+ ByteBuffer passwordBytes = null;
+ try {
+ passwordBytes = StandardCharsets.UTF_8.encode(CharBuffer.wrap(password));
+ byte[] userBytes = user.getBytes(StandardCharsets.UTF_8);
+ final MessageDigest md = MessageDigest.getInstance("MD5");
+
+ md.update(passwordBytes);
+ md.update(userBytes);
+ byte[] digest = md.digest(); // 16-byte MD5
+
+ final byte[] encodedPassword = new byte[35]; // 3 + 2 x 16
+ encodedPassword[0] = (byte) 'm';
+ encodedPassword[1] = (byte) 'd';
+ encodedPassword[2] = (byte) '5';
+ MD5Digest.bytesToHex(digest, encodedPassword, 3);
+
+ return new String(encodedPassword, StandardCharsets.UTF_8);
+ } catch (NoSuchAlgorithmException e) {
+ throw new IllegalStateException("Unable to encode password with MD5", e);
+ } finally {
+ Arrays.fill(password, ' ');
+ if (passwordBytes != null) {
+ if (passwordBytes.hasArray()) {
+ Arrays.fill(passwordBytes.array(), (byte) 0);
+ } else {
+ int limit = passwordBytes.limit();
+ for (int i = 0; i < limit; i++) {
+ passwordBytes.put(i, (byte) 0);
+ }
+ }
+ }
+ }
+ }
+
+ /**
+ * Encode the given password using the driver's default encryption method.
+ *
+ * @param user The username of the database user
+ * @param password The plain text of the user's password
+ * @return The encoded password
+ * @throws SQLException If an error occurs encoding the password
+ */
+ public static String encodePassword(String user, char[] password) throws SQLException {
+ return encodePassword(user, password, null);
+ }
+
+ /**
+ * Encode the given password for the specified encryption type.
+ * The word "encryption" is used here to match the verbiage in the PostgreSQL
+ * server, i.e. the "password_encryption" setting. In reality, a cryptographic
+ * digest / HMAC operation is being performed.
+ * The database user is only required for the md5 encryption type.
+ *
+ * @param user The username of the database user
+ * @param password The plain text of the user's password
+ * @param encryptionType The encryption type for which to encode the user's
+ * password. This should match the database's supported
+ * methods and value of the password_encryption setting.
+ * @return The encoded password
+ * @throws SQLException If an error occurs encoding the password
+ */
+ public static String encodePassword(String user, char[] password, @Nullable String encryptionType)
+ throws SQLException {
+ Objects.requireNonNull(password, "password");
+ if (encryptionType == null) {
+ encryptionType = DEFAULT_PASSWORD_ENCRYPTION;
+ }
+ switch (encryptionType) {
+ case "on":
+ case "off":
+ case "md5":
+ return encodeMd5(user, password);
+ case "scram-sha-256":
+ return encodeScramSha256(password);
+ }
+ // If we get here then it's an unhandled encryption type so we must wipe the array ourselves
+ Arrays.fill(password, ' ');
+ throw new PSQLException("Unable to determine encryption type: " + encryptionType, PSQLState.SYSTEM_ERROR);
+ }
+
+ /**
+ * Generate the SQL statement to alter a user's password using the given
+ * encryption.
+ * If the encryption type is not specified, the driver's default will be used.
+ * All other encryption settings for the password will use the driver's
+ * defaults.
+ *
+ * @param user The username of the database user
+ * @param password The plain text of the user's password
+ * @param encryptionType The encryption type of the password or null to use the
+ * default
+ * @return An SQL statement that may be executed to change the user's password
+ * @throws SQLException If an error occurs encoding the password
+ */
+ public static String genAlterUserPasswordSQL(String user, char[] password, @Nullable String encryptionType)
+ throws SQLException {
+ // This will also wipe the password array for us:
+ String encodedPassword = encodePassword(user, password, encryptionType);
+ StringBuilder sb = new StringBuilder();
+ sb.append("ALTER USER ");
+ Utils.escapeIdentifier(sb, user);
+ sb.append(" PASSWORD '");
+ // The choice of true / false for standard conforming strings does not matter
+ // here as the value being escaped is generated by us and known to be hex
+ // characters for all of the implemented password encryption methods.
+ Utils.escapeLiteral(sb, encodedPassword, true);
+ sb.append("'");
+ return sb.toString();
+ }
+}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/util/PasswordUtilTest.java b/pgjdbc/src/test/java/org/postgresql/test/util/PasswordUtilTest.java
new file mode 100644
index 00000000..c6da179f
--- /dev/null
+++ b/pgjdbc/src/test/java/org/postgresql/test/util/PasswordUtilTest.java
@@ -0,0 +1,182 @@
+/*
+ * Copyright (c) 2023, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.test.util;
+
+import org.postgresql.PGConnection;
+import org.postgresql.core.Utils;
+import org.postgresql.test.TestUtil;
+import org.postgresql.test.util.rules.ServerVersionRule;
+import org.postgresql.test.util.rules.annotation.HaveMinimalServerVersion;
+import org.postgresql.util.PasswordUtil;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+import org.junit.Assert;
+import org.junit.Rule;
+import org.junit.Test;
+
+import java.security.SecureRandom;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Properties;
+
+public class PasswordUtilTest {
+ @Rule
+ public ServerVersionRule versionRule = new ServerVersionRule();
+
+ private static final SecureRandom rng = new SecureRandom();
+
+ private static String randomSuffix() {
+ return Long.toHexString(rng.nextLong());
+ }
+
+ private void assertValidUsernamePassword(String user, String password) {
+ Properties props = new Properties();
+ props.setProperty("user", user);
+ props.setProperty("password", password);
+ try (Connection conn = TestUtil.openDB(props)) {
+ String actualUser = TestUtil.queryForString(conn, "SELECT USER");
+ Assert.assertEquals("User should match", user, actualUser);
+ } catch (SQLException e) {
+ throw new RuntimeException("Failed to authenticate using supplied user and password", e);
+ }
+ }
+
+ private void assertInvalidUsernamePassword(String user, String password) {
+ Properties props = new Properties();
+ props.setProperty("user", user);
+ props.setProperty("password", password);
+ Assert.assertThrows("User should not be able to authenticate", SQLException.class, () -> {
+ try (Connection conn = TestUtil.openDB(props)) {
+ conn.getSchema(); // Do something with conn to appease checkstyle
+ }
+ });
+ }
+
+ private void assertWiped(char[] passwordChars) {
+ for (int i = 0; i < passwordChars.length; i++) {
+ Assert.assertEquals("passwordChars[" + i + "] should be wiped", ' ', passwordChars[i]);
+ }
+ }
+
+ private void testUserPassword(@Nullable String encryptionType, String username, String password,
+ String encodedPassword) throws SQLException {
+ String escapedUsername = Utils.escapeIdentifier(null, username).toString();
+
+ try (Connection superConn = TestUtil.openPrivilegedDB()) {
+ TestUtil.execute(superConn, "CREATE USER " //
+ + escapedUsername //
+ + " WITH PASSWORD '" + encodedPassword + "'");
+
+ String shadowPass = TestUtil.queryForString(superConn, //
+ "SELECT passwd FROM pg_shadow WHERE usename = ?", username);
+ Assert.assertEquals("pg_shadow value of password must match encoded", shadowPass, encodedPassword);
+
+ // We should be able to log in using our new user:
+ assertValidUsernamePassword(username, password);
+ // We also check that we cannot log in with the wrong password to ensure that
+ // the server is not simply trusting everything
+ assertInvalidUsernamePassword(username, "Bad Password:" + password);
+
+ String newPassword = "mySecretNewPassword" + randomSuffix();
+ PGConnection pgConn = superConn.unwrap(PGConnection.class);
+ char[] newPasswordChars = newPassword.toCharArray();
+ pgConn.alterUserPassword(username, newPasswordChars, encryptionType);
+ Assert.assertNotEquals("newPassword char[] array should be wiped and not match original after encoding", newPassword, String.valueOf(newPasswordChars));
+ assertWiped(newPasswordChars);
+
+ // We should be able to log in using our new password
+ assertValidUsernamePassword(username, newPassword);
+ // We also check that we cannot log in with the wrong password to ensure that
+ // the server is not simply trusting everything
+ assertInvalidUsernamePassword(username, "Bad Password:" + newPassword);
+ } finally {
+ try (Connection superConn = TestUtil.openPrivilegedDB()) {
+ TestUtil.execute(superConn, "DROP USER " + escapedUsername);
+ } catch (Exception ignore) { }
+ }
+ }
+
+ private void testUserPassword(@Nullable String encryptionType, String username, String password) throws SQLException {
+ char[] passwordChars = password.toCharArray();
+ String encodedPassword = PasswordUtil.encodePassword(username, passwordChars, encryptionType);
+ Assert.assertNotEquals("password char[] array should be wiped and not match original password after encoding", password, String.valueOf(passwordChars));
+ assertWiped(passwordChars);
+ testUserPassword(encryptionType, username, password, encodedPassword);
+ }
+
+ private void testUserPassword(@Nullable String encryptionType) throws SQLException {
+ String username = "test_password_" + randomSuffix();
+ String password = "t0pSecret" + randomSuffix();
+
+ testUserPassword(encryptionType, username, password);
+ testUserPassword(encryptionType, username, "password with spaces");
+ testUserPassword(encryptionType, username, "password with single ' quote'");
+ testUserPassword(encryptionType, username, "password with double \" quote'");
+ testUserPassword(encryptionType, username + " with spaces", password);
+ testUserPassword(encryptionType, username + " with single ' quote", password);
+ testUserPassword(encryptionType, username + " with single \" quote", password);
+ }
+
+ @Test
+ public void testServerDefault() throws SQLException {
+ String encryptionType;
+ try (Connection conn = TestUtil.openPrivilegedDB()) {
+ encryptionType = TestUtil.queryForString(conn, "SHOW password_encryption");
+ }
+ testUserPassword(encryptionType);
+ }
+
+ @Test
+ @HaveMinimalServerVersion("10.0")
+ public void testDriverDefault() throws SQLException {
+ testUserPassword(null);
+ }
+
+ @Test
+ public void testMD5() throws SQLException {
+ testUserPassword("md5");
+ }
+
+ @Test
+ public void testEncryptionTypeValueOfOn() throws SQLException {
+ testUserPassword("on");
+ }
+
+ @Test
+ public void testEncryptionTypeValueOfOff() throws SQLException {
+ testUserPassword("off");
+ }
+
+ @Test
+ @HaveMinimalServerVersion("10.0")
+ public void testScramSha256() throws SQLException {
+ testUserPassword("scram-sha-256");
+ }
+
+ @Test
+ @HaveMinimalServerVersion("10.0")
+ public void testCustomScramParams() throws SQLException {
+ String username = "test_password_" + randomSuffix();
+ String password = "t0pSecret" + randomSuffix();
+ byte[] salt = new byte[32];
+ rng.nextBytes(salt);
+ int iterations = 12345;
+ String encodedPassword = PasswordUtil.encodeScramSha256(password.toCharArray(), iterations, salt);
+ Assert.assertTrue("encoded password should have custom iteration count", encodedPassword.startsWith("SCRAM-SHA-256$" + iterations + ":"));
+ testUserPassword("scram-sha-256", username, password, encodedPassword);
+ }
+
+ @Test
+ public void testUnknownEncryptionType() throws SQLException {
+ String username = "test_password_" + randomSuffix();
+ String password = "t0pSecret" + randomSuffix();
+ char[] passwordChars = password.toCharArray();
+ Assert.assertThrows(SQLException.class, () -> {
+ PasswordUtil.encodePassword(username, passwordChars, "not-a-real-encryption-type");
+ });
+ assertWiped(passwordChars);
+ }
+}
diff --git a/pgjdbc/src/testFixtures/java/org/postgresql/test/TestUtil.java b/pgjdbc/src/testFixtures/java/org/postgresql/test/TestUtil.java
index 7c8ec546..d41e7a6b 100644
--- a/pgjdbc/src/testFixtures/java/org/postgresql/test/TestUtil.java
+++ b/pgjdbc/src/testFixtures/java/org/postgresql/test/TestUtil.java
@@ -1026,6 +1026,21 @@ public class TestUtil {
return value;
}
+ /**
+ * Same as queryForString(...) above but with a single string param.
+ */
+ public static String queryForString(Connection conn, String sql, String param) throws SQLException {
+ PreparedStatement stmt = conn.prepareStatement(sql);
+ stmt.setString(1, param);
+ ResultSet rs = stmt.executeQuery();
+ Assert.assertTrue("Query should have returned exactly one row but none was found: " + sql, rs.next());
+ String value = rs.getString(1);
+ Assert.assertFalse("Query should have returned exactly one row but more than one found: " + sql, rs.next());
+ rs.close();
+ stmt.close();
+ return value;
+ }
+
/**
* Execute a SQL query with a given connection, fetch the first row, and return its
* boolean value.
[parent: 0b0f4ce6ed89]