Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL