Data Control Language

Usually there must be some kind of access control within a database server. You can grant permissions to users with the GRANT ... ON ... TO ... statement, and you can revoke permissions with REVOKE ... ON ... FROM ....

By creating multiple roles (i.e. users of your database), you can model the privileges very accurately. To add a new user to a database server:

      create user smith with password 'alpha123';

(CREATE USER is a shorthand for CREATE ROLE ... WITH LOGIN.)
Let's give the new user some privileges for the existing database samples15:

      grant connect on database samples15 to smith;
      grant select, insert, update on samples15.* to smith;

Any of this can be reverted by using the REVOKE statement. The user smith can now read and change entries in all of samples15's tables, but he cannot delete entries. Additionally, he must be able to connect to the database, or else he will get an "Access denied" error.

You can also restrict the columns that a user can access. For example, the user updatebot should only update the qty column on a wares table:

      grant update (qty) on wares to updatebot;

To allow everyone to access a database called public_data:

      grant connect on public_data to public;

A role can be a member of other roles. That way you can start by creating general privilege levels, and then add actual users to their privilege level. Note that this is handled completely with roles. It could look like this:

      create role readonly;
      grant connect, select on all tables in schema public to readonly;
      create role editor in role readonly;
      grant insert, update, references, trigger on all tables in schema public to editor;
      create role admin with createdb createrole in role editor;
      grant all privileges on all tables in schema * to admin;

      create user jhall with connection limit 1 password 'hello';
      grant editor to jhall;

An editor inherits from a read-only user, and an admin inherits from an editor. Based on this, users can be added to a given role. In the same way, a user can be removed from a role using revoke.
The admin role has all privileges, including the special privileges createdb and createrole.