PostgreSQL Grant

Examples

— 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  to u1 user   to access the dept table

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

–connect the  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

— issue the select statement against the  dept table as u1 user

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

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

— again connect the u2 superuser and grant all privilege to u1 user to accessing dept table

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

— Now issue the select statement as u1 user against dept table

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

Some basic command of grant :

— 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;

grant all privileges on database <db_name> to <group_name>

grant all privileges on database <db_name> to <user_name>
grant all privileges on <table_name> to <user_name>

revoke all privileges on database <db_name> from <user_name>
Grant insert privilege to all users on table tab1:
GRANT INSERT ON tab1 TO PUBLIC;

GRANT ALL PRIVILEGES ON emp TO nijam;

GRANT admins TO nijam;

GRANT UPDATE ON emp TO nijam;

GRANT all ON emp TO public;

\dp emp
Access privileges
Schema |  Name   | Type  |   Access privileges   | Column access privileges 
--------+---------+-------+-----------------------+--------------------------
 public | emp     | table | u1=arwdDxt/u1 | col1:
: =r/u1             :       u1_rw=rw/u1
: admin=arw/u1        
(1 row)

GRANT CONNECT ON DATABASE database_name TO user_name;

GRANT CONNECT ON DATABASE database_name TO Public;
GRANT CONNECT ON DATABASE database_name TO user_name;
REVOKE ALL ON ALL TABLES IN SCHEMA schema_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO user_name;

grant all privileges on database dbname to dbuser;
GRANT CONNECT ON DATABASE database_name TO user_name;

CREATE ROLE demo_role WITH LOGIN;
GRANT CONNECT ON DATABASE database_name TO user_name;

ALTER ROLE demo_role WITH NOLOGIN;

GRANT CONNECT ON DATABASE database_name TO user_name;
GRANT UPDATE ON demo TO demo_role;
GRANT INSERT ON demo TO PUBLIC;

\z
                                    Access privileges
 Schema |    Name     |   Type   |     Access privileges      | Column access privileges
--------+-------------+----------+----------------------------+------------------------  
 public | demo        | table    | postgres=arwdDxt/postgres +|
        |             |          | demo_role=w/postgres      +|
        |             |          | test_user=arwdDxt/postgres+|
        |             |          | =a/postgres                |
 public | demo_id_seq | sequence |                            |
(2 rows)