Re: Connection to second database on server

Hi, Hermann.

The best solution in my opinion would be using users name and password
to connect to database. That way you can decide (grant) what tables can
be accessed by this user and there is no password you have to hide from
him (or others). So use stored procedure returning set of records
instead ot view and pass the password as a parameter. I know this is not
too good, but its the best I can come up with.

On the other hand you can configure postgreSQL to trust connections from
localhost. That way you can exclude password from the connect string.
Any way - I would not advice you to go that path because of security
reasons.

Julius Tuskenis

Hermann Muster rašė:
> Hello everyone,
>
> I already asked about that a couple of days ago, but didn't get an
> satisfying solution for my problem which is following:
>
> I need to create a view that does a query on a second database on the
> same PostgreSQL server. dblink seems to be the only (???) solution for
> doing so. The problems are: Referring to dblink documentation I'll
> have to hardcode (uaah!!)username and password. 1.) Hence, everyone
> who could see the view definition e.g. in pgAdmin will be able to read
> the username and password (for the second database). 2.) If I have
> multiple postgres users with different rights they will all be treated
> as that one hard-coded user for the second database when querying the
> view.
>
> Someone suggested to set up a pgpass file so the query can get these
> dynamically. However a pgpass file is also not secure as username and
> password are stored in plain text, and problem #2 won't be solved, too.
>
> Does anyone have an idea how to better set up a database view for
> viewing records from another database?
>
> MSSQL for instance allows schema prefixes for using other databases of
> the same server, the current user information is being used to connect
> to this database as well.
>
> If dblink would allow to omit user and pwd and then use the current
> connection information to connect to the second database, this would
> perfectly do the job!
>
> Thank you very much for your help.
> Best regards,
> H. Muster
>