Schema best practices

From: "J(dot) Carlos Muro" <murojc(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Schema best practices
Date: 2009-05-15 10:44:39
Message-ID: 992cea4f0905150344y65ef0e4cu824cc032c07f7da9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi! When I have more that one project, which is better, in terms of
efficiency/organization?

* Case A: Create one database, and for every project a new schema (in the
oracle style) containing project's objects.

- A.1: a global user (different of 'postgres') as owner of all schemas

create database superdatabase;
create role super_user with login password 'x';
create schema project1 authorization super_user;
create schema project2 authorization super_user;

- A.2: every schema with a different user as owner of the schema

create database superdatabase;
create role user1 with login password 'x';
create schema project1 authorization user1;
create role user2 with login password 'x';
create schema project2 authorization user2;

* Case B: Create a database for every project (in the mysql style), not
using schemas (letting objects lay in 'public').

- B.1: user postgres as owner of the database and add a new user with all
privileges in that db, then use this user to create objects.

create database database1;
create database database2;
create role user1 with login password 'x';
grant all on database database1 to user1;
create role user2 with login password 'x';
grant all on database database1 to user2;

- B.2: a new user as owner of every database

create role user1 with login password 'x';
create database database1 owner user1;
create role user2 with login password 'x';
create database database2 owner user2;

I guess there is not big difference, and maybe this is more a matter of
preference, or.. "taste"? From your experience, please, can you point me
your best practice in this matter?
Thanks for you opinion!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scott Mead 2009-05-15 13:37:55 Re: Schema best practices
Previous Message Scott Marlowe 2009-05-14 22:33:36 Re: Replication for migration