Date: Thu, 17 Dec 2020 14:02:37 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Neil Chen <carpenter.nail.cz@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Subject: Re: Proposed patch for key managment
Message-ID: <20201217190237.GH23260@momjian.us>
References: <20201202213814.GG20285@momjian.us>
 <20201214230615.GA14596@momjian.us>
 <CAA3qoJ=qtO5JcSBjqFDBT9iKUX9XKmC5bXCrd7rysE+XSMEuTg@mail.gmail.com>
 <20201215041618.GC14596@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="HlL+5n6rz5pIUxbD"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20201215041618.GC14596@momjian.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--HlL+5n6rz5pIUxbD
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Mon, Dec 14, 2020 at 11:16:18PM -0500, Bruce Momjian wrote:
> On Tue, Dec 15, 2020 at 10:36:56AM +0800, Neil Chen wrote:
> > Since our implementation is not in contrib, I don't think we should put the
> > script there. Maybe we can refer to postgresql.conf.sample?  
> 
> Uh, the script are 20-60 lines long --- I am attaching them to this
> email.  Plus, when we allow user prompting for the SSL passphrase, we
> will have another script, or maybe three mor if people want to use a
> Yubikey to unlock the SSL passphrase.

Here is a run of all four authentication methods, and updated scripts. 
I have renamed Yubiki to PIV since the script should work with anY
PIV-enabled deviced, like a CAC.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee


--HlL+5n6rz5pIUxbD
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=log


aws_test.sh
------------------------------------------------------------------

+ cp /pgdev/cfe/pass_aws.sh /u/postgres/tmp
+ pgstop -w
waiting for server to shut down.... done
server stopped
+ rm -rf /u/pg/data/PG_VERSION /u/pg/data/base /u/pg/data/global /u/pg/data/pg_commit_ts /u/pg/data/pg_cryptokeys /u/pg/data/pg_dynshmem /u/pg/data/pg_hba.conf /u/pg/data/pg_ident.conf /u/pg/data/pg_logical /u/pg/data/pg_multixact /u/pg/data/pg_notify /u/pg/data/pg_replslot /u/pg/data/pg_serial /u/pg/data/pg_snapshots /u/pg/data/pg_stat /u/pg/data/pg_stat_tmp /u/pg/data/pg_subtrans /u/pg/data/pg_tblspc /u/pg/data/pg_twophase /u/pg/data/pg_wal /u/pg/data/pg_xact /u/pg/data/postgresql.auto.conf /u/pg/data/postgresql.conf /u/pg/data/postmaster.opts
+ rm -rf /u/postgres/.aws
+ mkdir /u/postgres/.aws
+ cp /root/.aws/AWS-ssh.pem /root/.aws/README /root/.aws/config /root/.aws/credentials /u/postgres/.aws
+ chown postgres.postgres /u/postgres/.aws/AWS-ssh.pem /u/postgres/.aws/README /u/postgres/.aws/config /u/postgres/.aws/credentials
+ aspg initdb -K 256 -R -c /u/postgres/tmp/pass_aws.sh "%d"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Cluster file encryption is enabled.

fixing permissions on existing directory /u/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /u/pgsql/data -l logfile start

+ aspg pg_ctl -R -l /u/pg/server.log start
waiting for server to start... done
server started
+ aspg pg_altercpass -R /u/postgres/tmp/pass_aws.sh "%d" /u/postgres/tmp/pass_aws.sh "%d"

+ aspg pg_altercpass -r
repair unnecessary
+ rm -rf /u/postgres/.aws

key_test.sh
------------------------------------------------------------------

+ cp /pgdev/cfe/pass_fd.sh /u/postgres/tmp
+ pgstop -w
waiting for server to shut down.... done
server stopped
+ rm -rf /u/pg/data/PG_VERSION /u/pg/data/base /u/pg/data/global /u/pg/data/pg_commit_ts /u/pg/data/pg_cryptokeys /u/pg/data/pg_dynshmem /u/pg/data/pg_hba.conf /u/pg/data/pg_ident.conf /u/pg/data/pg_logical /u/pg/data/pg_multixact /u/pg/data/pg_notify /u/pg/data/pg_replslot /u/pg/data/pg_serial /u/pg/data/pg_snapshots /u/pg/data/pg_stat /u/pg/data/pg_stat_tmp /u/pg/data/pg_subtrans /u/pg/data/pg_tblspc /u/pg/data/pg_twophase /u/pg/data/pg_wal /u/pg/data/pg_xact /u/pg/data/postgresql.auto.conf /u/pg/data/postgresql.conf /u/pg/data/postmaster.opts
+ aspg initdb -K 256 -R -c /u/postgres/tmp/pass_fd.sh %R "%P"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Cluster file encryption is enabled.

fixing permissions on existing directory /u/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /u/pgsql/data -l logfile start

+ aspg pg_ctl -R -l /u/pg/server.log start
waiting for server to start... done
server started
+ aspg pg_altercpass -R /u/postgres/tmp/pass_fd.sh %R "Enter old cluster passphrase:" /u/postgres/tmp/pass_fd.sh %R "Enter new cluster passphrase:"

+ aspg pg_altercpass -r
repair unnecessary

piv_test_nopin.sh
------------------------------------------------------------------

+ cp /pgdev/cfe/pass_piv_nopin.sh /u/postgres/tmp
+ pgstop -w
waiting for server to shut down.... done
server stopped
+ rm -rf /u/pg/data/PG_VERSION /u/pg/data/base /u/pg/data/global /u/pg/data/pg_commit_ts /u/pg/data/pg_cryptokeys /u/pg/data/pg_dynshmem /u/pg/data/pg_hba.conf /u/pg/data/pg_ident.conf /u/pg/data/pg_logical /u/pg/data/pg_multixact /u/pg/data/pg_notify /u/pg/data/pg_replslot /u/pg/data/pg_serial /u/pg/data/pg_snapshots /u/pg/data/pg_stat /u/pg/data/pg_stat_tmp /u/pg/data/pg_subtrans /u/pg/data/pg_tblspc /u/pg/data/pg_twophase /u/pg/data/pg_wal /u/pg/data/pg_xact /u/pg/data/postgresql.auto.conf /u/pg/data/postgresql.conf /u/pg/data/postmaster.opts
+ cp /root/.yubikey/piv.pin /u/postgres/tmp
+ chown postgres /u/postgres/tmp/piv.pin
+ aspg initdb -K 256 -R -c /u/postgres/tmp/pass_piv_nopin.sh "%d"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Cluster file encryption is enabled.

fixing permissions on existing directory /u/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... engine "pkcs11" set.

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 passphrase in a secure location for possible recovery.
engine "pkcs11" set.
ok
performing post-bootstrap initialization ... engine "pkcs11" set.
ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /u/pgsql/data -l logfile start

+ aspg pg_ctl -R -l /u/pg/server.log start
waiting for server to start... done
server started
+ aspg pg_altercpass -R /u/postgres/tmp/pass_piv_nopin.sh "%d" /u/postgres/tmp/pass_piv_nopin.sh "%d"
engine "pkcs11" set.
engine "pkcs11" set.

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 passphrase in a secure location for possible recovery.
engine "pkcs11" set.

+ aspg pg_altercpass -r
repair unnecessary

piv_test_pin.sh
------------------------------------------------------------------

+ cp /pgdev/cfe/pass_piv_pin.sh /u/postgres/tmp
+ pgstop -w
waiting for server to shut down.... done
server stopped
+ rm -rf /u/pg/data/PG_VERSION /u/pg/data/base /u/pg/data/global /u/pg/data/pg_commit_ts /u/pg/data/pg_cryptokeys /u/pg/data/pg_dynshmem /u/pg/data/pg_hba.conf /u/pg/data/pg_ident.conf /u/pg/data/pg_logical /u/pg/data/pg_multixact /u/pg/data/pg_notify /u/pg/data/pg_replslot /u/pg/data/pg_serial /u/pg/data/pg_snapshots /u/pg/data/pg_stat /u/pg/data/pg_stat_tmp /u/pg/data/pg_subtrans /u/pg/data/pg_tblspc /u/pg/data/pg_twophase /u/pg/data/pg_wal /u/pg/data/pg_xact /u/pg/data/postgresql.auto.conf /u/pg/data/postgresql.conf /u/pg/data/postmaster.opts
+ aspg initdb -K 256 -R -c /u/postgres/tmp/pass_piv_pin.sh "%d" %R
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Cluster file encryption is enabled.

fixing permissions on existing directory /u/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /u/pgsql/data -l logfile start

+ aspg pg_ctl -R -l /u/pg/server.log start
waiting for server to start... done
server started
+ aspg pg_altercpass -R /u/postgres/tmp/pass_piv_pin.sh "%d" %R /u/postgres/tmp/pass_piv_pin.sh "%d" %R

+ aspg pg_altercpass -r
repair unnecessary

--HlL+5n6rz5pIUxbD
Content-Type: application/x-sh
Content-Disposition: attachment; filename="pass_aws.sh"
Content-Transfer-Encoding: quoted-printable

#!/bin/sh=0A=0A# This uses the AWS Secrets Manager using the AWS CLI=0A=0A[=
 "$#" -ne 1 ] && echo "cluster_passphrase_command usage: $0 \"%d\"" 1>&2 &&=
 exit 1=0A# No need for %R or -R since we are not prompting=0A=0ADIR=3D"$1"=
=0A=0A# File containing the passphrased encrypted with the PIV_SLOT's publi=
c key=0AAWS_ID_FILE=3D"$DIR/aws-secret.id"=0A=0A=0A# Create an AWS Secrets =
Manager secret?=0Aif [ ! -e "$AWS_ID_FILE" ]=0Athen	# The 'postgres' operat=
ing system user must have permission to=0A	# access the AWS CLI=0A=0A	# The=
 epoch-time/directory/hostname combination is unique=0A	HASH=3D$(echo -n "$=
(date '+%s')$DIR$(hostname)" | sha1sum | awk '{print $1}')=0A	AWS_SECRET_ID=
=3D"Postgres-passphrase-$HASH"=0A=0A	# Use stdin to avoid passing the secre=
t on the command line=0A	openssl rand -hex 32 |=0A	xxd -plain -cols 999 |=
=0A	aws secretsmanager create-secret \=0A		--name "$AWS_SECRET_ID" \=0A		--=
description 'Used for Postgres cluster file encryption' \=0A		--secret-stri=
ng 'file:///dev/stdin' \=0A		--output text > /dev/null=0A	[ "$?" -ne 0 ] &&=
 exit 1=0A=0A	echo "$AWS_SECRET_ID" > "$AWS_ID_FILE"=0Afi=0A=0Aif ! aws sec=
retsmanager get-secret-value \=0A	--secret-id "$(cat "$AWS_ID_FILE")" \=0A	=
--output text=0Athen	exit 1=0Afi | awk -F'\t' 'NR =3D=3D 1 {print $4}'=0A=
=0Aexit 0=0A
--HlL+5n6rz5pIUxbD
Content-Type: application/x-sh
Content-Disposition: attachment; filename="pass_fd.sh"
Content-Transfer-Encoding: quoted-printable

#!/bin/sh=0A=0A# This uses a passphrase supplied by the user=0A=0A[ "$#" -n=
e 2 ] && echo "cluster_passphrase_command usage: $0 %R \"%P\"" 1>&2 && exit=
 1=0A=0AFD=3D"$1"=0AMSG=3D"$2"=0A=0A=0Astty -echo <&"$FD"=0A=0Aecho 1>&"$FD=
"=0Aecho -n "$MSG" 1>&"$FD"=0Aread PASS <&"$FD"=0A=0Astty echo <&"$FD"=0A=
=0Aecho "$PASS"=0A=0Aexit 0=0A
--HlL+5n6rz5pIUxbD
Content-Type: application/x-sh
Content-Disposition: attachment; filename="pass_piv_nopin.sh"
Content-Transfer-Encoding: quoted-printable

#!/bin/sh=0A=0A# This uses the public/private keys on a PIV device, like a =
CAC or Yubikey.=0A# It  uses a PIN stored in a file.=0A=0A[ "$#" -ne 1 ] &&=
 echo "cluster_passphrase_command usage: $0 \"%d\"" 1>&2 && exit 1=0A# No n=
eed for %R or -R since we are not prompting for a PIN=0A=0ADIR=3D"$1"=0A=0A=
# File that stores the PIN to unlock the PIV=0APIN_FILE=3D'/u/postgres/tmp/=
piv.pin'=0A=0A# PIV slot 3 is the "Key Management" slot=0APIV_SLOT=3D'0:3'=
=0A=0A# File containing the passphrased encrypted with the PIV_SLOT's publi=
c key=0AKEY_FILE=3D"$DIR/pivpass.key"=0A=0A=0A# Create a passphrased encryp=
ted with the PIV_SLOT's public key?=0Aif [ ! -e "$KEY_FILE" ]=0Athen	# The =
'postgres' operating system user must have permission to=0A	# access the PI=
V device.=0A=0A	openssl rand -hex 32 |=0A	xxd -plain -cols 999 |=0A	if ! op=
enssl rsautl -engine pkcs11 -keyform engine -encrypt \=0A		-inkey "$PIV_SLO=
T" -passin file:"$PIN_FILE" -out "$KEY_FILE"=0A	then	exit 1=0A	fi=0A=0A	# W=
arn the user to save the passphrase in a safe place=0A	cat 1>&2 <<END=0A=0A=
WARNING:  The PIV device can be locked and require a reset if too many PIN=
=0Aattempts fail.  It is recommended to run this command manually and save=
=0Athe passphrase in a secure location for possible recovery.=0AEND=0A=0Afi=
=0A=0A# Decrypt the passphrased encrypted with the PIV_SLOT's public key=0A=
if ! openssl rsautl -engine pkcs11 -keyform engine -decrypt \=0A	-inkey "$P=
IV_SLOT" -passin file:"$PIN_FILE" -in "$KEY_FILE"=0Athen	exit 1=0Afi=0A=0Ae=
xit 0=0A
--HlL+5n6rz5pIUxbD
Content-Type: application/x-sh
Content-Disposition: attachment; filename="pass_piv_pin.sh"
Content-Transfer-Encoding: quoted-printable

#!/bin/sh=0A=0A# This uses the public/private keys on a PIV device, like a =
CAC or Yubikey.=0A# It requires a use-entered PIN.=0A=0A[ "$#" -ne 2 ] && e=
cho "cluster_passphrase_command usage: $0 \"%d\" %R" 1>&2 && exit 1=0A=0ADI=
R=3D"$1"=0AFD=3D"$2"=0A=0A# PIV slot 3 is the "Key Management" slot=0APIV_S=
LOT=3D'0:3'=0A=0A# File containing the passphrased encrypted with the PIV_S=
LOT's public key=0AKEY_FILE=3D"$DIR/pivpass.key"=0A=0AMSG=3D'Enter PIV PIN:=
'=0A=0A=0Astty -echo <&"$FD"=0A=0A# Create a passphrased encrypted with the=
 PIV_SLOT's public key?=0Aif [ ! -e "$KEY_FILE" ]=0Athen	echo 1>&"$FD"=0A	e=
cho -n "$MSG" 1>&"$FD"=0A=0A	# The 'postgres' operating system user must ha=
ve permission to=0A	# access the PIV device.=0A=0A	openssl rand -hex 32 |=
=0A	xxd -plain -cols 999 |=0A	# 'engine "pkcs11" set.' message confuses pro=
mpting=0A	if ! openssl rsautl -engine pkcs11 -keyform engine -encrypt \=0A	=
	-inkey "$PIV_SLOT" -passin fd:"$FD" -out "$KEY_FILE" 2>&1=0A	then	stty ech=
o <&"$FD"=0A		exit 1=0A	fi | grep -v 'engine "pkcs11" set\.'=0A=0A	echo 1>&=
"$FD"=0A=0A	# Warn the user to save the passphrase in a safe place=0A	cat 1=
>&"$FD" <<END=0A=0AWARNING:  The PIV can be locked and require a reset if t=
oo many PIN=0Aattempts fail.  It is recommended to run this command manuall=
y and save=0Athe passphrase in a secure location for possible recovery.=0AE=
ND=0A=0Afi=0A=0Aecho 1>&"$FD"=0Aecho -n "$MSG" 1>&"$FD"=0A=0A# Decrypt the =
passphrased encrypted with the PIV_SLOT's public key=0Aif ! openssl rsautl =
-engine pkcs11 -keyform engine -decrypt \=0A	-inkey "$PIV_SLOT" -passin fd:=
"$FD" -in "$KEY_FILE" 2>&1=0Athen	stty echo <&"$FD"=0A	exit 1=0Afi | grep -=
v 'engine "pkcs11" set\.'=0A=0Aecho 1>&"$FD"=0A=0Astty echo <&"$FD"=0A=0Aex=
it 0=0A
--HlL+5n6rz5pIUxbD--