PostgreSQL PRIVILEGES (Privileges)

Whenever a database object is created, an owner is assigned to it, and the owner is usually the person who executes the create statement.

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

In PostgreSQL, permissions are divided into the following types:

• SELECT

• INSERT

• UPDATE

• DELETE

• TRUNCATE

• REFERENCES

• TRIGGER

• CREATE

• CONNECT

• TEMPORARY

• EXECUTE

• USAGE

According to the type of the object (table, function, etc.), apply the specified permissions to the 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 value can be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

• object − The name of the object to grant access permissions. Possible objects include: table, view, sequence.

• PUBLIC − Represents all users.

• GROUP group − Grant permissions to user groups.

• username − The username to be granted permissions. PUBLIC is a short form representing all users.

Additionally, we can use the REVOKE command to revoke permissions, REVOKE syntax：

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

Online Example

To understand permissions, create a user:

w3codeboxdb=# CREATE USER w3codebox WITH PASSWORD 'password';
CREATE ROLE

Information CREATE ROLE indicates that a user "w3codebox".

Online Example

Create COMPANY table (Download COMPANY SQL file ），Data content as follows：

w3codeboxdb# 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 grant permissions to the user "w3Permissions assigned to "codebox":

w3codeboxdb=# GRANT ALL ON COMPANY TO w3codebox;
GRANT

Information GRANT indicates that all permissions have been assigned to "w3codebox".

Below are the permissions revoked for the user "w3Permissions of "codebox":

w3codeboxdb=# REVOKE ALL ON COMPANY FROM w3codebox;
REVOKE

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

You can also delete the user:

w3codeboxdb=# DROP USER w3codebox;
DROP ROLE

Information DROP ROLE indicates that the user "w3codebox" has been deleted from the database.