Please help to understand the following. Where the User(who is not the owner
of a table) is able to ALTER DEFAULT PRIVILEGES and GRANT SELECT rights for
all tables???? Is providing USAGE on schema is enough to do that? How is
this secure?
learning=> select current_user;
current_user
--------------
student
(1 row)
learning=> \dn
List of schemas
Name | Owner
-------------+----------
academics | head
board_exams | head
public | postgres
(3 rows)
learning=> set role head;
SET
learning=> CREATE SCHEMA additional;
CREATE SCHEMA
learning=>
learning=> \dn
List of schemas
Name | Owner
-------------+----------
academics | head
* additional | head* Schema's owner is the user head
board_exams | head
public | postgres
(4 rows)
learning=> CREATE TABLE additional.chess(id serial not null, marks varchar);
CREATE TABLE
learning=> GRANT USAGE ON SCHEMA additional TO student;
GRANT
learning=> set role student;
SET
learning=> \z additional.chess
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
------------+-------+-------+-------------------+-------------------+----------
* additional | chess | table | | |* --
USER student has no privilege on the table
(1 row)
learning=> SELECT current_user;
current_user
--------------
student
(1 row)
--with the student user have no privilege how ALTER DEFAULT PRIVILEGES
works????
*learning=> ALTER DEFAULT PRIVILEGES IN SCHEMA additional GRANT INSERT ON
TABLES TO student;
ALTER DEFAULT PRIVILEGES
learning=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
---------+-------------+-------+--------------------
student | academics | table | student=aD/student
student | additional | table | student=a/student
student | board_exams | table | student=r/student
(3 rows)*
learning=> GRANT INSERT ON TABLES TO student;
ERROR: relation "tables" does not exist
learning=> GRANT INSERT ON TABLE additional.chess TO student;
ERROR: permission denied for relation chess
learning=>
-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
