Regaining user access after restoring a PostgreSQL backup made with dumpall

Usual, frequent backups – containing just about everything – are highly favoured by a wide-range of sysadmins. In some scenarios, it is more than enough to just issue a full backup (including the kitchen sink) then to make selective and separate dumps from parts of the database, based on the size and incremency of the database.

When restoring a dump made with pg_dumpall to a freshly built host, the following – most uncomfortable – situation had risen:

– authentication failed for logging in with one of the users
– remote access via pgAdmin III was denied

To remedy the situation, the following steps were made:

 

1. Change the accepted addresses of the PostgreSQL server

 # nano /etc/postgresql/8.4/main/pg_hba.conf

>> add this line:

host all all [your_ip_address_pool]/24 password

 

2. Change the listen address(es) of the PostgreSQL server

# nano /etc/postgresql/8.4/main/postgresql.conf

>> find and change this line:

listen_addresses=’localhost’

>> to this:

listen_addresses=”[the_ip_you_want_to_bind_it_to]”

 

3. Alternatively, change the (re-)password of the login user

# sudo postgres psql

postgres=# ALTER USER dbuser WITH PASSWORD ‘password’;

>> also let’s re-enable the login feature
postgres=# ALTER USER dbuser WITH LOGIN;

 

 


Thanks for reading! Please consider Buying me a Coffee or checking out Colibri!