How can I recover the pivotal user after accidental deletion?

The pivotal user should never be deleted or altered, as it has privileges on the users container that no other identity has, namely the CREATE ACE for the global users container.

If you are already in the situation where the pivotal user has been deleted, you can recover the pivotal user with the following annotated steps.

# create pivotal's public key from /etc/opscode/pivotal.pem and store in an accessible location
openssl rsa -in /etc/opscode/pivotal.pem -pubout > /var/opt/opscode/postgresql/9.2/data/pivotal.pub

# get the pivotal user's authz_id and store in an accessible location
echo "SELECT authz_id FROM auth_actor WHERE id = 1" | su -l opscode-pgsql -c 'psql bifrost -tA' | tr -d '\n' > /var/opt/opscode/postgresql/9.2/data/pivotal.authz_id

# create the pivotal user's record 
echo "INSERT INTO users (id, authz_id, username, email, pubkey_version, public_key, serialized_object, last_updated_by, created_at, updated_at) VALUES (md5(random()::text), pg_read_file('pivotal.authz_id'), 'pivotal', '[e-mail]', 0, pg_read_file('pivotal.pub'), '{\"first_name\":\"Clark\",\"last_name\":\"Kent\",\"display_name\":\"Clark Kent\"}', pg_read_file('pivotal.authz_id'), LOCALTIMESTAMP, LOCALTIMESTAMP);" | su -l opscode-pgsql -c 'psql opscode_chef'

# delete the temporary files
rm /var/opt/opscode/postgresql/9.2/data/pivotal.pub /var/opt/opscode/postgresql/9.2/data/pivotal.authz_id

If you are getting an error like this when doing org-list and other privileged chef-server-ctl commands

# chef-server-ctl org-list 
ERROR: Failed to authenticate to https://127.0.0.1:443 as pivotal with key /tmp/latovip20200203-12060-1k2776d 
Response: Invalid signature for user or client 'pivotal'

Then, if you have a pivotal user in your database, but the /etc/opscode/pivotal.pem doesn't match the public key stored in the keys table for that user on a Chef Server 12.6.x and up system, you can update that key like this after doing the openssl rsa command in the first command line example above to get your public key out in the format we will need to update the database.

echo "SELECT id FROM users WHERE username='pivotal'" | su -l opscode-pgsql -c 'psql opscode_chef -tA' | tr -d '\n' > /var/opt/opscode/postgresql/9.2/data/pivotal_id
echo "UPDATE keys SET (public_key) = (pg_read_file('pivotal.pub')) WHERE id=(pg_read_file('pivotal_id'))" | su -l opscode-pgsql -c 'psql opscode_chef -tA'