Thread: DROP TABLE can be issued by schema owner as well as table owner
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
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
Le 05/20/2011 05:42 PM, Derrick Rice a écrit : > 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) > 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
On Fri, May 20, 2011 at 12:18 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
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.
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.
Excerpts from Derrick Rice's message of vie may 20 12:35:24 -0400 2011: > On Fri, May 20, 2011 at 12:18 PM, Guillaume Lelarge > <guillaume@lelarge.info>wrote: > > > 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. > > > > It is not documented clearly. Any information not made clear is an > 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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Le 05/20/2011 06:53 PM, Alvaro Herrera a écrit : > Excerpts from Derrick Rice's message of vie may 20 12:35:24 -0400 2011: >> On Fri, May 20, 2011 at 12:18 PM, Guillaume Lelarge >> <guillaume@lelarge.info>wrote: >> >>> 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. >>> >> >> It is not documented clearly. Any information not made clear is an >> opportunity for an error which leads to a vulnerability. > > So we need a standard caveat stmt on all relevant pages? Seems > reasonable to me. > Could be. Not sure it's that important. -- Guillaume http://www.postgresql.fr http://dalibo.com
On Fri, May 20, 2011 at 11:42 AM, Derrick Rice <derrick.rice@gmail.com> wrote: > 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." 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
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
Robert Haas wrote: > On Fri, May 20, 2011 at 11:42 AM, Derrick Rice <derrick.rice@gmail.com> wrote: > > 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." > > 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. I applied the following documentation patch to clarify this issue, and used generic wording "user with the proper permissions". -- Bruce Momjian <bruce@momjian.us> 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>
On Thu, Oct 13, 2011 at 10:06 AM, Bruce Momjian <bruce@momjian.us> wrote: > I applied the following documentation patch to clarify this issue, and > used generic wording "user with the proper permissions". That doesn't seem like an improvement; what permissions are proper? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote: > On Thu, Oct 13, 2011 at 10:06 AM, Bruce Momjian <bruce@momjian.us> wrote: > > I applied the following documentation patch to clarify this issue, and > > used generic wording "user with the proper permissions". > > 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Oct 14, 2011 at 11:11 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Robert Haas wrote:
>> On Thu, Oct 13, 2011 at 10:06 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> > I applied the following documentation patch to clarify this issue, and
>> > used generic wording "user with the proper permissions".
>>
>> 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.
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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote: > On Fri, Oct 14, 2011 at 11:11 AM, Bruce Momjian <bruce@momjian.us> wrote: > > Robert Haas wrote: > >> On Thu, Oct 13, 2011 at 10:06 AM, Bruce Momjian <bruce@momjian.us> wrote: > >> > I applied the following documentation patch to clarify this issue, and > >> > used generic wording "user with the proper permissions". > >> > >> 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. > > 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. OK, done. -- Bruce Momjian <bruce@momjian.us> 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 239767f..fc51c7c *** 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 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">. --- 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">.