Re: Single postgres for Multiple application

Twaha Daudi wrote on 10.03.2012 10:32:
> Hello all, Good day. I have installed postgres 8.4 on ubuntu 11.10
> desktop.My interest is to test three web-based application.One is
> drupal7.the other two still working on it.There should be possibility
> of data transaction between them.The two web based will work with
> tomcat instances(port:8080) while drupal7 normal Apache 2.0. Is it
> possible? How can configure to achieve that?.any idea or tutorial
> link appreciated.
>
> or the most important are those two application which use tomcat.
> thank you huu
>
As you say "there should be possibility of data transaction between them", you can't use databases to separate the individual application.

You will need to setup schemas and corresponding users to achieve this:

First you need to create three different users.
Then for each user create one schema and make the corresponding user the owner of the schema.

Set the user's search path such that their "own" schema is the only one in the list (or at least the first one).
That way the application users don't have to prefix all tables.

Something like this:

create user u1 password 'welcome';
create user u2 password 'welcome';
create user u3 password 'welcome';

create schema s1 authorization u1;
create schema s2 authorization u2;
create schema s3 authorization u3;

alter user u1 set search_path to s1;
alter user u2 set search_path to s2;
alter user u3 set search_path to s3;

commit;

For those tables that need to be used by more than one user (i.e. application) grant the necessary privileges to the other users.

Regards
Thomas