Re: Connection to second database on server - Mailing list pgsql-general
From | Troy Rasiah |
---|---|
Subject | Re: Connection to second database on server |
Date | |
Msg-id | 48B21C58.3070409@vicnet.net.au Whole thread Raw |
In response to | Re: Connection to second database on server (Hermann Muster <Hermann.Muster@gmx.de>) |
Responses |
Re: Connection to second database on server
|
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
pgsql-general by date: