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