As in MySQL, granting and revoking privileges in PostgreSQL is done with the grant and revoke statements. The syntax is the same as in MySQL except that PostgreSQL doesn't use the identified by portion of the statement because with PostgreSQL, passwords are assigned when you create the user with the create user statement, as discussed previously. Here is the syntax of the grant statement:
GRANT what_to_grant ON where_to_grant TO user_name;
The following command, for example, grants all privileges to the user foobar on the database sampledata:
GRANT ALL ON sampledata TO foobar;
To revoke privileges, you use the revoke statement. Here is an example:
REVOKE ALL ON sampledata FROM foobar;
This command removes all privileges from the user foobar on the database sampledata.
Advanced administration and user configuration are complex topics. This section cannot begin to cover all the aspects of PostgreSQL administration or of privileges and users. For more information on administering PostgreSQL, see the PostgreSQL documentation or consult a book on PostgreSQL, such as PostgreSQL (Sams Publishing).
Was this article helpful?