This is a multi-part message in MIME format.
--------------020407020309020301050305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Bill Moran wrote:
> In response to Hermann Muster <Hermann.Muster@[EMAIL PROTECTED]
>:
>
>
>> 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 do***entation 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.
--------------020407020309020301050305
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Bill Moran wrote:
<blockquote
cite="mid:20080620100625.a2afb3bb.wmoran@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">In response to Hermann Muster <a
class="moz-txt-link-rfc2396E"
href="mailto:Hermann.Muster@[EMAIL PROTECTED]
"><Hermann.Muster@[EMAIL PROTECTED]
>:
</pre>
<blockquote type="cite">
<pre wrap="">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 do***entation 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.
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
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. :-)<br>
Regards.<br>
</body>
</html>
--------------020407020309020301050305--
|
