Granting User Rights

I l @ ve RuBoard

There are four basic rights in the PostgreSQL database system:

Select (read)
Insert (write)
Update/Delete (write)
Rule (write/execute)

By default, the creator of a database is implicitly given all rights to all objects in the database. These privileges are considered immutable for the DBA superuser account.

To assign other users rights on database options, use the GRANT and REVOKE SQL commands, such as:

 GRANT SELECT, UPDATE ON authors TO bill;  REVOKE ALL ON payroll FROM joe;

The PostgreSQL system also has a reserved keyword called PUBLIC that applies to every user in the system (except the DBA). This can make setting blanket rules and permissions much easier.

 GRANT SELECT, UPDATE ON authors TO PUBLIC;  REVOKE UPDATE, DELETE on payroll FROM PUBLIC;

Specifying permissions on a user-by-user basis can be tedious on systems with a sizable number of user accounts. Using GRANT and REVOKE in combination with groups can be an effective method for handling rights management.

Typically, the users should be collected into logical groups that seek to match like users together with respect to their permissions. Rights then can be assigned or revoked for the entire group without having to specify every individual user.

 GRANT SELECT, UPDATE ON authors TO GROUP staff;  REVOKE UPDATE, DELETE on payroll FROM GROUP staff;  GRANT SELECT, UPDATE, DELETE on payroll TO GROUP managers;

I l @ ve RuBoard