Thread: BUG #3921: CREATE TABLE / INCLUDING INDEXES fails with permission denied

The following bug has been logged online:

Bug reference:      3921
Logged by:          Andrew Gilligan
Email address:      andy@tcpd.net
PostgreSQL version: 8.3.0
Operating system:   FreeBSD 4.11
Description:        CREATE TABLE / INCLUDING INDEXES fails with permission
denied
Details:

Hi all,

There seems to be an incorrect permission failure if INCLUDING INDEXES
is specified while creating a table, even if the user owns the database.

The example below was run on 8.3.0 cvs, checked out earlier today.
No changes were made to any tablespaces.

template1=# CREATE USER andy PASSWORD 'foo';
CREATE ROLE
Time: 2.350 ms
template1=# CREATE DATABASE test OWNER andy;
CREATE DATABASE
Time: 225.004 ms
template1=# \c test andy
Password for user andy:
You are now connected to database "test" as user "andy".
test=> CREATE TABLE t1 (a int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
Time: 25.515 ms
test=> CREATE TABLE t2 (LIKE t1 INCLUDING INDEXES);
ERROR:  permission denied for tablespace pg_default
test=> \db+
                       List of tablespaces
    Name    | Owner | Location | Access privileges | Description
------------+-------+----------+-------------------+-------------
 pg_default | pgsql |          |                   |
 pg_global  | pgsql |          |                   |
(2 rows)

"Andrew Gilligan" <andy@tcpd.net> writes:
> test=> CREATE TABLE t2 (LIKE t1 INCLUDING INDEXES);    
> ERROR:  permission denied for tablespace pg_default

How annoying :-(.  We could work around this particular manifestation
with a patch to make generateClonedIndexStmt() not set index->tableSpace
if the source index is in the database's default tablespace.  However,
it seems to me there are a number of definitional oddities in this area,
so I'm bouncing this over to -hackers for some discussion.

1. DefineIndex() treats an omitted tablespace clause differently from
explicitly specifying the tablespace that is the database's default:
if you explicitly specify the space then you must have permissions on
it, otherwise you don't need any.  (This is the same behavior as in
DefineRelation incidentally.)  Maybe this isn't such a hot idea, and
we should treat the two cases interchangeably?

2. Because heap_create() replaces MyDatabaseTableSpace with zero before
storing reltablespace into pg_class, it is impossible to tell after the
fact whether a table or index was created with an explicit tablespace
specification that happened to match the DB's default, or whether the
tablespace clause was omitted.  This seems inconsistent with point 1,
if we decide to maintain the current behavior that the cases are not
treated identically.  However we can't just change this, because of the
way that CREATE DATABASE works.

3. Should LIKE INCLUDING INDEXES even try to copy the source index's
tablespace?  It doesn't try to copy the table's tablespace.  (However,
the new table's tablespace can be specified elsewhere in the command,
whereas there's noplace to put a per-index tablespace spec.)  IIRC
it was reported as a bug that LIKE didn't copy index tablespaces, and
we just "fixed" that without much thought, but maybe the point needs
more careful consideration.

Thoughts?
        regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> 1. DefineIndex() treats an omitted tablespace clause differently from
> explicitly specifying the tablespace that is the database's default:
> if you explicitly specify the space then you must have permissions on
> it, otherwise you don't need any.  (This is the same behavior as in
> DefineRelation incidentally.)  Maybe this isn't such a hot idea, and
> we should treat the two cases interchangeably?

I always thought that was absolutely bizarre. Security should never depend on
*how* you refer to an object. You should either have access to the object or
not regardless of how you refer to it.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication
support!