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?
nat
table that might be redirecting traffic?iptables -t nat -L -n
. – Craig Ringer Jul 30 at 1:00localhost
use the public IP<server's address>
? Additionally: trytcptraceroute <server's address>
from the client you're having issues with. – Craig Ringer Jul 30 at 1:01iptables -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:2725 <server> (<IP address>) [open] 98.399 ms 95.556 ms 98.614 ms
– Max Jul 30 at 2:35