7.3. Privileges

When a database object is created, it is assigned an owner. The owner is the user that executed the creation statement. There is currently no polished interface for changing the owner of a database object. By default, only an owner (or a superuser) can do anything with the object. In order to allow other users to use it, privileges must be granted.

There are several different privileges: SELECT (read), INSERT (append), UPDATE (write), DELETE, RULE, REFERENCES (foreign key), and TRIGGER. (See the GRANT manual page for more detailed information.) The right to modify or destroy an object is always the privilege of the owner only. To assign privileges, the GRANT command is used. So, if joe is an existing user, and accounts is an existing table, write access can be granted with

GRANT UPDATE ON accounts TO joe;

The user executing this command must be the owner of the table. To grant a privilege to a group, use

GRANT SELECT ON accounts TO GROUP staff;

The special "user" name PUBLIC can be used to grant a privilege to every user on the system. Writing ALL in place of a specific privilege specifies that all privileges will be granted.

To revoke a privilege, use the fittingly named REVOKE command:

REVOKE ALL ON accounts FROM PUBLIC;

The special privileges of the table owner (i.e., the right to do DROP, GRANT, REVOKE, etc) are always implicit in being the owner, and cannot be granted or revoked. But the table owner can choose to revoke his own ordinary privileges, for example to make a table read-only for himself as well as others.