When you create a database object, you become its owner. By default, only the owner of an object can do anything with the object. In order to allow other users to use it, privileges must be granted. (However, users that have the superuser attribute can always access any object.)
There are several different privileges: SELECT
,
INSERT
, UPDATE
, DELETE
,
REFERENCES
, TRIGGER
,
CREATE
, CONNECT
, TEMPORARY
,
EXECUTE
, and USAGE
.
The privileges applicable to a particular
object vary depending on the object's type (table, function, etc).
For complete information on the different types of privileges
supported by PostgreSQL, refer to the
GRANT reference
page. The following sections and chapters will also show you how
those privileges are used.
The right to modify or destroy an object is always the privilege of the owner only.
To change the owner of a table, index, sequence, or view, use the
ALTER TABLE
command. There are corresponding ALTER
commands for
other object types.
To assign privileges, the GRANT
command is
used. For example, 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;
Writing ALL
in place of a specific privilege grants all
privileges that are relevant for the object type.
The special “user” name PUBLIC
can
be used to grant a privilege to every user on the system. Also,
“group” roles can be set up to help manage privileges when
there are many users of a database — for details see
Chapter 18, Database Roles and Privileges.
To revoke a privilege, use the fittingly named
REVOKE
command:
REVOKE ALL ON accounts FROM PUBLIC;
The special privileges of the object 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 object owner can choose
to revoke his own ordinary privileges, for example to make a
table read-only for himself as well as others.
Ordinarily, only the object's owner (or a superuser) can grant or revoke privileges on an object. However, it is possible to grant a privilege “with grant option”, which gives the recipient the right to grant it in turn to others. If the grant option is subsequently revoked then all who received the privilege from that recipient (directly or through a chain of grants) will lose the privilege. For details see the GRANT and REVOKE reference pages.