Обсуждение: BUG #14131: BUG, schema owner can drop otheruser's object in it's schema

The following bug has been logged on the website:

Bug reference:      14131
Logged by:          digoal
Email address:      digoal@126.com
PostgreSQL version: 9.5.2
Operating system:   CentOS 6.x x64
Description:

According to the SQL standard, the owner of a schema always owns all objects
within it. PostgreSQL allows schemas to contain objects owned by users other
than the schema owner. This can happen only if the schema owner grants the
CREATE privilege on his schema to someone else, or a superuser chooses to
create objects in it.

postgres=# create role r1 login;
CREATE ROLE
postgres=# create role r2 login;
CREATE ROLE
postgres=# grant all on database postgres to r1;
GRANT
postgres=# grant all on database postgres to r2;
GRANT
postgres=# \c postgres r1;
postgres=> create schema r1;
CREATE SCHEMA
postgres=> grant all on schema r1 to r2;
GRANT
postgres=> \c postgres r2;
postgres=> create table r1.t(id int);
CREATE TABLE
postgres=> \c postgres postgres
postgres=# create table r1.t1(id int);
CREATE TABLE
postgres=# \c postgres r1
postgres=> drop table r1.t;
DROP TABLE
postgres=> drop table r1.t1;
DROP TABLE

schema r1's owenr is r1.
i create two table in schema r1, these table's owner is r2 and postgres. but
schema r1's owner r1 can drop r2 and postgres's table in schema r1.

it's so bad for dba?

digoal@126.com writes:
> schema r1's owenr is r1.
> i create two table in schema r1, these table's owner is r2 and postgres. but
> schema r1's owner r1 can drop r2 and postgres's table in schema r1.

This is  the intended and documented behavior.  It's no different than
if a database owner chooses to drop a database.

            regards, tom lane

On Tue, May 10, 2016 at 6:12 AM, <digoal@126.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14131
> Logged by:          digoal
> Email address:      digoal@126.com
> PostgreSQL version: 9.5.2
> Operating system:   CentOS 6.x x64
> Description:
>
> According to the SQL standard, the owner of a schema always owns all
> objects
> within it. PostgreSQL allows schemas to contain objects owned by users
> other
> than the schema owner. This can happen only if the schema owner grants th=
e
> CREATE privilege on his schema to someone else, or a superuser chooses to
> create objects in it.
>

Yeah, PostgreSQL provides for more flexability - standard conformance is up
to the DBA to enforce.  Its not a bug to deviate from the standard - and
while there is some downside to be more flexible this particular dynamic
doesn't even seem that egregious compared to other areas where we deviate.

=E2=80=8BDavid J.=E2=80=8B

<div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial"><div style="position:relative;zoom:1">Yes,
databaseowner can drop database direct, but it can not drop object in database direct. </div><div
style="position:relative;zoom:1">butschema owner can drop object in schema direct. </div><div
style="position:relative;zoom:1">Ithink this is not good for a Enterprise Database system.</div><div
style="position:relative;zoom:1"><br/></div><div style="position:relative;zoom:1"><div
style="position:relative;zoom:1">postgres=>\c postgres r1</div><div style="position:relative;zoom:1">You are now
connectedto database "postgres" as user "r1".</div><div style="position:relative;zoom:1">postgres=> create database
db1;</div><divstyle="position:relative;zoom:1">CREATE DATABASE</div><div style="position:relative;zoom:1">postgres=>
grantall on database db1 to r2;</div><div style="position:relative;zoom:1">GRANT</div><div
style="position:relative;zoom:1">postgres=>\c db1 r2</div><div style="position:relative;zoom:1">You are now
connectedto database "db1" as user "r2".</div><div style="position:relative;zoom:1">db1=> create schema
r2;</div><divstyle="position:relative;zoom:1">CREATE SCHEMA</div><div style="position:relative;zoom:1">db1=> create
tabler2.t(id int);</div><div style="position:relative;zoom:1">CREATE TABLE</div><div
style="position:relative;zoom:1">db1=>insert into t select generate_series(1,100);</div><div
style="position:relative;zoom:1">INSERT0 100</div><div style="position:relative;zoom:1">db1=> \c db1
postgres</div><divstyle="position:relative;zoom:1">You are now connected to database "db1" as user
"postgres".</div><divstyle="position:relative;zoom:1">db1=# create table t(id int);</div><div
style="position:relative;zoom:1">CREATETABLE</div><div style="position:relative;zoom:1">db1=# insert into t select
generate_series(1,100);</div><divstyle="position:relative;zoom:1">INSERT 0 100</div><div
style="position:relative;zoom:1"><divstyle="position:relative;zoom:1">postgres=> \c db1 r1</div><div
style="position:relative;zoom:1">Youare now connected to database "db1" as user "r1".</div><div
style="position:relative;zoom:1">db1=>drop table r2.t ;</div><div style="position:relative;zoom:1">ERROR:
 permissiondenied for schema r2</div><div style="position:relative;zoom:1">db1=> drop table public.t ;</div><div
style="position:relative;zoom:1">ERROR: must be owner of relation t</div><div style="position:relative;zoom:1">db1=>
dropschema r2;</div><div style="position:relative;zoom:1">ERROR:  must be owner of schema r2</div><div
style="position:relative;zoom:1">db1=>drop schema public;</div><div style="position:relative;zoom:1">ERROR:  must be
ownerof schema public</div><div style="position:relative;zoom:1">db1=> \c postgres r1</div><div
style="position:relative;zoom:1">Youare now connected to database "postgres" as user "r1".</div><div
style="position:relative;zoom:1">postgres=>drop database r1;</div><div style="position:relative;zoom:1">ERROR:
 database"r1" does not exist</div><div style="position:relative;zoom:1">postgres=> drop database db1;</div><div
style="position:relative;zoom:1">DROPDATABASE</div></div></div><div style="position:relative;zoom:1"><br /></div><div
style="position:relative;zoom:1">regards,digoal<div style="clear:both"></div></div><div
id="divNeteaseMailCard"></div><br/><pre><br />At 2016-05-10 21:59:13, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: 
>digoal@126.com writes:
>> schema r1's owenr is r1.
>> i create two table in schema r1, these table's owner is r2 and postgres. but
>> schema r1's owner r1 can drop r2 and postgres's table in schema r1.
>
>This is  the intended and documented behavior.  It's no different than
>if a database owner chooses to drop a database.
>
>            regards, tom lane
</pre></div>

On 10-05-2016 12:55, µÂ¸ç wrote:
> I think we can improve friendly.
> like this :
> drop object direct , if user it's not superuser & owner, then error.
> and when drop schema or database.
>
I don't buy that it is an improvement. First, you will break
applications that rely on that behavior since 7.3 (2002). Second, we
have workaround to avoid such DROP occurs -- set a reliable schema owner.


--
   Euler Taveira                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On 5/10/2016 5:46 PM, 德哥 wrote:
>
> I mean schema owner can drop object in its schema direct, not friendly.

if you don't trust the schema owner, who do you trust??

'who will watch the watchers?'


--
john r pierce, recycling bits in santa cruz