Re: Connection to second database on server - Mailing list pgsql-general

Hermann Muster wrote:
> Bill Moran wrote:
>> In response to Hermann Muster <Hermann.Muster@gmx.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