Skip to main content

Grant and revoke

Qualified users can grant roles and other users various privileges on tables. These privileges are any combination of select, insert, update, references. Alter and index privileges cannot be granted. Only the owner has the right to alter a table definition. Any privilege can be later revoked, which takes any combination of the previously granted privileges.

grant:
    GRANT privileges TO grantees [ WITH GRANT OPTION ]
  | GRANT authid_list TO grantees [ WITH ADMIN OPTION ] [ WITH ADMIN grantor ]

revoke:
    REVOKE [ GRANT OPTION FOR ] privileges FROM grantees [ FROM grantor ]
  | REVOKE [ ADMIN OPTION FOR ] authid ','... FROM grantees [ FROM grantor ]

grantor:
    CURRENT_USER | CURRENT_ROLE

privileges:
    object_privileges ON TABLE [ ident | ident ]

object_privileges:
    ALL [ PRIVILEGES ] | operation ','...

operation:
    INSERT | DELETE | UPDATE [ opt_column_list ] | SELECT [ opt_column_list ]
  | REFERENCES [ opt_column_list ]
  | EXECUTE grantees: [ PUBLIC | authid ] ','...

Examples

It is possible to grant one privilege at a time to a user, per table:

GRANT SELECT ON bookSchema.toOrderTable TO libraryUser;
GRANT INSERT ON bookSchema.tOrderTable TO libraryUser;

However, if you want the user to be able to select, insert, update and delete then it can be done in one go:

GRANT ALL ON bookSchema.orderTable TO libraryWorker;