0

So, as I alluded to in a past question, I moved the database to a new server, but the DB can't be accessed by the old code. The query error if it helps:

Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user someuser

Now, from I have read online, am I correct in thinking that this requires a modification to the pg_hba.conf file. But! I cannot find this file. My database cluster is stored in /var/lib/postgresql/9.0, but there is no pg_hba file there! Someone might have moved it away from the default directory, and stored it who knows where.

So, my question is that, can I fix the user not authenticated problem by editing the pg_hba file (I had thought that when you did a pg_dump and restored the DB, authenticated users would come with it, but maybe that's wrong), and if I do need to access the pg_hba file, how can I find it?

I am using ubuntu if it helps. Thanks for everything.

Improve this question
3
  • The 'locate' command will search the entire filesystem for it. Run 'updatedb' as root first to make sure the file locations are up to date.
    – hurfdurf
    Sep 15, 2011 at 19:22
  • I found the file, thanks a lot for that, but now, I can't figure out what I should add to the config file. How do I specify a range of ip addresses?
    – zermy
    Sep 15, 2011 at 19:44
  • IP addresses are specified in CIDR noration. For example, if you wanted to allow 192.168.3.1 to 192.168.3.1.254 to access the DB, you would do 192.168.3.0/24
    – Kendall
    Sep 15, 2011 at 19:46

1 Answer 1

Reset to default
1

Yes, you need the pg_hba.conf file from the old database server. Dumping and restoring the database will not copy this file; all it does it dump and restore the table structures and the data they hold.

The pg_hba.conf file should be located under the data directory; on a default install (at least on SuSE) this would be /var/lib/pgsql/data/pg_hba.conf. On yours I think it will be /var/lib/postgresql/9.0/pg_hba.conf

You will also want to make sure to grab the old postgresql.conf and put it under /var/lib/postgresql/9.0 as well.

You can run

find / -name pg_hba.conf

to search the entire filesystem for files named pg_hba.conf

After putting this file in place, you must reload Postgres for it to take affect.

/etc/init.d/postgresql reload

Improve this answer
2
  • Thanks a lot for the response, the new database location is on the same server that accesses the site, so am I correct in assuming that I only need to modify the local field entry? If I change the access method from ident to trust, what's the likelihood that something else (i.e. another website accessing the postgresql server) will break?
    – zermy
    Sep 15, 2011 at 20:17
  • I'd say low; with trust, you're implicitly trusting the connection for the user from the specified IP. With ident, you're requiring authentication. Trust says "oh hey I see user X wants database Y from IP Z, I'll go ahead and let ya in!"
    – Kendall
    Sep 15, 2011 at 20:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .