Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

Started by rajanalmost 7 years ago6 messages
#1rajan
vgmonnet@gmail.com

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

#2Andrew Gierth
andrew@tao11.riddles.org.uk
In reply to: rajan (#1)
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

"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)

#3rajan
vgmonnet@gmail.com
In reply to: Andrew Gierth (#2)
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

THanks for the response, Andrew.

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

#4rajan
vgmonnet@gmail.com
In reply to: rajan (#3)
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

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

#5Andrew Gierth
andrew@tao11.riddles.org.uk
In reply to: rajan (#4)
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

"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)

#6rajan
vgmonnet@gmail.com
In reply to: Andrew Gierth (#5)
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner

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