PostgreSQL PRIVILEGES

An owner is assigned, whenever an object is created and owner is the one who executed the creation statement.In starting owner is the one who can create or delete the object.

Different kinds of privileges in PostgreSQL are −
1. SELECT,
2. INSERT,
3. UPDATE,
4. DELETE,
5. TRUNCATE,
6. REFERENCES,
7. TRIGGER,
8. CREATE,
9. CONNECT,
10. TEMPORARY,
11. EXECUTE, and
12. USAGE



Syntax for GRANT :

Basic syntax for GRANT command is as follows −

\\Syntax for GRANT
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
--------------------------------------
\\Syntax for REVOKE
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

privilege:-SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object:-The name of an object to which to grant access.
PUBLIC:-A short form representing all users.
GROUP:-A group to whom to grant privileges.
username :-The username to whom to grant privileges.


Example :

let us first create a USER as follows −

CREATE USER rahul WITH PASSWORD 'password';
CREATE ROLE
*The message CREATE ROLE indicates that the USER "rahul" is created.*
----------------------------------------------------------------------
lfcdb=# select * from COMPANY;
id | name     | age | address    | salary
----+-------+-----+------------+--------
1 | Prateek  |  22 | Mysore     |  20000
2 | Vaibhav  |  20 | Udaipur    |  15528
3 | Yash     |  24 | Mumbai     |  22000
4 | Jugal    |  23 | Delhi      |  25000
5 | Prasun   |  19 | Kolkata    |  30000
-----------------------------------------------------------------------
\\let us grant all privileges on a table
\\GRANT indicates that all privileges are assigned to the USER
lfcdb=# GRANT ALL ON COMPANY TO rahul;
GRANT 
-----------------------------------------------------------------------
\\let us revoke the privileges from the USER on a table
\\REVOKE indicates that all privileges are revoked from the USER.
lfcdb=# REVOKE ALL ON COMPANY FROM rahul;
REVOKE
-----------------------------------------------------------------------
\\You can even delete the user as follows −
lfcdb=# DROP USER rahul;
DROP ROLE





Visit :


Discussion


* You must be logged in to add comment.