PostgreSQL PRIVILEGES

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, permissions 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.), specific permissions are applied to that object.

To assign permissions 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 values can be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
• object − The name of the object to grant access to. Possible objects include: table, view, sequence.
• PUBLIC − Represents all users.
• GROUP group − Grants permissions to a user group.
• username − The username to grant permissions to. PUBLIC is a shorthand for all users.

Additionally, you can use the REVOKE command to revoke permissions. The REVOKE syntax is:

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

Example

To understand permissions, create a user:

tutorialprodb=# CREATE USER tutorialpro WITH PASSWORD 'password';
CREATE ROLE

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

Example

Create the COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# 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 permissions to the user "tutorialpro":

tutorialprodb=# GRANT ALL ON COMPANY TO tutorialpro;
GRANT

The message GRANT indicates that all permissions have been assigned to "tutorialpro".

Next, revoke the permissions from the user "tutorialpro":

tutorialprodb=# REVOKE ALL ON COMPANY FROM tutorialpro;
REVOKE

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

You can also delete the user:

tutorialprodb=# DROP USER tutorialpro;
DROP ROLE

The message DROP ROLE indicates that the user "tutorialpro" has been removed from the database.