When a database object is created, it is assigned an owner. The
owner is the user that executed the creation statement. To change
the owner of a table, index, sequence, or view, use the
ALTER TABLE command. 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,
INSERT, UPDATE, DELETE,
RULE, REFERENCES, TRIGGER,
CREATE, TEMPORARY, EXECUTE,
USAGE, and ALL PRIVILEGES. For more
information on the different types of privileges support by
PostgreSQL, refer to the
GRANT page in the PostgreSQL 7.3 Reference Manual. 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, the privilege to
update the table 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.
