Re: Proposed patch for key managment - Mailing list pgsql-hackers

On Wed, Dec  9, 2020 at 08:40:50PM -0500, Bruce Momjian wrote:
> My next task is to write a script for Yubikey authentication.

I know Craig Ringer wanted Yubikey support, which allows two-factor
authentication, so I have added it to the most recent patch by adding a
cluster_passphrase_command %d/directory parameter:

    https://github.com/postgres/postgres/compare/master...bmomjian:key.diff

You can also store the PIN in a file, so you don't need a PIN to be
entered by the user for each server start.  Attached is the script I
with a PIN required.  Here is a session:

    $ initdb -K 256 -R -c '/u/postgres/tmp/pass_yubi.sh %R "%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 ...
    Enter Yubikey PIN:
    
    WARNING:  The Yubikey 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.
    
-->    Enter Yubikey PIN:
    ok
    performing post-bootstrap initialization ...
-->    Enter Yubikey PIN:
    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


    $ pg_ctl -R -l /u/pg/server.log start
    waiting for server to start...
-->    Enter Yubikey PIN:
     done
    server started

It even allows for passphrase rotation using my pg_altercpass tool with
this patch:

        https://github.com/bmomjian/postgres/compare/key...bmomjian:key-alter.diff

The Yubikey-encrypted passphrase is stored in the key directory, so the
encrypted passphrase stays with the data/WAL keys during passphrase
rotation:

    $ pg_altercpass -R '/u/postgres/tmp/pass_yubi.sh %R "%d"' '/u/postgres/tmp/pass_yubi.sh %R "%d"'
    
-->    Enter Yubikey PIN:
    
-->    Enter Yubikey PIN:
    
    WARNING:  The Yubikey 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.
    
-->    Enter Yubikey PIN:

Yubikey PIN rotation has to be done using the Yubikey tool, and data/WAL
key rotation has to be done via switching to a standby, which hasn't
been implemented yet.

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

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