Thread: Unable to create serial column even with permissions

Severity: Minor
Version tested: 8.4.4
Platform: Solaris 10u8
Steps to reproduce:

1. Create table "sometable" owned by user "someuser", and fill it with a
million generate_series records.

2. Log in as the superuser "postgres".

3. Do:

alter table sometable add column someserial serial;

4. Postgres attempts to add and populate the serial column for this
table.  This takes a while.

5. At the very end, after waiting for creation and population and
locking the table for a while, you get:

ERROR:  sequence must have same owner as table it is linked to

What should happen instead:

If the creating user has permissions on the table sufficient to create a
column on the table, the sequence should be created as owned by the
table owner.

At the very least, postgres should throw an error before spending a lot
of time populating the serial column ( "Only the table owner can create
a serial column" ).

--
-- Josh Berkus
---------------------------------------------------------
Josh Berkus                       PostgreSQL Experts Inc.
CEO                               database professionals
josh.berkus@pgexperts.com         www.pgexperts.com
1-888-743-9778 x.508              San Francisco

Josh Berkus <josh.berkus@pgexperts.com> writes:
> [ as suitably privileged user, alter somebody else's table with ]
> alter table sometable add column someserial serial;
> ERROR:  sequence must have same owner as table it is linked to

> If the creating user has permissions on the table sufficient to create a
> column on the table, the sequence should be created as owned by the
> table owner.

Yeah, I think that would be the most desirable behavior.

It looks to me like the simplest way to make this happen would require
(a) adding a field to CreateSeqStmt to carry the userID we want the
sequence to be owned by;
(b) adding a parameter to DefineRelation to pass in said userID.

(Or we could add a field to CreateStmt rather than a separate parameter
to DefineRelation, but I'm unconvinced that's better.)

We could in theory back-patch this, since CreateSeqStmt won't ever go to
disk in stored rules.  However, tweaking DefineRelation's API in stable
branches seems fairly hazardous to third-party code.  Does it seem
sufficient to fix the problem in 9.0 and up?

            regards, tom lane

> We could in theory back-patch this, since CreateSeqStmt won't ever go to
> disk in stored rules.  However, tweaking DefineRelation's API in stable
> branches seems fairly hazardous to third-party code.  Does it seem
> sufficient to fix the problem in 9.0 and up?

Might be worth asking a few interface developers what this will break.
However, given that the issue has existed for a year or more and I'm the
first one to report it formally, it clearly isn't that huge of an issue.

Any idea what version this got broken in?

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:
> However, given that the issue has existed for a year or more and I'm the
> first one to report it formally, it clearly isn't that huge of an issue.

Longer than that.

> Any idea what version this got broken in?

Presumably, when we added ALTER SEQUENCE OWNED BY, which seems to be
8.2.

            regards, tom lane

I wrote:
> It looks to me like the simplest way to make this happen would require
> (a) adding a field to CreateSeqStmt to carry the userID we want the
> sequence to be owned by;
> (b) adding a parameter to DefineRelation to pass in said userID.

> We could in theory back-patch this, since CreateSeqStmt won't ever go to
> disk in stored rules.  However, tweaking DefineRelation's API in stable
> branches seems fairly hazardous to third-party code.  Does it seem
> sufficient to fix the problem in 9.0 and up?

Since nobody's objected, I will see about making this change in HEAD and
9.0.

            regards, tom lane