Bill Moran wrote:
> In response to Hermann Muster <Hermann(dot)Muster(at)gmx(dot)de>:
>
>
>> 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.
>>
>
> I feel this paragraph encapsulates your problem. To summarize: you're
> doing it wrong.
>
> Don't take this as an attack, it's not. It's a statement that PostgreSQL
> handles this kind of thing differently than MySQL, and if you try to
> do it the MySQL way, you're going to hit these kinds of problems.
>
> The PostgreSQL way to do it is to create schemas within a single database,
> you can then use roles to set permissions, use search_path to determine
> what users see by default, and schema-qualify when needed.
>
> If you can't migrate your setup to use schemas, then I expect anything
> else you do will feel sub-optimal, as PostgreSQL is designed to use
> schemas for this sort of thing.
>
I just found the time to try that out and it worked! Thank you for your
help. I actually had no idea about using schemas in PostgreSQL. It was
easy to setup and db_link isn't needed anymore. I hope I won't run into
anymore problems. :-)
Regards.