Обсуждение: 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
Yes, database owner can drop database direct, but it can not drop object in database direct.
but schema owner can drop object in schema direct.
I think this is not good for a Enterprise Database system.
postgres=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> create database db1;
CREATE DATABASE
postgres=> grant all on database db1 to r2;
GRANT
postgres=> \c db1 r2
You are now connected to database "db1" as user "r2".
db1=> create schema r2;
CREATE SCHEMA
db1=> create table r2.t(id int);
CREATE TABLE
db1=> insert into t select generate_series(1,100);
INSERT 0 100
db1=> \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# create table t(id int);
CREATE TABLE
db1=# insert into t select generate_series(1,100);
INSERT 0 100
postgres=> \c db1 r1
You are now connected to database "db1" as user "r1".
db1=> drop table r2.t ;
ERROR: permission denied for schema r2
db1=> drop table public.t ;
ERROR: must be owner of relation t
db1=> drop schema r2;
ERROR: must be owner of schema r2
db1=> drop schema public;
ERROR: must be owner of schema public
db1=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> drop database r1;
ERROR: database "r1" does not exist
postgres=> drop database db1;
DROP DATABASE
regards, digoal
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
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