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
