I have a postgresql (9.1.4) server running on a remote machine (Ubuntu 12.04), and I'm having trouble accessing it on my local machine. Specifically, on my local machine, I can access the remote server using only the username "postgres" and the database name "postgres", even though I have other user names and databases. First some of the background information. While in the interactive environment invoked with "psql" on the remote machine, I created another user, called "mxtxdb", and set the password for that user. I also created a database called "mxtxdb". To demonstrate their existence, I logged onto the remote machine and ran:

sudo su postgres
Password:
postgres@myhost:~$ psql                                  
psql (9.1.4)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 mxtxdb    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mxtxdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | mxtxdb=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Furthermore, while still on the remote machine, I can access the database from the "mxtxdb" postgresql account:

postgres@myhost:~$ psql -h localhost -U mxtxdb -d mxtxdb
Password for user mxtxdb: 
psql (9.1.4)
Type "help" for help.

mxtxdb=>

But when I try to do the same on the local machine, I get this:

psql -h <server's address> -U postgres -d "postgres" -p 5432
Password for user postgres: 
psql (9.1.4)
Type "help" for help.

postgres=# 

and

psql -h <server's address> -U "postgres" -d "mxtxdb" -p 5432
Password for user postgres: 
psql: FATAL:  database "mxtxdb" does not exist

and

psql -h <server's address> -U "mxtxdb" -d "mxtxdb" -p 5432
Password for user mxtxdb: 
psql: FATAL:  role "mxtxdb" does not exist

The last two lines of my pg_hba.conf file are:

host    all             mxtxdb          0.0.0.0/0               trust
host    all             postgres        0.0.0.0/0               trust

and the listen_addresses is set to '*' (and uncommented) in the postgresql.conf file.

Any idea why I cannot access other accounts or databases remotely, but I can when ssh'd into the remote machine?

share|improve this question
+1 Complete, good detailed question. Thankyou. No immediate ideas, though. – Craig Ringer Jul 30 at 0:10
Fairly wild idea, but: Are there any rules in the iptables nat table that might be redirecting traffic? iptables -t nat -L -n . – Craig Ringer Jul 30 at 1:00
Also, what happens if you ssh to the problem server and connect locally using psql, but instead of using localhost use the public IP <server's address> ? Additionally: try tcptraceroute <server's address> from the client you're having issues with. – Craig Ringer Jul 30 at 1:01
First of all, thanks for the help! Here's what happens when I run iptables -t nat -L -n: Chain PREROUTING (policy ACCEPT) target prot opt source destination Chain INPUT (policy ACCEPT) target prot opt source destination Chain OUTPUT (policy ACCEPT) target prot opt source destination Chain POSTROUTING (policy ACCEPT) target prot opt source destination My system doesn't have tcptraceroute installed, so I'm looking into that now. – Max Jul 30 at 1:27
tcptraceroute returned in 25 hops with: 25 <server> (<IP address>) [open] 98.399 ms 95.556 ms 98.614 ms – Max Jul 30 at 2:35
show 8 more comments
feedback

1 Answer

up vote 0 down vote accepted

First of all, thanks to Craig Richard for your suggestions, and in fact you were both correct. Here's what happened: I installed PostgreSQL using sudo apt-get install postgresql-9.1, and then I followed the instructions for configuring the server at http://www.postgresql.org/docs/9.1/interactive/runtime.html . Unfortunately, what I did not notice was that the apt-get install process creates a postgresql database cluster by default, so when I followed the instructions to create a new one, I then had two clusters, one at /var/lib/postgresql/9.1/main and one at /usr/local/pgsql/data, and I was interacting with different clusters when I logged in locally vs remotely. When I removed the second cluster, the issue was resolved and I was able to connect remotely.

My takeaways from this process: when installing postgresql with apt-get, and then following the instructions in the postgresql manual, be aware that the install process will create a postgres operating system user by default, and will also create a database cluster by default, so those steps in the manual do not need to be performed. If anyone experiences an error like:

"FATAL: role/database '<name>' does not exist"

when you are sure you've created that user or database, check to ensure that you're operating with the correct database cluster.

Thanks again!

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

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