Assigning and revoking a privilege to/from a user or a role

Once a user is created, the next task is to grant privileges or sometimes to revoke granted privileges from the user. PostgreSQL supports several privileges that are similar to other relational databases. The following is a list of available privileges that can be granted to a user or revoked from a user/role:

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE

In this recipe, we shall see how we can assign and revoke a privilege to/from a user.

Getting ready

To grant or revoke privileges from a user, GRANT and REVOKE commands are used. It will be wise to use a database user that has a superuser role or sometimes the owner of the schema and objects to perform GRANT or REVOKE.

How to do it

The following steps need to be followed to understand the recipe:

1. To grant a select privilege on a table, employee, to a user, percuser, the following GRANT command could be used:

GRANT SELECT ON employee TO percuser;

2. Now we revoke SELECT from the user:

REVOKE SELECT ON employee FROM percuser;

3. GRANT all the privileges possible on the employee table to percuser:

GRANT ALL ON employee TO percuser;

4. REVOKE all the privileges on employee from percuser:

REVOKE ALL ON employee FROM percuser;

5. GRANT all privileges on a database to a user:

GRANT ALL ON DATABASE percona TO percuser ;

6. REVOKE all privileges from a user:

REVOKE ALL ON DATABASE percona FROM percuser ;

How it works

In order to assign privileges, a GRANT command must be used. And to revoke the assigned privilege, a REVOKE command must be used. For example, to assign a SELECT privilege on the employee table to a user, percuser, the command seen in step 1 can be used. And to revoke the SELECT privilege on the employee table from percuser, the command seen in step 2 can be used.

When a privilege is granted to a user or a role, it takes effect immediately without the need for SIGHUP or a reload. At the same time, a user can also be granted all the privileges that can be assigned to an object depending on the object type.

The command seen in step 3 can be used to grant all the privileges possible on the employee table to percuser. And to revoke all the privileges, the command seen in step 4 can be used. We could similarly allocate all privileges on a database to a user as seen in step 5. And to revoke the privileges, the command seen in step 6 can be used.