YouTip

# PostgreSQL PRIVILEGES (Permissions) Whenever a database object is created, an owner is assigned to it. The owner is typically 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.), the specified 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 value can be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL. * object − The name of the object to grant access to. Possible objects are: table, view, sequence. * PUBLIC − Indicates all users. * GROUP group − Grants permissions to a user group. * username − The username to grant permissions to. PUBLIC is a shorthand form representing all users. Additionally, we 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, let's create a user: tutorialdb=# CREATE USER WITH PASSWORD 'password'; CREATE ROLE The message CREATE ROLE indicates that a user "" has been created. ### Example Create the COMPANY table ((https://static.jyshare.com/download/company.sql)), with the following data: tutorialdb# 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 "": tutorialdb=# GRANT ALL ON COMPANY TO ; GRANT The message GRANT indicates that all permissions have been granted to "". Next, revoke the permissions from the user "": tutorialdb=# REVOKE ALL ON COMPANY FROM ; REVOKE The message REVOKE indicates that the user's permissions have been revoked. You can also delete the user: tutorialdb=# DROP USER ; DROP ROLE The message DROP ROLE indicates that the user "" has been deleted from the database.