Database Reference
In-Depth Information
Getting ready
Make sure you've read the recipe
Deciding
on
a
design
for
multi-tenancy
, so that you're
certain this is the route you wish to take. Other options exist, and it is possible that they may
be preferable in some cases.
How to do it...
Schemas can be created easily by using the following:
CREATE SCHEMA finance;
CREATE SCHEMA sales;
We can then create objects directly within those schemas by using "fully qualiied" names,
for example:
CREATE
TABLE
finance.month_end_snapshot
(….)
The default schema into which an object is created is known as the current schema. We can
find out which is our current schema by using the following query:
postgres=# select current_schema;
current_schema
----------------
public
(1 row)
When we access database objects, we use the user-settable parameter
search_path
to
identify which schemas to search. The current schema is the first schema in the
search_
path
—there is no separate parameter for the current schema.
So, if we want to have only a specific user look at certain sets of tables, we can modify
his/her
search_path
. The parameter can be set for each user, so that the value will
be set when he/she connects. The SQL for this would be something like the following:
ALTER ROLE fiona SET search_path = 'finance';
ALTER ROLE sally SET search_path = 'sales';
Note that the "public" schema is not mentioned on the
search_path
, and so would not be
searched. All tables created by
fiona
would go into the finance schema by default, whereas
all tables created by
sally
would go into the sales schema by default.
The users for finance and sales would be able to see that the other schema existed, though
we would be able to grant/revoke privileges such that they could neither create objects nor
read data in the others' schema.
