Обсуждение: Question regarding authentication/login
Hello, I have installed postgres version 8.4.9 from the debian repository.
I set up a username and password, and was able to create my tables and add information to the database from a java application running through a remote SSH tunnel.
Then I moved this same command line program onto the same server as the database resides : when I create tables from this location I can only access them from this local machine: I can use psql -U user dbname (same login and pass as the remote connection) and i can select * from users; and it shows all the entries just fine.
But if I try to connect using the same login and password through a remote SSH tunnel, I can not see any of the tables created from the CLI on the server... If I create the tables from the remote location I can query them fine.
The exact error message is : ERROR: relation "users" does not exist (Either from pgAdmin GUI, or from the command line interface that comes with pgAdmin )
Am I misunderstanding something fundamental about user authentication? How does postgres distinguish localhost connections from SSH tunneled connections? Is it possible that somehow connecting form a local linux-user account is creating hidden tables within my otherwise remotely accessable database?
Thank you
On Thursday, December 08, 2011 1:40:08 pm Chris Deadlock wrote: > Hello, I have installed postgres version 8.4.9 from the debian repository. > > I set up a username and password, and was able to create my tables and add > information to the database from a java application running through a > remote SSH tunnel. > > Then I moved this same command line program onto the same server as the > database resides : when I create tables from this location I can only > access them from this local machine: I can use psql -U user dbname (same > login and pass as the remote connection) and i can select * from > users; and it shows all the entries just fine. > > But if I try to connect using the same login and password through a remote > SSH tunnel, I can not see any of the tables created from the CLI on the > server... If I create the tables from the remote location I can query them > fine. > > The exact error message is : ERROR: relation "users" does not exist > (Either from pgAdmin GUI, or from the command line interface that comes > with pgAdmin ) > > Am I misunderstanding something fundamental about user authentication? How > does postgres distinguish localhost connections from SSH tunneled > connections? See here: http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html > Is it possible that somehow connecting form a local linux-user > account is creating hidden tables within my otherwise remotely accessable > database? First question, are you sure you are connecting to same database in both the remote and local case? If the answer to above is yes, then it sounds like a search_path issue. To confirm, when trying to select from users in the remote case use the full schema qualified name for table. i.e some_schema.users. > > Thank you -- Adrian Klaver adrian.klaver@gmail.com
On 12/09/2011 10:35 PM, Adrian Klaver wrote: > First question, are you sure you are connecting to same database in > both the remote and local case? It strikes me that this is another use case for being able to get the system identifier from SQL :-) -- Craig Ringer
Like most problems, this was stupidity on my part!
I had an old test copy of postgres running on the same port of this windows laptop, so of course I was connecting to that instead of the SSH tunnel that was set up by Putty.
Problem solved, thanks for the help
On Thu, Dec 8, 2011 at 3:40 PM, Chris Deadlock <cdeadlock@vendtxt.com> wrote:
Hello, I have installed postgres version 8.4.9 from the debian repository.I set up a username and password, and was able to create my tables and add information to the database from a java application running through a remote SSH tunnel.Then I moved this same command line program onto the same server as the database resides : when I create tables from this location I can only access them from this local machine: I can use psql -U user dbname (same login and pass as the remote connection) and i can select * from users; and it shows all the entries just fine.But if I try to connect using the same login and password through a remote SSH tunnel, I can not see any of the tables created from the CLI on the server... If I create the tables from the remote location I can query them fine.The exact error message is : ERROR: relation "users" does not exist (Either from pgAdmin GUI, or from the command line interface that comes with pgAdmin )Am I misunderstanding something fundamental about user authentication? How does postgres distinguish localhost connections from SSH tunneled connections? Is it possible that somehow connecting form a local linux-user account is creating hidden tables within my otherwise remotely accessable database?Thank you