Granting Privileges to Roles

A superuser or owner of a schema, table, or other database object can assign privileges to a role, just as they would assign privileges to an individual user by using the GRANT statements described in the SQL Reference Manual. See About Database Privileges for information about which privileges can be granted.

Granting a privilege to a role immediately affects active user sessions. When you grant a new privilege, it becomes immediately available to every user with the role active.

Example

The following example creates two roles and assigns them different privileges on a single table called applog.

  1. Create a table called applog:

    => CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));
    
  2. Create a new role called logreader:

    => CREATE ROLE logreader; 
  3. Assign read-only privileges to the logreader role on table applog:

    => GRANT SELECT ON applog TO logreader;
  4. Create a role called logwriter:

    => CREATE ROLE logwriter;
  5. Assign write privileges to the logwriter role on table applog:

    => GRANT INSERT ON applog TO logwriter;

See the SQL Reference Manualfor the different GRANT statements.