Connection to second database on server

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šė:

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:

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.

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