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!