Thread: Able to do ALTER DEFAULT PRIVILEGES from a user who is not theowner

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

>>>>> "rajan" == rajan  <vgmonnet@gmail.com> writes:

 rajan> --with the student user have no privilege how ALTER DEFAULT PRIVILEGES
 rajan> works????
 rajan> *learning=> ALTER DEFAULT PRIVILEGES IN SCHEMA additional GRANT INSERT ON
 rajan> TABLES TO student;

This ALTER only affects the default privileges for tables created by
the role "student" (because they're the ones executing the ALTER), it
does not affect default privileges for tables created by anybody else.

-- 
Andrew (irc:RhodiumToad)

THanks for the response, Andrew.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Andrew,

Another question, If the user student is not the owner of the
Schema(additional) and has only USAGE / no privileges, How come it is able
to modify permissions at schema level?



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

>>>>> "rajan" == rajan  <vgmonnet@gmail.com> writes:

 rajan> Andrew,

 rajan> Another question, If the user student is not the owner of the
 rajan> Schema(additional) and has only USAGE / no privileges, How come
 rajan> it is able to modify permissions at schema level?

Because it's not modifying anything that affects any other user. If
"student" can't create objects in schema "additional", then the default
has no effect (since it applies only to objects created by "student");
if those permissions are later granted, then the previously set default
still applies.

-- 
Andrew (irc:RhodiumToad)

Thanks Andrew for the reply.

Based on the answer, Is there a way to provide read access on all tables(
*created by any user* ) to a Read Only user?



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html