Обсуждение: 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
Re: BUG #14131: BUG, schema owner can drop otheruser's object in it's schema
От
"David G. Johnston"
Дата:
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>
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.
When:
drop schema schema_name cascade; -- if some object it's not owned by schema's owner, then error.
and hint user to use FORCE option to force drop schema. (this it's currently code).
drop schema schema_name cascade force;
drop database also can use force.
regards, digoal
在 2016-05-10 22:18:46,"David G. Johnston" <david.g.johnston@gmail.com> 写道:
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.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.David J.
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
I mean schema owner can drop object in its schema direct, not friendly.
At 2016-05-11 05:21:56, "Euler Taveira" <euler@timbira.com.br> wrote: >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