DBs and Schemas

[Roderick A. Anderson]

I'm looking for some input on a configuration I'm implementing. The long
term goal is to providing hosting for companies and organizations with a
basic/generic set of applications that use PostgreSQL.

Most of these applications want to be installed in their own database.
Given I'm looking at 6 or so already this would be 6+ databases per
company. Not my idea of an easy to admin situation.

My plan is to make one database (per company/organization) and modify the
installation scripts and the applications so they end up in and using
different schemas (probably named the same as the database would have been
named.)

I currently see see several issues.

1. Modifying the applications to use schemas instead of connecting to
specific databases. Getting the authors to use schemas so I do it
once.

2. PostgreSQL usernames are global to the instance (?) not specific to the
database which means each username has to/should include a grouping
type of prefix/postfix identifier. I think this is so. Please tell me
I'm wrong!

3. Isolation of users in a database from other users of another database.
(Grants, groups, etc.) Just making sure I do it and not cutting them
off from system stuff they need.

And several more, I'm sure.

Most of the reading I've done so far hasn't got into this very deep. (What
did I miss in this arena?) I have done this type of setup before many
years (10+) ago using Oracle but think there are significant differences
in their way and the PostgreSQL way.
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Adam Ruth]

> 1. Modifying the applications to use schemas instead of connecting to

specific databases. Getting the authors to use schemas so I do it
once.
One thing I'd suggest would be to modify the application to issue a
"set search_path = yourschema;" at the beginning, then the rest of the
application wouldn't need to change. That's what I did when I did
something similar.

Adam Ruth

On Jan 6, 2004, at 12:39 PM, Roderick A. Anderson wrote:
I'm looking for some input on a configuration I'm implementing. The
long
term goal is to providing hosting for companies and organizations with
a
basic/generic set of applications that use PostgreSQL.

Most of these applications want to be installed in their own database.
Given I'm looking at 6 or so already this would be 6+ databases per
company. Not my idea of an easy to admin situation.

My plan is to make one database (per company/organization) and modify
the
installation scripts and the applications so they end up in and using
different schemas (probably named the same as the database would have
been
named.)

I currently see see several issues.

1. Modifying the applications to use schemas instead of connecting to
specific databases. Getting the authors to use schemas so I do it
once.

2. PostgreSQL usernames are global to the instance (?) not specific to
the
database which means each username has to/should include a grouping
type of prefix/postfix identifier. I think this is so. Please
tell me
I'm wrong!

3. Isolation of users in a database from other users of another
database.
(Grants, groups, etc.) Just making sure I do it and not cutting
them
off from system stuff they need.

And several more, I'm sure.

Most of the reading I've done so far hasn't got into this very deep.
(What
did I miss in this arena?) I have done this type of setup before many
years (10+) ago using Oracle but think there are significant
differences
in their way and the PostgreSQL way.
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to
ma*******@postg resql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Oliver Elphick]

On Tue, 2004-01-06 at 22:06, Adam Ruth wrote:

1. Modifying the applications to use schemas instead of connecting to
specific databases. Getting the authors to use schemas so I do it
once.

One thing I'd suggest would be to modify the application to issue a
"set search_path = yourschema;" at the beginning, then the rest of the
application wouldn't need to change. That's what I did when I did
something similar.

You can use ALTER DATABASE to set that up permanently, without touching
the application.

--
Oliver Elphick Ol************@ lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
=============== =============== ==========
"Thou shalt not avenge, nor bear any grudge against the
children of thy people, but thou shalt love thy
neighbour as thyself. I am the LORD."
Leviticus 19:18
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Roderick A. Anderson]

On Tue, 6 Jan 2004, Adam Ruth wrote:

One thing I'd suggest would be to modify the application to issue a
"set search_path = yourschema;" at the beginning, then the rest of the
application wouldn't need to change. That's what I did when I did
something similar.

Neat idea. I was thinking that most of the applications use a specific id
to access their database so I was going to do this at the user level
(alter user). I think it will depend on the specific application as to
how I'd do this.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Roderick A. Anderson]

On Tue, 6 Jan 2004, Oliver Elphick wrote:

You can use ALTER DATABASE to set that up permanently, without touching
the application.

I'd thought of this but since there will be several applications installed
and some, I am sure, will have same-named tables this could back-fire.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Martijn van Oosterhout]

On Tue, Jan 06, 2004 at 03:34:21PM -0800, Roderick A. Anderson wrote:

On Tue, 6 Jan 2004, Oliver Elphick wrote:

You can use ALTER DATABASE to set that up permanently, without touching
the application.
I'd thought of this but since there will be several applications installed
and some, I am sure, will have same-named tables this could back-fire.

Are you actually going to be doing joins between these applications? If not,
why not setup multiple databases, then you can be sure they won't conflict.
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/+0h2Y5Twig3Ge+Y RAgp2AJ9+Q/gyWsYXUvVaGk9YB 3dO6ccQxQCdErYb
PJiqSulNY6zprf+ nnMx3Fzc=
=EIyG
-----END PGP SIGNATURE-----

[Adam Ruth]

On Jan 6, 2004, at 3:27 PM, Oliver Elphick wrote:

On Tue, 2004-01-06 at 22:06, Adam Ruth wrote:

1. Modifying the applications to use schemas instead of connecting to
specific databases. Getting the authors to use schemas so I do it
once.

One thing I'd suggest would be to modify the application to issue a
"set search_path = yourschema;" at the beginning, then the rest of the
application wouldn't need to change. That's what I did when I did
something similar.

You can use ALTER DATABASE to set that up permanently, without touching
the application.

If you were combining several databases into one database with several
schemas, each application will use a different schema. Thus each
application would need to specify which schema it's using when starting
up.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Roderick A. Anderson]

On Wed, 7 Jan 2004, Martijn van Oosterhout wrote:

Are you actually going to be doing joins between these applications?
If not, why not setup multiple databases, then you can be sure they
won't conflict.

Well in at least one situation I can think of there will be joins. And
with a minimum of six applications for a company/organization that adds up
to quite a bit of admin work when there are several company/organizations.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org