Re: Question regarding authentication/login

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(dot)klaver(at)gmail(dot)com