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
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
grant update (qty) on wares to updatebot;
To allow everyone to access a database called
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
The admin role has all privileges, including the special privileges