Database Reference
In-Depth Information
How it works...
This sequence of commands first grants full access in schema to a role, gives viewing
(
SELECT)
and modifying (
INSERT
,
UPDATE
,
DELETE
) rights to the role, and then grants
membership in this role to two database users.
There's more...
There is no requirement in PostgreSQL to have some privileges in order to have others. That
is, you may well have "write-only" tables, where you are allowed to insert but you can't select.
This can be used for implementing a mail queue like functionality, where several users post
messages to one user, but can't see what other users have posted.
Alternatively, you can write a record, but you can't change or delete it. This is useful for
auditing log type tables, where all changes are recorded, and with which are not tampered.
Access to schema is also needed
In order to access any table, the user first needs access to the schema containing the table:
GRANT USAGE ON SCHEMA someschema TO someuser;
Granting access to a table through a group role
It is often desirable to give groups of users similar permissions to a group of database objects.
To do this, you first assign all the permissions to a proxy role (also known as a permission
group), and then assign the group to selected users, as follows:
CREATE GROUP webreaders;
GRANT SELECT ON pages TO webreaders;
GRANT INSERT ON viewlog TO webreaders;
GRANT webreaders TO tim, bob;
Now, both
tim
and
bob
have the
SELECT
privilege on table
pages
, and
INSERT
on table
viewlog
. You can also add privileges to the group role after assigning it to users. So after:
GRANT INSERT, UPDATE, DELETE ON comments TO webreaders;
both
bob
and
tim
have all these privileges on table
comments
.
Granting access to all objects in schema
Before Version 9.0 of PostgreSQL, there was no easy way to manipulate privileges to more
than one object at a time, except listing them all in the
GRANT
or
REVOKE
command.
