5.42. PostgreSQL PRIVILEGES (permission)

Whenever a database object is created, it is assigned an owner, 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 that user.

In PostgreSQL, there are several types of permissions:

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

Applies the specified permissions to the object depending on the type of object (tables, functions, and so on).

To assign permissions to a user, you can use the GRANT Orders.

5.42.1. GRANT syntax ¶

GRANT The basic syntax of the command is as follows:

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

The privilege − value can be: SELECT,INSERT,UPDATE,DELETE, RULE,ALL.
The name of the object to which object − wants to grant access. Possible objects are: table, view,sequence.
PUBLIC − represents all users.
GROUP group − grants permissions to user groups.
The user name that username − wants to grant permissions to. PUBLIC is a short form that represents all users.

In addition, we can use the REVOKE Command to revoke permissions REVOKE Syntax:

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

5.42.2. Example ¶

To understand permissions, create a user:

runoobdb=# CREATE USER runoob WITH PASSWORD 'password';
CREATE ROLE

information CREATE ROLE Indicates that a user “runoob” has been created.

5.42.3. Example ¶

Create COMPANY 表（下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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 “runoob”:

runoobdb=# GRANT ALL ON COMPANY TO runoob;
GRANT

information GRANT Indicates that all permissions have been assigned to “runoob”.

The following revokes the permissions of the user “runoob”:

runoobdb=# REVOKE ALL ON COMPANY FROM runoob;
REVOKE

information REVOKE Indicates that the user’s permissions have been revoked.

You can also delete users:

runoobdb=# DROP USER runoob;
DROP ROLE

The message DROP ROLE indicates that the user “runoob” has been deleted from the database.