Database Reference
In-Depth Information
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 would be to allow one user to create privileges on only one schema,
but usage rights on all other schemas. We would set up that arrangement like the following:
REVOKE ALL ON SCHEMA finance FROM public;
GRANT USAGE ON SCHEMA finance TO public;
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;
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, such as:
GRANT SELECT ON month_end_snapshot TO public;
or set default privileges so that they are picked up when objects are created using:
ALTER DEFAULT PRIVILEGES FOR USER fiona IN SCHEMA finance
GRANT SELECT ON TABLES TO PUBLIC;
How it works...
Earlier, I said that schemas work like directories, a little at least.
The PostgreSQL concept of
search_path
is similar to the concept of a PATH environment
variable.
The PostgreSQL concept of the current schema is similar to the concept of the current working
directory. There is no "cd" command to change directory. The current working directory is
changed by altering
search_path
.
A few other differences exist, for example, PostgreSQL schemas are not arranged in a
hierarchy, as are filesystem directories.
Many people create a user of the same name as the schema to make this work in a similar
way to other RDBMS, such as Oracle.
Note that both the finance and sales schemas exist within the same PostgreSQL database,
and run on the same database server. They use a common buffer pool, and there are many
global settings that tie the two schema fairly closely together.
