From: | Troy Rasiah <troyr(at)vicnet(dot)net(dot)au> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | troyrasiah(at)gmail(dot)com |
Subject: | Re: Connection to second database on server |
Date: | 2008-08-25 02:43:36 |
Message-ID: | 48B21C58.3070409@vicnet.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hermann Muster wrote:
> 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.
Sorry for bringing up an old post...If you have a generic set of tables..
eg. table of countries / post codes etc which are used across several
databases what is the best way to access / store them?
I currently
- use dblink to create views when i want to do joins,
OR
- i just open up a separate db handle when i just want to display the
data (via a perl script) from the 'generic database' (eg. a select list
of countries)
but was wondering whether schema's would apply to me as well ?
--
Troy Rasiah
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-08-25 03:59:08 | Re: Connection to second database on server |
Previous Message | Brian Green | 2008-08-24 22:49:52 | Installing Postgress 8.3.3 |