From: Bruce Momjian <bruce@momjian.us>
To: Neil Chen <carpenter.nail.cz@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Subject: Re: Proposed patch for key managment
Date: Thu, 17 Dec 2020 14:02:37 -0500
Message-ID: <20201217190237.GH23260@momjian.us> (raw)
In-Reply-To: <20201215041618.GC14596@momjian.us>
References: <20201202213814.GG20285@momjian.us>
<20201214230615.GA14596@momjian.us>
<CAA3qoJ=qtO5JcSBjqFDBT9iKUX9XKmC5bXCrd7rysE+XSMEuTg@mail.gmail.com>
<20201215041618.GC14596@momjian.us>
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
Attachments:log (text/plain, 9.5K) download
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
pass_aws.sh (application/x-sh, 1.2K) download
pass_fd.sh (application/x-sh, 282B) download
pass_piv_nopin.sh (application/x-sh, 1.4K) download
pass_piv_pin.sh (application/x-sh, 1.6K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bruce@momjian.us, carpenter.nail.cz@gmail.com, masahiko.sawada@2ndquadrant.com
Subject: Re: Proposed patch for key managment
In-Reply-To: <20201217190237.GH23260@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox