Thread: Connection to second database on server
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. If dblink would allow to omit user and pwd and then use the current connection information to connect to the second database, this would perfectly do the job! Thank you very much for your help. Best regards, H. Muster
Hi, Hermann. The best solution in my opinion would be using users name and password to connect to database. That way you can decide (grant) what tables can be accessed by this user and there is no password you have to hide from him (or others). So use stored procedure returning set of records instead ot view and pass the password as a parameter. I know this is not too good, but its the best I can come up with. On the other hand you can configure postgreSQL to trust connections from localhost. That way you can exclude password from the connect string. Any way - I would not advice you to go that path because of security reasons. Julius Tuskenis Hermann Muster rašė: > 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. > > If dblink would allow to omit user and pwd and then use the current > connection information to connect to the second database, this would > perfectly do the job! > > Thank you very much for your help. > Best regards, > H. Muster >
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. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
On Fri, Jun 20, 2008 at 8:06 AM, Bill Moran <wmoran@collaborativefusion.com> 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. Point of order, he was talking about MSSQL, not MySQL, but your point is not changed by that at all. > 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. Note that this is also the Oracle way of doing things. I much prefer schemas to "cross database" work myself.
Bill Moran wrote:
Regards.
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. :-)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.
Regards.
On Thu, Jul 3, 2008 at 7:29 AM, Hermann Muster <Hermann.Muster@gmx.de> wrote: > > 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. :-) Well, it's a short but sharp learning curve, so you'll run into more, but that's why we, and the archives for the list are here... Most of the problems people run into when converting from another database have to do with unlearning different and / or bad habits they learned there.
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
On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: > > 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 ? Yes, schemas would be much better. The nice thing is with search_path, you could have a setup where application1 and application2 live in different schemas but have access to a common schema. When running app1, you'd do something like: set search_path='app1','commonschema'; and when running app2 you'd change the app1 up there to app2 and then you could access the tables in both schemas without having to use prefixes.
Scott Marlowe wrote: > On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: >> 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 ? > > Yes, schemas would be much better. The nice thing is with > search_path, you could have a setup where application1 and > application2 live in different schemas but have access to a common > schema. When running app1, you'd do something like: > > set search_path='app1','commonschema'; > > and when running app2 you'd change the app1 up there to app2 and then > you could access the tables in both schemas without having to use > prefixes. Thanks Scott. We currently do websites for different customers on the same machine so we have been setting each of them up with individual (database,user,pass). Instead should i be setting them all up in the one database and having individual schema's for each customer and then only granting each user access to their schema & the proposed 'commonschema' ? -- Troy Rasiah
On Sun, Aug 24, 2008 at 10:19 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: > > > Scott Marlowe wrote: >> On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: >>> 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 ? >> >> Yes, schemas would be much better. The nice thing is with >> search_path, you could have a setup where application1 and >> application2 live in different schemas but have access to a common >> schema. When running app1, you'd do something like: >> >> set search_path='app1','commonschema'; >> >> and when running app2 you'd change the app1 up there to app2 and then >> you could access the tables in both schemas without having to use >> prefixes. > > > Thanks Scott. We currently do websites for different customers on the > same machine so we have been setting each of them up with individual > (database,user,pass). > > Instead should i be setting them all up in the one database and having > individual schema's for each customer and then only granting each user > access to their schema & the proposed 'commonschema' ? That's how I'd do it. You probably want to drop the public schema as well. If you need to separate everybody from each other into their own database, then you could always replicate the common schema to each db, but if the common schema is large or changes a lot this could be a pain.
Scott Marlowe wrote: > On Sun, Aug 24, 2008 at 10:19 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: >> >> Scott Marlowe wrote: >>> On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr@vicnet.net.au> wrote: >>>> 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 ? >>> Yes, schemas would be much better. The nice thing is with >>> search_path, you could have a setup where application1 and >>> application2 live in different schemas but have access to a common >>> schema. When running app1, you'd do something like: >>> >>> set search_path='app1','commonschema'; >>> >>> and when running app2 you'd change the app1 up there to app2 and then >>> you could access the tables in both schemas without having to use >>> prefixes. >> >> Thanks Scott. We currently do websites for different customers on the >> same machine so we have been setting each of them up with individual >> (database,user,pass). >> >> Instead should i be setting them all up in the one database and having >> individual schema's for each customer and then only granting each user >> access to their schema & the proposed 'commonschema' ? > > That's how I'd do it. You probably want to drop the public schema as > well. If you need to separate everybody from each other into their > own database, then you could always replicate the common schema to > each db, but if the common schema is large or changes a lot this > could be a pain. Thanks for the info, much appreciated. -- Troy Rasiah