December 20, 2010

Missing Passwords in PostgreSQL

We run a PostgreSQL installation on Red Hat and periodically need to dump our test database and do a full restore from production; a dreadful experience, particularly when no one on the team is responsible for the current implementation. Most of our heavy lifting is done by MongoDB where full database backups consist of two commands, so the prospect of a PostgreSQL backup with a missing pgsql password was not pleasant.

The backup part is easy. I log into the database server and issue the following commands:

pg_dump -U <owner> <database> > data.sql \
&& scp data.sql <user>@<test database server>:/home/<user>/data.sql

A copy of our production database now exists on the test database server. Great. I disconnect from production and connect to our test database server, where things get fun. I enter the command to restore the database:

psql -U <owner> <database> < data.sql

It runs, but a ton of errors and warnings are displayed: "duplicate key value violates unique constraint"; "relation already exists". The database must be dropped first because this is a full backup. Unfortunately, I must connect to PostgreSQL as the super user, pgsql, in order to drop the database, but the password has been lost. From root, I set pgsql's user password with:

root> passwd pgsql

Great. Now I log in with the pgsql user and tell PostgreSQL to use trusted authentication instead of password authentication (i.e. PostgreSQL will log me into postgres with the current shell user assuming prior authentication) by saving the current pg_hba.conf file and restoring the original pg_hba.conf file. Basically, I want that file to temporarily look like this:

local all all trust
host all all trust
host all all ::1/128 trust

After restarting the database, the following command gets me into the postgres shell:

pgsql> psql postgres

The first thing I do is set a pgsql password so I don't have to dance with .conf files in the future, then I drop and create an empty database to restore:

postgres=# alter user pgsql with password '<password>';
postgres=# drop database <database>;
postgres=# create database <database> owner <owner>;

I exit the shell, then restore the correct pg_hba.conf and restart the database. Once back, I execute:

psql -U <owner> <database> < data.sql

Success! Fun times? Fun times.

No comments: