Subject: DROP TABLE can be issued by schema owner as well as table owner

From: Derrick Rice <[email-masked]>
Date: Fri, 20 May 2011 11:42:32 -0400

According to

http://www.postgresql.org/docs/9.0/interactive/sql-droptable.html

"DROP TABLE removes tables from the database. Only its owner can drop a
table."

In fact, the schema owner can drop the table, which is clearly stated here:

http://www.postgresql.org/docs/9.0/interactive/sql-dropschema.html

"A schema can only be dropped by its owner or a superuser. Note that the
owner can drop the schema (and thereby all contained objects) even if he
does not own some of the objects within the schema."

There are likely other places besides the DROP TABLE page which can be
misleading with regard to ability to drop a table.  This should be made more
clear, since in (possibly contrived) circumstances, being able to drop a
table and recreate an exactly similar table may be a vulnerability (if the
design assumed the table could only be dropped by the owner).

(Just joined the list to post this -- sorry if it has already been brought
up)

Derrick

---

From: Guillaume Lelarge <[email-masked]>
Date: Fri, 20 May 2011 18:18:13 +0200

> (Just joined the list to post this -- sorry if it has already been brought

Well, for a specific object, any superuser, the database owner, the
schema owner, and the object owner could drop the object. This is not a
vulnerability.

-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

---

From: Derrick Rice <[email-masked]>
Date: Fri, 20 May 2011 12:35:24 -0400

> schema owner, and the object owner could drop the object. This is not a

It is not documented clearly.  Any information not made clear is an
opportunity for an error which leads to a vulnerability.

It is not a vulnerability in postgresql itself.  It is a vulnerability in an
ill-designed system, which can come about due to misinformation / lack of
clarity.

Putting your first sentence ("For a specific object, any superuser, the
database owner, the schema owner, and the object owner could drop the
object.") in the documentation would remove the opportunity for error.

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 20 May 2011 12:53:49 -0400

> opportunity for an error which leads to a vulnerability.

So we need a standard caveat stmt on all relevant pages?  Seems
reasonable to me.

-- 
Álvaro Herrera <[email-masked]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: Guillaume Lelarge <[email-masked]>
Date: Fri, 20 May 2011 19:24:26 +0200

> So we need a standard caveat stmt on all relevant pages?  Seems

Could be. Not sure it's that important.

-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

---

From: Robert Haas <[email-masked]>
Date: Mon, 13 Jun 2011 12:51:38 -0400

> does not own some of the objects within the schema."

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
- the database owner, since they could drop the entire database
- the system administrator, since they could delete the entire data
directory, or any part of it
- the person with physical control of the machine, since they could
remove and wipe the disk
- any world leader with access to nuclear weapons, since they could...
well, you get the idea

Even if we excluded the last few, it would be quite wordy to
recapitulate this for every object type.  I suggest we steal the
phraseology from "DROP FOREIGN DATA WRAPPER", which reads:

To execute this command, the current user must be the owner of the
foreign-data wrapper.

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Derrick Rice <[email-masked]>
Date: Mon, 13 Jun 2011 16:40:13 -0400

> - 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.

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 13 Oct 2011 10:06:56 -0400 (EDT)

> add ", or the superuser" to the end of the sentence.

I applied the following documentation patch to clarify this issue, and
used generic wording "user with the proper permissions".

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/drop_table.sgml b/doc/src/sgml/ref/drop_table.sgml
new file mode 100644
index 26fe76e..239767f
*** a/doc/src/sgml/ref/drop_table.sgml
--- b/doc/src/sgml/ref/drop_table.sgml
*************** DROP TABLE [ IF EXISTS ] <replaceable cl
*** 30,36 ****
  
    <para>
     <command>DROP TABLE</command> removes tables from the database.
!    Only its owner can drop a table.  To empty a table of rows
     without destroying the table, use <xref linkend="sql-delete">
     or <xref linkend="sql-truncate">.
    </para>
--- 30,37 ----
  
    <para>
     <command>DROP TABLE</command> removes tables from the database.
!    Only its owner and users with the proper permissions can drop a
!    table.  To empty a table of rows
     without destroying the table, use <xref linkend="sql-delete">
     or <xref linkend="sql-truncate">.
    </para>

---

From: Robert Haas <[email-masked]>
Date: Fri, 14 Oct 2011 11:07:46 -0400

> used generic wording "user with the proper permissions".

That doesn't seem like an improvement; what permissions are proper?

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 14 Oct 2011 11:11:33 -0400 (EDT)

> That doesn't seem like an improvement; what permissions are proper?

No idea, but it hints that other users can do it too.  I thought too
specific was too complex for this case.

---

From: Robert Haas <[email-masked]>
Date: Sat, 15 Oct 2011 06:29:14 -0400

> specific was too complex for this case.

I disagree.  I think it's the purpose of documentation to be specific.
 The code says:

        /* Allow DROP to either table owner or schema owner */
        if (!pg_class_ownercheck(relOid, GetUserId()) &&
            !pg_namespace_ownercheck(classform->relnamespace, GetUserId()))
            aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
                           rel->relname);

So the command can be executed by the owner of the table, the owner of
the containing schema, or the superuser.

That seems simple enough.

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 15 Oct 2011 10:08:11 -0400 (EDT)

Robert Haas wrote:
> > No idea, but it hints that other users can do it too. ?I thought too

OK, done.

index 239767f..fc51c7c
  
    <para>
     table.  To empty a table of rows
--- 30,36 ----
  
    <para>
     <command>DROP TABLE</command> removes tables from the database.
!    Only the table owner, the schema owner, and superuser can drop a
     table.  To empty a table of rows
     without destroying the table, use <xref linkend="sql-delete">
     or <xref linkend="sql-truncate">.