up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
50% accept rate
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 '11 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 '11 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 '11 at 19:46
feedback

migrated from stackoverflow.com Sep 15 '11 at 19:39

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote

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

link|improve this answer
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 '11 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 '11 at 20:57
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.