public inbox for [email protected]
help / color / mirror / Atom feedFrom: Derrick Rice <[email protected]>
To: Robert Haas <[email protected]>
Cc: [email protected]
Subject: Re: DROP TABLE can be issued by schema owner as well as table owner
Date: Mon, 13 Jun 2011 16:40:13 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
> The sentence really should be written a way that indicates that we're
> talking about who can execute this particular command, rather than who
> can manage to accomplish the removal of the object. I don't think
> it's practical to document the latter. We'd have to include:
>
> - the owner of the table
> - the superuser
> - the schema owner, since they could drop the entire schema
>
At least the schema owner can actually run DROP TABLE. Interestingly, the
database owner CANNOT.
> The phrase "to execute this command" makes the scope of what follows
> clear: it's just who can run this command, NOT who might be able by
> indirect means to get rid of the object. To cover all bases, we could
> add ", or the superuser" to the end of the sentence.
>
>
Example / Proof:
postgres=# select version();
version
-----
PostgreSQL 8.4.8 ...[snip]
(1 row)
postgres=# create role dbowner login password 'pass';
CREATE ROLE
postgres=# create database testdb owner dbowner;
CREATE DATABASE
testdb=# create role schemaowner login password 'pass';
CREATE ROLE
testdb=# create schema testschema;
CREATE SCHEMA
testdb=# alter schema testschema owner to schemaowner;
ALTER SCHEMA
testdb=# create role tableowner login password 'pass';
CREATE ROLE
testdb=# create table testschema.testtable (val text);
CREATE TABLE
testdb=# alter table testschema.testtable owner to tableowner;
ALTER TABLE
testdb=# \c testdb schemaowner
Password for user schemaowner:
psql (8.4.8)
You are now connected to database "testdb" as user "schemaowner".
testdb=> \du schemaowner
List of roles
Role name | Attributes | Member of
-------------+------------+-----------
schemaowner | | {}
testdb=> \dt+ testschema.testtable;
List of relations
Schema | Name | Type | Owner | Size | Description
------------+-----------+-------+------------+---------+-------------
testschema | testtable | table | tableowner | 0 bytes |
(1 row)
testdb=> \dn+ testschema
List of schemas
Name | Owner | Access privileges | Description
------------+-------------+-------------------+-------------
testschema | schemaowner | |
(1 row)
testdb=> drop table testschema.testtable;
DROP TABLE
If I try as DB owner:
// reconnect as superuser.
testdb=# create table testschema.testtable (val text);
CREATE TABLE
testdb=# alter table testschema.testtable owner to tableowner;
ALTER TABLE
testdb=# \c testdb dbowner;
Password for user dbowner:
psql (8.4.8)
You are now connected to database "testdb" as user "dbowner".
testdb=> drop table testschema.testtable;
ERROR: permission denied for schema testschema
Derrick
view thread (12+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: DROP TABLE can be issued by schema owner as well as table owner
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox