Database Users and Permissions
Managing database users and their privileges is in concept similar
to managing the users of a Unix operating system, but the details
are not identical.
Database Users
Database users are conceptually completely separate from
operating system users. In practice it might be convenient to
maintain a correspondence, but this is not required. Database user
names are global across a database cluster installation (and not
per individual database). To create a user use the CREATE
USER SQL command:
CREATE USER namename follows the rules for SQL
identifiers: either unadorned without special characters, or
double-quoted. To remove an existing user, use the analogous
DROP USER command.
For convenience, the shell scripts createuser
and dropuser are provided as wrappers around these SQL
commands.
In order to bootstrap the database system, a freshly initialized
system always contains one predefined user. This user will have the
fixed id 1, and by default (unless altered when running
initdb) it will have the same name as
the operating system user that initialized the database
cluster. Customarily, this user will be named
postgres. In order to create more users
you first have to connect as this initial user.
The user name to use for a particular database connection is
indicated by the client that is initiating the connection request
in an application-specific fashion. For example, the
psql program uses the
command line option to indicate the user to connect as. The set of
database users a given client connection may connect as is
determined by the client authentication setup, as explained in
. (Thus, a client is not
necessarily limited to connect as the user with the same name as
its operating system user, in the same way a person is not
constrained in its login name by her real name.)
User attributes
A database user may have a number of attributes that define its
privileges and interact with the client authentication system.
superuser
A database superuser bypasses all permission checks. Also,
only a superuser can create new users. To create a database
superuser, use CREATE USER name
CREATEUSER.
database creation
A user must be explicitly given permission to create databases
(except for superusers, since those bypass all permission
checks). To create such a user, use CREATE USER
name CREATEDB.
password
A password is only significant if the client authentication
method requires the user to supply a password when connecting
to the database. At present, the
A user's attributes can be modified after creation with
ALTER USER.
See the reference pages for CREATE USER and
ALTER USER for details.
Groups
As in Unix, groups are a way of logically grouping users to ease
management of permissions: permissions can be granted to, or revoked
from, a group as a whole. To create a group, use
CREATE GROUP name
To add users to or remove users from a group, use
ALTER GROUP name ADD USER uname1, ...
ALTER GROUP name DROP USER uname1, ...
PrivilegesBeing moved to User's Guide. Will eventually disappear here.
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 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.
Functions and Triggers
Functions and triggers allow users to insert code into the backend
server that other users may execute without knowing it. Hence, both
mechanisms permit users to Trojan horse
others with relative impunity. The only real protection is tight
control over who can define functions (e.g., write to relations
with SQL fields) and triggers. Audit trails and alerters on the
system catalogs pg_class,
pg_shadow and pg_group are also
possible.
Functions written in any language except SQL run inside the backend
server process with the operating systems permissions of the
database server daemon process. It is possible to change the
server's internal data structures from inside of trusted functions.
Hence, among many other things, such functions can circumvent any
system access controls. This is an inherent problem with
user-defined C functions.