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;
No comments:
Post a Comment