Database Administration - PostgreSQL: Up and Running - page 28

Another common way to organize schemas is by roles. We found this to be particularly
handy with applications that serve multiple clients whose data must be kept separate.
Suppose that you started a business to build and lease a dog-management system to dog
spas. Through creative advertising, you now have a dozen clients, but your database still
has a single table to store all the dogs. Whimsical government regulation passes, and
now you have to put in iron-clad assurances that one spa cannot see dog information
from another. To comply, you set up one schema per spa and create the same dogs table
in each. You then move the dog records into the schema for the spa where those dogs
are pampered. The final touch is to create different login roles for each schema with the
same name as the schema, so that the doggy_day_care schema would be owned by the
doggy_day_care role, hot_dogs schema would be owned by the hot_dogs role, etc. Dogs
are now completely isolated in their respective schemas. When spas log into your da‐
tabase to make edits, they will be able to access only data in their own schemas.
Wait, it gets better. Because we named our roles to match their respective schemas, we're
blessed with another useful technique. But we must first introduce the search_path
database variable.
As we mentioned earlier, object names must be unique within a schema, but you can
have same-named objects in different schemas. For example, you have the same table
called dogs in all 12 schemas. When you execute something like SELECT * FROM dogs ,
how does PostgreSQL know which schema you're referring to? The simple answer is to
always prepend the schema name separated from the table name by a dot, such as in
SELECT * FROM doggy_day_care.dogs . Another method is to set the search_path
variable to be something like public, doggy_day_care, hot_dogs . When the query
executes, the planner searches for the dogs table first in the public schema, then dog
gy_day_care , then hot_dogs .
PostgreSQL has a little-known variable called user that lists the name of the currently
logged-in user. SELECT user returns this name.
Recall how we named our spa schemas to be same as their login roles. We did this so
that we can take advantage of the default search path set in postgresql.conf :
search_path = "$user", public;
Now, if role doggy_day_care logs in, all queries will first look in the doggy_day_care
schema for the tables before moving to public . And most important, the SQL remains
the same for all spas. Even if the spa-management business grows to have thousands or
hundreds of thousands of clients, none of the SQL scripts needs to change. To make
things easier, create a template database with no dogs. Adding a new spa requires just a
few lines to create a schema, database, role, and skeleton tables.
Another practice that we strongly advocate is to create schemas to house extensions
( “Step 2: Installing into a database (version 9.1 and later)” on page 35 ). When you install
an extension, new tables, functions, data types, and plenty of other relics enter your