PostgreSQL PRIVILEGES (Permissions)

Whenever a database object is created, it is assigned an owner, who is usually the person who executed the CREATE statement.

For most types of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, permissions must be set for that user.

In PostgreSQL, privileges are divided into the following types:

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

Depending on the type of object (table, function, etc.), specified privileges are applied to that object.

To assign privileges to a user, you can use the GRANT command.

GRANT Syntax

The basic syntax of the GRANT command is as follows:

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

• privilege − The value can be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
• object − The name of the object to which access is granted. Possible objects include: table, view, sequence.
• PUBLIC − Represents all users.
• GROUP group − Grants privileges to a user group.
• username − The name of the user to whom privileges are granted. PUBLIC is a short form representing all users.

In addition, we can use the REVOKE command to revoke privileges. The REVOKE syntax:

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

Example

To understand privileges, create a user:

exampledb=# CREATE USER example WITH PASSWORD 'password';
CREATE ROLE

The message CREATE ROLE indicates that a user "example" has been created.

Example

Create the COMPANY table (Download the COMPANY SQL file), with the data content as follows:

exampledb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now assign privileges to the user "example":

exampledb=# GRANT ALL ON COMPANY TO example;
GRANT

The message GRANT indicates that all privileges have been assigned to "example".

Next, revoke the privileges of user "example":

exampledb=# REVOKE ALL ON COMPANY FROM example;
REVOKE

The message REVOKE indicates that the user's privileges have been revoked.

You can also delete the user:

exampledb=# DROP USER example;
DROP ROLE

The message DROP ROLE indicates that user "example" has been deleted from the database.