Banner password management for batch jobs

DOCUMENT IN PROGRESS

GoPass is a command-line password manager written in the Go language. It is licensed under the MIT license, which allows for free use of the software and the ability to modify the source code if desired, with minimal restrictions. It supports all major operating systems.

The software uses GPG for password encryption, and includes integration with git, making it a potentially ideal solution for managing Banner database passwords used by the system. With this in mind, we used GoPass to create the initial password store; however, during testing, we discovered that the software was apparently designed with the thought that it would be used interactively. Since Oakland needs to be able to manage passwords in batch, we created scripts that use native GPG commands to query and modify the password store instead. This document describes the use of the password store, followed by details of the setup in Oakland's Banner environment.

Adding a password to the password store

This should only be done from the banner account on the jobsub server, but could be done by anyone with dba privilege.

To manually enter a new password or to edit an existing password:

/banner/gopass/editkey <username>

The username will be converted to upper case. You will be prompted for the password twice. The password can be mixed case, and must follow our current guidelines for Banner passwords.

The editkey script will automatically push the password change to GitLab.

Adding multiple passwords to the password store

This should only be done from the banner account on the jobsub server!

To import multiple passwords into the password store, create an input file containing usernames and passwords, in the format "USERNAME/password", with one username and password per line. Then, enter:

/banner/gopass/importkeys [ -d <database_SID> ] [ -k <filename> ]

In the above command, "-d <database_SID" is optional; if omitted, your current ORACLE_SID value is used. Also, "-k <filename>" is optional; if omitted, the $BANNER_HOME/.siboleth file is used as input. If your repository previously contained a password for a given USERNAME, it will be overwritten with the one from your input file.

Deleting a password in the password store

This should only be done from the banner account on the jobsub server, or from an account with dba privileges

To delete a password from the password store:

/banner/gopass/editkey -x <username>

Retrieving a password from the password store

Any user in either the dba or pgmr groups on the Banner job submission server can query the password safe using the banpass and banidpw commands.

To get a password only, enter:

banpass <username_string>

where <username_string> can be any of "<username>", "<username>@<database>", or "<database>/<username>". If you omit the <database> portion, the default is your current ORACLE_SID.

To get the password in the format USERNAME/password, enter:

banidpw <username_string>

If you need a user on a different server to be able to access these commands, you must add that user's public SSH key to the /banner/.ssh/authorized_keys file on the Banner jobsub server. You would then access the above commands using SSH. For example:

ssh [email protected] banpass <username_string>

When accessing from a remote server, you must use a form of the <username_string> that includes <database>.

Handling invalid passwords in the password store

If you are concerned that the passwords in the password store do not match what is in the database, you may run:

/banner/upgrade/fixorapass.shl [ -u <username> ] [ -f ]

This command will try all of the passwords defined in the password store (or just the password for <username> if the -u switch is used), and offer to run a script to fix any discrepancies by changing the user password to match what is in the password store. Accounts which are locked or which do not have restricted session privilege (if restricted session is enabled) will fail verification, and fixorapass.shl will not attempt to fix passwords for such accounts unless the -f switch is used. NOTE: -f does NOT force the passwords to be changed; it merely allows you to change passwords even if you are unable to verify whether the password in the store is currently invalid. You will always be prompted before any passwords are changed.

NOTE: Use of $BANNER_HOME/.siboleth is DEPRECATED! During the transition from using $BANNER_HOME/.siboleth to using the password store, scripts that modify database passwords are maintaining both. If you manually update one without the other (i.e. update a password in .siboleth without also performing editkey, or vice versa), the .siboleth file and the password store will become out of sync. You can synchronize them by running the following command:

/banner/gopass/comparekeys

This will compare all keys defined in $BANNER_HOME/.siboleth to those stored in the password store, and if it finds any differences, it will offer to set the password store back to the values in .siboleth.

Setup

This describes how the GoPass software was installed and configured.

1. Install GoPass

To install GoPass, we performed the following steps:

  1. Download the most recent version of GoPass from the source. At the time of installation, this was found at https://github.com/gopasspw/gopass/releases/tag/v1.9.2/.

  2. Unpack the software into /opt/gopass.

2. Create a GPG key

To create a GPG key, you must log on directly to the user account where you are creating it. You may NOT use su or sudo from another account to become that user and create a key.

NOTE: You should NOT do this when installing GoPass on a new jobsub server; instead, migrate the key as described below.

These are the steps that were used to create the GPG key for the banner account on the Banner job submission server.

Enter "gpg --gen-key" and respond to the prompts:

  1. select 1 (RSA and DSA)
  2. enter 2048 for key size
  3. enter 0 so key does not expire
  4. enter Y to confirm
  5. enter a real name - use "banner"
  6. enter an email address - use "[email protected]"

  7. comment is optional
  8. enter O for Okay
  9. enter a passphrase
  10. confirm the passphrase
  11. enter random string to create entropy (you do NOT need to remember this string for later) - this will take a long time

3. Create a Git repository

These are the steps followed to create a Git repository for the password store:

In GitLab, create an empty repository in the BannerDBA group:

  1. Select Groups from the top menu bar, and find the BannerDBA group.
  2. Click New Project
  3. Name the group appropriately (e.g. "TEST_Passwords")
  4. Make sure project is private
  5. Clear Initialize project with a README
  6. Click Create Project
  7. Ensure that banner user has its SSH public key associated with an account (NOTE: banner on both banjobs5 and banjobs6 is currently associated with the SIGDBA account; if that account is ever removed, you will need to associate the banner user's SSH public key with another account that has owner or maintainer privileges in the BannerDBA group).

4. Create the password store

Here are the steps followed to create the password store:

As banner on jobsub server, enter "/opt/gopass/gopass" and follow the prompts:

  1. enter Y to use the wizard
  2. select Local Store
  3. enter 0 to use the GPG key you created earlier
  4. enter Y to use "[email protected]" for password store git config

  5. enter N to add a git remote - it will be added later
  6. enter N to not confirm recipients

We configured GoPass to perform automatic syncing of the git repository with GitLab; however, since we are no longer using GoPass directly, this is irrelevant. The command we used for configuration was:

/opt/gopass/gopass config autosync true

5. Associate the password store with the Git repository

As banner on jobsub server:

  1. cd ~/.password-store
  2. git remote add origin [email protected]:BannerDBA/<project_name>.git

  3. git push -u origin --all
  4. create a README.md file with appropriate comments
  5. create a .siboleth file with the GPG key's passphrase
  6. git add README.md
  7. git add .siboleth
  8. git commit -m "add README and .siboleth"
  9. git push -u origin --all

NOTE: Yes, we added the GPG key's passphrase to the Git repository! However, if you don't have also have the public and secret keys, the passphrase is of little use to you. This is why we made access to the repository private, and why only the banner user has access to read the passphrase on the jobsub server.

6. Load the password store

Load the $BANNER_HOME/.siboleth file into the password store with:

/banner/gopass/importkeys

Migrating to a new server

NOTE: This procedure assumes that you do not already have a password store in the new location!

At some point (for example, when Banner job submission is migrated to a new server), you will need to install GoPass there as described above, and you will also need to move the password store. This procedure will allow you to do that.

NOTE: This procedure could also be used to clone the repository to another location on the same or a different server, though this is not recommended, as the copy owned by banner on the Banner jobsub server is the source used by job submission processes, while the repository on GitLab is only a backup, and any other clones cannot be trusted.

1. Obtain the private key

Copy the keyring from the old server (recommended)

NOTE: This procedure assumes that you do NOT already have a gpg keyring on the new server!

As banner on the old server:

tar zcvf gnupg_backup.tgz ~/.gnupg
chmod 600 gnupg_backup.tgz

As banner on the new server:

scp -p old_server:gnupg_backup.tgz .
tar zxvf gnupg_backup.tgz

Import the keys from the banner user (not recommended)

NOTE: This procedure assumes that you already have a keyring, or that you are making a clone of the primary password store as an additional, offline backup. If this is an offline backup, you should not use it for actual work.

As banner on the jobsub server:, export the public key and the secret key, as follows:

gpg --armor --output bannerpubkey --export [email protected]
gpg --armor --output bannersecretkey --export-secret-key [email protected]
chmod 600 bannersecretkey

Then, as the other user, transfer both keys from banner on the jobsub server to your user account on the same or a different server, and import them into your existing GPG keyring, as follows:

gpg --import bannerpubkey
gpg --import bannersecretkey
rm bannersecretkey

2. Clone the password store

After you have the keyring, you will need to clone the password store. You must not have a previously existing password store. Clone the store as follows:

cd ~
git clone [email protected]:BannerDBA/<store_name>.git
mv <store_name> .password-store

where <store_name> is the name of your repository.

If this is a backup of the main repository, you should regularly keep it up-to-date by performing:

git pull origin master

DataAdminHowTo

DB_Administration