Subject: [PATCH] cfe-03-scripts_over_cfe-02-internaldoc squash commit

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Apr 2021 14:23:36 -0400

---
 src/backend/Makefile                          | 15 +++-
 src/backend/crypto/ckey_aws.sh.sample (new)   | 53 ++++++++++++
 .../crypto/ckey_direct.sh.sample (new)        | 39 +++++++++
 .../crypto/ckey_passphrase.sh.sample (new)    | 35 ++++++++
 .../crypto/ckey_piv_nopin.sh.sample (new)     | 68 ++++++++++++++++
 .../crypto/ckey_piv_pin.sh.sample (new)       | 81 +++++++++++++++++++
 .../crypto/ssl_passphrase.sh.sample (new)     | 35 ++++++++
 7 files changed, 325 insertions(+), 1 deletion(-)

diff --git a/src/backend/Makefile b/src/backend/Makefile
index 0da848b1fd..76eb44f632 100644
--- a/src/backend/Makefile
+++ b/src/backend/Makefile
@@ -211,6 +211,12 @@ endif
 	$(INSTALL_DATA) $(srcdir)/libpq/pg_hba.conf.sample '$(DESTDIR)$(datadir)/pg_hba.conf.sample'
 	$(INSTALL_DATA) $(srcdir)/libpq/pg_ident.conf.sample '$(DESTDIR)$(datadir)/pg_ident.conf.sample'
 	$(INSTALL_DATA) $(srcdir)/utils/misc/postgresql.conf.sample '$(DESTDIR)$(datadir)/postgresql.conf.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ckey_aws.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ckey_aws.sh.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ckey_direct.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ckey_direct.sh.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ckey_passphrase.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ckey_passphrase.sh.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ckey_piv_nopin.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ckey_piv_nopin.sh.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ckey_piv_pin.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ckey_piv_pin.sh.sample'
+	$(INSTALL_DATA) $(srcdir)/crypto/ssl_passphrase.sh.sample '$(DESTDIR)$(datadir)/auth_commands/ssl_passphrase.sh.sample'
 
 ifeq ($(with_llvm), yes)
 install-bin: install-postgres-bitcode
@@ -236,6 +242,7 @@ endif
 
 installdirs:
 	$(MKDIR_P) '$(DESTDIR)$(bindir)' '$(DESTDIR)$(datadir)'
+	$(MKDIR_P) '$(DESTDIR)$(datadir)' '$(DESTDIR)$(datadir)/auth_commands'
 ifeq ($(PORTNAME), cygwin)
 ifeq ($(MAKE_DLL), true)
 	$(MKDIR_P) '$(DESTDIR)$(libdir)'
@@ -275,7 +282,13 @@ endif
 	$(MAKE) -C utils uninstall-data
 	rm -f '$(DESTDIR)$(datadir)/pg_hba.conf.sample' \
 	      '$(DESTDIR)$(datadir)/pg_ident.conf.sample' \
-	      '$(DESTDIR)$(datadir)/postgresql.conf.sample'
+	      '$(DESTDIR)$(datadir)/postgresql.conf.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ckey_aws.sh.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ckey_direct.sh.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ckey_passphrase.sh.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ckey_piv_nopin.sh.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ckey_piv_pin.sh.sample' \
+	      '$(DESTDIR)$(datadir)/auth_commands/ssl_passphrase.sh.sample'
 ifeq ($(with_llvm), yes)
 	$(call uninstall_llvm_module,postgres)
 endif
diff --git a/src/backend/crypto/ckey_aws.sh.sample b/src/backend/crypto/ckey_aws.sh.sample
new file mode 100644
index 0000000000..d9bee53132
--- /dev/null
+++ b/src/backend/crypto/ckey_aws.sh.sample
@@ -0,0 +1,53 @@
+#!/bin/sh
+
+# This uses the AWS Secrets Manager using the AWS CLI and OpenSSL.
+# This stores the AWS secret Id in $DIR.
+# Do not create any file with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.  
+
+[ "$#" -ne 1 ] && echo "cluster_key_command usage: $0 \"%d\"" 1>&2 && exit 1
+# No need for %R or -R since we are not prompting
+
+DIR="$1"
+[ ! -e "$DIR" ] && echo "$DIR does not exist" 1>&2 && exit 1
+[ ! -d "$DIR" ] && echo "$DIR is not a directory" 1>&2 && exit 1
+
+# File containing the id of the AWS secret
+AWS_ID_FILE="$DIR/aws-secret.id"
+
+
+# ----------------------------------------------------------------------
+
+
+# Create an AWS Secrets Manager secret?
+if [ ! -e "$AWS_ID_FILE" ]
+then	# The 'postgres' operating system user must have permission to
+	# access the AWS CLI
+
+	# The epoch-time/directory/hostname combination is unique
+	HASH=$(echo -n "$(date '+%s')$DIR$(hostname)" | sha1sum | cut -d' ' -f1)
+	AWS_SECRET_ID="Postgres-cluster-key-$HASH"
+
+	# Use stdin to avoid passing the secret on the command line
+	openssl rand -hex 32 |
+	aws secretsmanager create-secret \
+		--name "$AWS_SECRET_ID" \
+		--description "Postgres cluster file encryption on $(hostname)" \
+		--secret-string 'file:///dev/stdin' \
+		--output text > /dev/null
+	if [ "$?" -ne 0 ]
+	then	echo 'cluster key generation failed' 1>&2
+		exit 1
+	fi
+
+	echo "$AWS_SECRET_ID" > "$AWS_ID_FILE"
+fi
+
+if ! aws secretsmanager get-secret-value \
+	--secret-id "$(cat "$AWS_ID_FILE")" \
+	--output text
+then	echo 'cluster key retrieval failed' 1>&2
+	exit 1
+fi | awk -F'\t' 'NR == 1 {print $4}'
+
+exit 0
diff --git a/src/backend/crypto/ckey_direct.sh.sample b/src/backend/crypto/ckey_direct.sh.sample
new file mode 100644
index 0000000000..492defcffe
--- /dev/null
+++ b/src/backend/crypto/ckey_direct.sh.sample
@@ -0,0 +1,39 @@
+#!/bin/sh
+
+# This uses a 64-character hex key supplied by the user.
+# If OpenSSL is installed, you can generate a pseudo-random key by running:
+#	openssl rand -hex 32
+# To get a true random key, run:
+#	wget -q -O - 'https://www.random.org/cgi-bin/randbyte?nbytes=32&format=h' | tr -d ' \n'; echo
+# Do not create any fie with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.
+
+[ "$#" -lt 1 ] && echo "cluster_key_command usage: $0 %R [%p]" 1>&2 && exit 1
+# Supports environment variable PROMPT
+
+FD="$1"
+[ ! -t "$FD" ] && echo "file descriptor $FD does not refer to a terminal" 1>&2 && exit 1
+
+[ "$2" ] && PROMPT="$2"
+
+
+# ----------------------------------------------------------------------
+
+[ ! "$PROMPT" ] && PROMPT='Enter cluster key as 64 hexadecimal characters: '
+
+stty -echo <&"$FD"
+
+echo 1>&"$FD"
+echo -n "$PROMPT" 1>&"$FD"
+read KEY <&"$FD"
+
+stty echo <&"$FD"
+
+if [ "$(expr "$KEY" : '[0-9a-fA-F]*$')" -ne 64 ]
+then	echo 'invalid;  must be 64 hexadecimal characters' 1>&2
+	exit 1
+fi
+
+echo "$KEY"
+
+exit 0
diff --git a/src/backend/crypto/ckey_passphrase.sh.sample b/src/backend/crypto/ckey_passphrase.sh.sample
new file mode 100644
index 0000000000..a5d837b45e
--- /dev/null
+++ b/src/backend/crypto/ckey_passphrase.sh.sample
@@ -0,0 +1,35 @@
+#!/bin/sh
+
+# This uses a passphrase supplied by the user.
+# Do not create any fie with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.
+
+[ "$#" -lt 1 ] && echo "cluster_key_command usage: $0 %R [\"%p\"]" 1>&2 && exit 1
+
+FD="$1"
+[ ! -t "$FD" ] && echo "file descriptor $FD does not refer to a terminal" 1>&2 && exit 1
+# Supports environment variable PROMPT
+
+[ "$2" ] && PROMPT="$2"
+
+
+# ----------------------------------------------------------------------
+
+[ ! "$PROMPT" ] && PROMPT='Enter cluster passphrase: '
+
+stty -echo <&"$FD"
+
+echo 1>&"$FD"
+echo -n "$PROMPT" 1>&"$FD"
+read PASS <&"$FD"
+
+stty echo <&"$FD"
+
+if [ ! "$PASS" ]
+then	echo 'invalid:  empty passphrase' 1>&2
+	exit 1
+fi
+
+echo "$PASS" | sha256sum | cut -d' ' -f1
+
+exit 0
diff --git a/src/backend/crypto/ckey_piv_nopin.sh.sample b/src/backend/crypto/ckey_piv_nopin.sh.sample
new file mode 100644
index 0000000000..e90a579dea
--- /dev/null
+++ b/src/backend/crypto/ckey_piv_nopin.sh.sample
@@ -0,0 +1,68 @@
+#!/bin/sh
+
+# This uses the public/private keys on a PIV device, like a CAC or Yubikey.
+# It  uses a PIN stored in a file.
+# It uses OpenSSL with PKCS11 enabled via OpenSC.
+# This stores the cluster encryption key encrypted with the PIV public
+# key in $DIR.  This is technically a three-level encryption
+# architecture, with the third level requiring the PIV and PIN.
+# Do not create any fie with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.
+
+[ "$#" -ne 1 ] && echo "cluster_key_command usage: $0 \"%d\"" 1>&2 && exit 1
+# Supports environment variable PIV_PIN_FILE
+# No need for %R or -R since we are not prompting for a PIN
+
+DIR="$1"
+[ ! -e "$DIR" ] && echo "$DIR does not exist" 1>&2 && exit 1
+[ ! -d "$DIR" ] && echo "$DIR is not a directory" 1>&2 && exit 1
+
+# Set these here or pass in as environment variables.
+# File that stores the PIN to unlock the PIV
+#PIV_PIN_FILE=''
+# PIV slot 3 is the "Key Management" slot, so we use '0:3'
+PIV_SLOT='0:3'
+
+# File containing the cluster key encrypted with the PIV_SLOT's public key
+KEY_FILE="$DIR/pivpass.key"
+
+
+# ----------------------------------------------------------------------
+
+[ ! "$PIV_PIN_FILE" ] && echo 'PIV_PIN_FILE undefined' 1>&2 && exit 1
+[ ! -e "$PIV_PIN_FILE" ] && echo "$PIV_PIN_FILE does not exist" 1>&2 && exit 1
+[ -d "$PIV_PIN_FILE" ] && echo "$PIV_PIN_FILE is a directory" 1>&2 && exit 1
+
+[ ! "$KEY_FILE" ] && echo 'KEY_FILE undefined' 1>&2 && exit 1
+[ -d "$KEY_FILE" ] && echo "$KEY_FILE is a directory" 1>&2 && exit 1
+
+# Create a cluster key encrypted with the PIV_SLOT's public key?
+if [ ! -e "$KEY_FILE" ]
+then	# The 'postgres' operating system user must have permission to
+	# access the PIV device.
+
+	openssl rand -hex 32 |
+	if ! openssl rsautl -engine pkcs11 -keyform engine -encrypt \
+		-inkey "$PIV_SLOT" -passin file:"$PIV_PIN_FILE" -out "$KEY_FILE"
+	then	echo 'cluster key generation failed' 1>&2
+		exit 1
+	fi
+
+	# Warn the user to save the cluster key in a safe place
+	cat 1>&2 <<END
+
+WARNING:  The PIV device can be locked and require a reset if too many PIN
+attempts fail.  It is recommended to run this command manually and save
+the cluster key in a secure location for possible recovery.
+END
+
+fi
+
+# Decrypt the cluster key encrypted with the PIV_SLOT's public key
+if ! openssl rsautl -engine pkcs11 -keyform engine -decrypt \
+	-inkey "$PIV_SLOT" -passin file:"$PIV_PIN_FILE" -in "$KEY_FILE"
+then	echo 'cluster key decryption failed' 1>&2
+	exit 1
+fi
+
+exit 0
diff --git a/src/backend/crypto/ckey_piv_pin.sh.sample b/src/backend/crypto/ckey_piv_pin.sh.sample
new file mode 100644
index 0000000000..e693ac31ba
--- /dev/null
+++ b/src/backend/crypto/ckey_piv_pin.sh.sample
@@ -0,0 +1,81 @@
+#!/bin/sh
+
+# This uses the public/private keys on a PIV device, like a CAC or Yubikey.
+# It requires a user-entered PIN.
+# It uses OpenSSL with PKCS11 enabled via OpenSC.
+# This stores the cluster encryption key encrypted with the PIV public
+# key in $DIR.  This is technically a three-level encryption
+# architecture, with the third level requiring the PIV and PIN.
+# Do not create any fie with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.
+
+[ "$#" -lt 2 ] && echo "cluster_key_command usage: $0 \"%d\" %R [\"%p\"]" 1>&2 && exit 1
+# Supports environment variable PROMPT
+
+DIR="$1"
+[ ! -e "$DIR" ] && echo "$DIR does not exist" 1>&2 && exit 1
+[ ! -d "$DIR" ] && echo "$DIR is not a directory" 1>&2 && exit 1
+
+FD="$2"
+[ ! -t "$FD" ] && echo "file descriptor $FD does not refer to a terminal" 1>&2 && exit 1
+
+[ "$3" ] && PROMPT="$3"
+
+# PIV slot 3 is the "Key Management" slot, so we use '0:3'
+PIV_SLOT='0:3'
+
+# File containing the cluster key encrypted with the PIV_SLOT's public key
+KEY_FILE="$DIR/pivpass.key"
+
+
+# ----------------------------------------------------------------------
+
+[ ! "$PROMPT" ] && PROMPT='Enter PIV PIN: '
+
+stty -echo <&"$FD"
+
+# Create a cluster key encrypted with the PIV_SLOT's public key?
+if [ ! -e "$KEY_FILE" ]
+then	echo 1>&"$FD"
+	echo -n "$PROMPT" 1>&"$FD"
+
+	# The 'postgres' operating system user must have permission to
+	# access the PIV device.
+
+	openssl rand -hex 32 |
+	# 'engine "pkcs11" set.' message confuses prompting
+	if ! openssl rsautl -engine pkcs11 -keyform engine -encrypt \
+		-inkey "$PIV_SLOT" -passin fd:"$FD" -out "$KEY_FILE" 2>&1
+	then	stty echo <&"$FD"
+		echo 'cluster key generation failed' 1>&2
+		exit 1
+	fi | grep -v 'engine "pkcs11" set\.'
+
+	echo 1>&"$FD"
+
+	# Warn the user to save the cluster key in a safe place
+	cat 1>&"$FD" <<END
+
+WARNING:  The PIV can be locked and require a reset if too many PIN
+attempts fail.  It is recommended to run this command manually and save
+the cluster key in a secure location for possible recovery.
+END
+
+fi
+
+echo 1>&"$FD"
+echo -n "$PROMPT" 1>&"$FD"
+
+# Decrypt the cluster key encrypted with the PIV_SLOT's public key
+if ! openssl rsautl -engine pkcs11 -keyform engine -decrypt \
+	-inkey "$PIV_SLOT" -passin fd:"$FD" -in "$KEY_FILE" 2>&1
+then	stty echo <&"$FD"
+	echo 'cluster key retrieval failed' 1>&2
+	exit 1
+fi | grep -v 'engine "pkcs11" set\.'
+
+echo 1>&"$FD"
+
+stty echo <&"$FD"
+
+exit 0
diff --git a/src/backend/crypto/ssl_passphrase.sh.sample b/src/backend/crypto/ssl_passphrase.sh.sample
new file mode 100644
index 0000000000..efbf5c0720
--- /dev/null
+++ b/src/backend/crypto/ssl_passphrase.sh.sample
@@ -0,0 +1,35 @@
+#!/bin/sh
+
+# This uses a passphrase supplied by the user.
+# Do not create any fie with extension "wkey" in $DIR;  these are
+# reserved for wrapped data key files.
+
+[ "$#" -lt 1 ] && echo "ssl_passphrase_command usage: $0 %R [\"%p\"]" 1>&2 && exit 1
+
+FD="$1"
+[ ! -t "$FD" ] && echo "file descriptor $FD does not refer to a terminal" 1>&2 && exit 1
+# Supports environment variable PROMPT
+
+[ "$2" ] && PROMPT="$2"
+
+
+# ----------------------------------------------------------------------
+
+[ ! "$PROMPT" ] && PROMPT='Enter cluster passphrase: '
+
+stty -echo <&"$FD"
+
+echo 1>&"$FD"
+echo -n "$PROMPT" 1>&"$FD"
+read PASS <&"$FD"
+
+stty echo <&"$FD"
+
+if [ ! "$PASS" ]
+then	echo 'invalid:  empty passphrase' 1>&2
+	exit 1
+fi
+
+echo "$PASS"
+
+exit 0
-- 
2.20.1

--gatW/ieO32f1wygP
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="cfe-04-common_over_cfe-03-scripts.diff"