How to do it…

1.  Schemas can be easily created using the following commands:

CREATE SCHEMA finance; 
CREATE SCHEMA sales;

2. We can then create objects directly within those schemas using fully qualified names, like this:

CREATE TABLE finance.month_end_snapshot (.....)

The default schema in which an object is created is known as the current_schema. We can find out which is our current schema using the following query:

postgres=# select current_schema;

This returns an output like the following:

current_schema
----------------
 public
(1 row)

3. When we access database objects, we use the user-settable search_path parameter to identify the schemas to search for. The current_schema is the first schema in the search_path parameter. There is no separate parameter for the current_schema.

So, if we want to let only a specific user look at certain sets of tables, we can modify their search_path parameter. This parameter can be set for each user so that the value will be set when they connect. The SQL queries for this would be something like the following:

ALTER ROLE fiona SET search_path = 'finance';
ALTER ROLE sally SET search_path = 'sales';

The public schema is not mentioned on search_path, so it will not be searched. All tables created by fiona will go into the finance schema by default, whereas all tables created by sally will go into the sales schema by default.

4. The users for finance and sales will be able to see that the other schema exists and change search_path to use it, but we will be able to GRANT or REVOKE privileges so that they can neither create objects nor read data in other people's schemas:

REVOKE ALL ON SCHEMA finance FROM public;
GRANT ALL ON SCHEMA finance TO fiona;
REVOKE ALL ON SCHEMA sales FROM public;
GRANT ALL ON SCHEMA sales TO sally;

An alternate technique is to allow one user to create privileges on only one schema, but grant usage rights on all other schemas. We can set up that arrangement like this:

REVOKE ALL ON SCHEMA finance FROM public;
GRANT USAGE ON SCHEMA finance TO fiona;
GRANT CREATE ON SCHEMA finance TO fiona;
REVOKE ALL ON SCHEMA sales FROM public;
GRANT USAGE ON SCHEMA sales TO sally;
GRANT CREATE ON SCHEMA sales TO sally;
GRANT USAGE ON SCHEMA sales TO fiona;
GRANT USAGE ON SCHEMA finance TO sally

5. Note that you need to grant the privileges for usage on the schema, as well as specific rights on the objects in the schema. So, you will also need to issue specific grants for objects, as shown here:

GRANT SELECT ON month_end_snapshot TO public;

You can also set default privileges so that they are picked up when objects are created, using the following command:

ALTER DEFAULT PRIVILEGES FOR USER fiona IN SCHEMA finance
GRANT SELECT ON TABLES TO PUBLIC;