Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 86BAAB5DBC6 for ; Mon, 13 Jun 2011 17:40:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 32033-04 for ; Mon, 13 Jun 2011 20:40:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 52F96B5D800 for ; Mon, 13 Jun 2011 17:40:14 -0300 (ADT) Received: by bwz15 with SMTP id 15so4012182bwz.19 for ; Mon, 13 Jun 2011 13:40:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=sSIMut0D2DjjHYsJvi3C4SM7G0dknBCJaJftNn87oiE=; b=WbEtNjQV5Xyg3klhHs39dof9kL4dYT+uR/sfkDUE0zRsyrpC9nTXiM9jWHXaIyw44N SIshqrVVs38vMj+8jSP4GICCaCtk2xNizYFtfQzQYsKDF+ZLDqsE+opR0bVKGTdZrqLL 8oVIanebqG+5xemByIbfyZStPibn0uZ9J3A8w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=UZ4SiffHezLxm7uM0YM6zgfxOvKDXS89N2vlX3nbmzILe2zg8dZBL0YrBMxlAN6qaq YZTIcnJql8MktkBE0pw43izNT/bY5mdv02aDnkAZ3qkcZSv2fCu9FmJ0lTJiPNfENrL6 HC7qaKWStfaYx1zY/vYjjGNqFySI8ftBdmv8Q= MIME-Version: 1.0 Received: by 10.204.100.2 with SMTP id w2mr1902082bkn.22.1307997613685; Mon, 13 Jun 2011 13:40:13 -0700 (PDT) Received: by 10.204.38.136 with HTTP; Mon, 13 Jun 2011 13:40:13 -0700 (PDT) In-Reply-To: References: Date: Mon, 13 Jun 2011 16:40:13 -0400 Message-ID: Subject: Re: DROP TABLE can be issued by schema owner as well as table owner From: Derrick Rice To: Robert Haas Cc: pgsql-docs@postgresql.org Content-Type: multipart/alternative; boundary=001636499157217a8504a59ded6b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.897 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201106/54 X-Sequence-Number: 6828 --001636499157217a8504a59ded6b Content-Type: text/plain; charset=ISO-8859-1 > 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 --001636499157217a8504a59ded6b Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
The sentence rea= lly 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. =A0I don't think it's practical to document the latter. =A0We'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.=A0 Interes= tingly, the database owner CANNOT.
=A0
The phrase "to execute this command" makes the scope of what foll= ows
clear: it's just who can run this command, NOT who might be able by
indirect means to get rid of the object. =A0To cover all bases, we could add ", or the superuser" to the end of the sentence.


Example / Proof:

postgres=3D# select versi= on();=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
=A0version
-----
= =A0PostgreSQL 8.4.8 ...[snip]
(1 row)

postgres=3D# create role db= owner login password 'pass';
CREATE ROLE
postgres=3D# create database testdb owner dbowner;
CREATE= DATABASE
testdb=3D# create role schemaowner login password 'pass= 9;;
CREATE ROLE
testdb=3D# create schema testschema;
CREATE SCHEMA=
testdb=3D# alter schema testschema owner to schemaowner;
ALTER SCHEMAtestdb=3D# create role tableowner login password 'pass';
CREATE= ROLE
testdb=3D# create table testschema.testtable (val text);
CREATE= TABLE
testdb=3D# alter table testschema.testtable owner to tableowner;
ALTER T= ABLE
testdb=3D# \c testdb schemaowner
Password for user schemaowner: =
psql (8.4.8)
You are now connected to database "testdb" as= user "schemaowner".

testdb=3D> \du schemaowner
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 List= of roles
=A0 Role name=A0 | Attributes | Member of
-------------+--= ----------+-----------
=A0schemaowner |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= | {}

testdb=3D> \dt+ testschema.testtable;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= List of relations
=A0=A0 Schema=A0=A0 |=A0=A0 Name=A0=A0=A0 | Type=A0 |= =A0=A0 Owner=A0=A0=A0 |=A0 Size=A0=A0 | Description
------------+------= -----+-------+------------+---------+-------------
=A0testschema | testt= able | table | tableowner | 0 bytes |
(1 row)

testdb=3D> \dn+ testschema
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 List of schemas
=A0=A0=A0 Name=A0= =A0=A0 |=A0=A0=A0 Owner=A0=A0=A0 | Access privileges | Description
----= --------+-------------+-------------------+-------------
=A0testschema |= schemaowner |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
(1 row)

testdb=3D> drop table testschema.testtable;
DROP TABLE=


If I try as DB owner:

// reconnect as superuser.

= testdb=3D# create table testschema.testtable (val text);
CREATE TABLE testdb=3D# alter table testschema.testtable owner to tableowner;
ALTER T= ABLE
testdb=3D# \c testdb dbowner;
Password for user dbowner:
psq= l (8.4.8)
You are now connected to database "testdb" as user &= quot;dbowner".
testdb=3D> drop table testschema.testtable;
ERROR:=A0 permission deni= ed for schema testschema



Derrick

--001636499157217a8504a59ded6b--