PostgreSQL Grant

The GRANT command has two basic variants: 

1.grants privileges on a database object (table, column, view, sequence, database, foreign-data wrapper, foreign server, function, procedural language, schema, or tablespace), 

2.grants membership in a role

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views).

The key word PUBLIC indicates that the privileges are to be granted to all roles

If WITH GRANT OPTION is specified, the recipient of the privilege can in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options cannot be granted to PUBLIC.

PRIVILEGES	Description
SELECT	Allows SELECT from any column, or the specific columns listed, of the specified table, this privilege allows the object to be read.
INSERT	Allows INSERT of a new row into the specified table.
UPDATE	Allows UPDATE of any column, or the specific columns listed, of the specified table.
DELETE	Allows DELETE of a row from the specified table.
TRUNCATE	Allows TRUNCATE on the specified table.
REFERENCES	To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns. The privilege may be granted for all columns of a table, or just specific columns.
TRIGGER	Allows the creation of a trigger on the specified table.
CREATE	For databases, allows new schemas to be created within the database. For schemas, allows new objects to be created within the schema.  For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace,
CONNECT	Allows the user to connect to the specified database.
TEMPORARY TEMP	Allows temporary tables to be created while using the specified database.
EXECUTE	Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)
USAGE	For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages. For schemas, allows access to objects contained in the specified schema  For sequences, this privilege allows the use of the currval and nextval functions. For foreign-data wrappers, this privilege enables the grantee to create new servers using that foreign-data wrapper. For servers, this privilege enables the grantee to create, alter, and drop his own user's user mappings associated with that server. Also, it enables the grantee to query the options of the server and associated user mappings.
ALL PRIVILEGES	Grant all of the available privileges at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

PRACTICAL FOR GRANT:

--connect the u2 user in postgres database

postgres=# \conninfo
You are connected to database "postgres" as user "u2" via socket in "/tmp" at port "5432".

--List down the user

postgres=# \du    
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u10       |                                                | {}
 u11       |                                                | {}
 u2        | Superuser                                      | {}

--list the table

postgres=# \dt    
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 benz2  | buy         | table | u2
 benz2  | comment_log | table | u2
 benz2  | contable    | table | u2
 benz2  | demo        | table | u2
 benz2  | dept        | table | postgres

--connect  as u1 user

[postgres@r1 ~]$ psql -U u1 
Password for user u1: 

--Check the privileges of dept tables as u1 user

postgres=> \dp benz2.dept
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges 
--------+------+-------+-------------------+--------------------------
 benz2  | dept | table |                   | 
(1 row)

Note:Here u1 user don't have any privilege to access dept table

--connect the superuser as u2 grant the read & insert permission on dept table  to u1 user

postgres=# grant select on benz2.dept to u1;
GRANT
postgres=# grant insert on benz2.dept to u1;
GRANT

--connect as u1 user and check the privilege using \dp command

postgres=> \dp benz2.dept                                                       
                              Access privileges
 Schema | Name | Type  |     Access privileges     | Column access privileges 
--------+------+-------+---------------------------+--------------------------
 benz2  | dept | table | postgres=arwdDxt/postgres+| 
        |      |       | u1=ar/postgres            | 
(1 row)

Here "ar" means insert & read(select)

The entries shown by \dp are interpreted thus:

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

--select the dept table as u1 user

postgres=> select * from benz2.dept;
ERROR:  permission denied for schema benz2
LINE 1: select * from benz2.dept;

becouse you need to specify grant usuage option on benz2 schema                     ^

--connect as u2 superuser and grant all privilege to u1 user

postgres=# grant USAGE ON schema benz2 TO u1;
GRANT

--Now you can select dept table as u1 user

postgres=> select * from benz2.dept; 
 dept_id | department | address 
---------+------------+---------
       1 | ece        | chennai
(1 row)

--Create a "nologin" role to act as the owner

 create role dbowner nologin;

--Change the owner of your database to this

 alter database mydb owner dbowner;

--Grant all your logins to this new role

grant dbowner to user1, user2;