Thread: Permissions problem with sequences
Using PostgreSQL 7.4.2 on Fedora core 2
I have a problem with permissions on sequences when restored to another PC. I cannot
find this specific bug reported.
I have a database with lots of tables & sequences with no problems. I have taken a
dump of this database and restored onto another PC (same pg version and fc2).
All tables, functions etc are present and all permissions on all objects seem to be OK
except for all the sequences. Permissions on all the tables are granted to a single group
and all the "normal" users of the database are in this group. I have reduced the problem
to a simple reproducible case (hopefully).
create a database called test1
in the database cluster:
create group webroster;
create user user1_test password 'test' in group webroster;
in database test1
create table test
(
c1 serial,
c2 int4 not null,
primary key (c1)
);
grant all on test to group webroster;
then do a dump of the database test1 with:
pg_dump -Fc --file=test1.dump test1
on the other machine:
create a database called test1
in the database cluster:
create group webroster;
create user user1_test password 'test' in group webroster;
get the dump file and:
pg_restore --dbname=test1 test1.dump
in the newly restored database connect as user1_test and execute:
insert into test(c2) values(1);
you get the error:
ERROR: permission denied for sequence test_c1_seq
Is this fixed in a later version? I will shortly be testing 8.0 beta in the hope that this is
mended.
Thanks,
Gary.
"Gary Doades" <gpd@gpdnet.co.uk> writes:
> I have a problem with permissions on sequences when restored to
> another PC.
This is not a dump/restore bug, because in fact you would have had the
same behavior before dumping. You never granted permissions on the c1
sequence to user1_test in the first place.
regards, tom lane
On 4 Sep 2004 at 18:18, Tom Lane wrote: > "Gary Doades" <gpd@gpdnet.co.uk> writes: > > I have a problem with permissions on sequences when restored to > > another PC. > > This is not a dump/restore bug, because in fact you would have had the > same behavior before dumping. You never granted permissions on the c1 > sequence to user1_test in the first place. > You're right as ever. I had inadvertantly created the user in the first database as a superuser so the inserts worked OK. I am used to the situation in SQL Server where the "identity" property "belongs" to the table so no explicit permissions need to be granted on this. I guess I was expecting the same behaviour from postgres. Oh well, back to the schema defintion script.... Thanks, Gary.
On Sat, Sep 04, 2004 at 06:18:00PM -0400, Tom Lane wrote: > "Gary Doades" <gpd@gpdnet.co.uk> writes: > > I have a problem with permissions on sequences when restored to > > another PC. > > This is not a dump/restore bug, because in fact you would have had the > same behavior before dumping. You never granted permissions on the c1 > sequence to user1_test in the first place. There is, however, an ownership problem with restoring sequences in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION AUTHORIZATION command and then creates a table, so implicitly-created sequences are restored with the correct ownership. In 8.0.0beta2, however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather creates a table and then issues ALTER TABLE ... OWNER TO. The ownership of implicitly-created sequences is never set, so they end up being owned by the user doing the restore, typically a database superuser. As a result, non-superusers may find that they're no longer able to insert records into their tables after a restore because they no longer own the implicit sequences. I reported this problem several weeks ago: http://archives.postgresql.org/pgsql-bugs/2004-08/msg00086.php Aside from Bruce Momjian's "I have reproduced this problem" there hasn't been any discussion, at least not on pgsql-bugs, and the problem still exists in the latest CVS sources. Please let me know if I haven't made this clear enough or if I've misunderstood something. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> There is, however, an ownership problem with restoring sequences
> in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION
> AUTHORIZATION command and then creates a table, so implicitly-created
> sequences are restored with the correct ownership. In 8.0.0beta2,
> however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather
> creates a table and then issues ALTER TABLE ... OWNER TO.
Yeah, we still need to find a solution for that. I'd prefer not to
back out the ALTER OWNER TO changes, but I will if nothing else
presents itself.
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> However, of course the best thing is to just fix it, which I guess I'll
> have a crack at...
Given that pg_dump does put out GRANT/REVOKE operations on the sequence,
it's certainly aware that the sequence exists. I suspect this is just a
fixable bug (ie, suppression of output of the sequence CREATE command is
being done at the wrong place).
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Given that pg_dump does put out GRANT/REVOKE operations on the sequence,
>> it's certainly aware that the sequence exists. I suspect this is just a
>> fixable bug (ie, suppression of output of the sequence CREATE command is
>> being done at the wrong place).
> I'm trying to think of the solution here.
One way is to allow the ArchiveEntry to be created (ie, suppress the
discrimination against owned sequences at pg_dump.c:7306) and instead
discriminate at the point of emitting the CREATE or DROP from the
ArchiveEntry ... but not when emitting an ALTER OWNER from it.
That does seem a bit ugly though. What about emitting only an ACL
ArchiveEntry instead of a full ArchiveEntry for an owned sequence?
Actually ... given that pg_dump.c:7306 is suppressing the ArchiveEntry
... where the heck are the GRANT/REVOKE outputs coming from? I thought
those were generated off an ArchiveEntry but apparently not. It's too
late at night here for me to feel like tracking this down, but it seems
an important part of the puzzle.
Ultimately I think this one comes down to taste --- do what seems least
ugly ...
> Also, are there any other objects that are going to have this problem?
> Off the top of my head it's only serial sequences.
Can't think of any others ATM. If more come up, we'll need to invent
some infrastructure to support it --- more fields in an ArchiveEntry,
say. That is also a possible solution right now, but I'm not sure it's
worth the trouble as long as there's only one use-case. Again it boils
down to your design taste ...
regards, tom lane
On Mon, Sep 06, 2004 at 11:41:48PM -0400, Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > >> Given that pg_dump does put out GRANT/REVOKE operations on the sequence, > >> it's certainly aware that the sequence exists. I suspect this is just a > >> fixable bug (ie, suppression of output of the sequence CREATE command is > >> being done at the wrong place). > > > I'm trying to think of the solution here. > > One way is to allow the ArchiveEntry to be created (ie, suppress the > discrimination against owned sequences at pg_dump.c:7306) and instead > discriminate at the point of emitting the CREATE or DROP from the > ArchiveEntry ... but not when emitting an ALTER OWNER from it. I raised a question in my original post that I haven't seen discussed: Is failing to change the sequence ownership a bug in pg_dump, or should changing a table's ownership also change the ownership of implicitly-created sequences? That seems the most reasonable behavior to me: I'd expect that the cases where you wouldn't want this to happen would be the exception, not the rule. DROP TABLE cascades to implictly-created sequences -- why shouldn't ALTER TABLE OWNER TO cascade as well? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> ... DROP TABLE cascades to implictly-created sequences -- why
> shouldn't ALTER TABLE OWNER TO cascade as well?
Hmm ... I hadn't thought of that approach, but it seems pretty
reasonable offhand ... comments?
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Hmm ... I hadn't thought of that approach, but it seems pretty
>> reasonable offhand ... comments?
> What if they change the owner of the serial sequence independently
> anyway?
I suppose a complete solution would involve forbidding that. We don't
allow you to alter the owner of an index independently of its parent
table ...
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I've got a mostly working fix for the bug that involves alter owner on
> the sequence, but let me know if you want me to finish it.
Please. We need to see the alternatives ...
regards, tom lane
>>There is, however, an ownership problem with restoring sequences >>in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION >>AUTHORIZATION command and then creates a table, so implicitly-created >>sequences are restored with the correct ownership. In 8.0.0beta2, >>however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather >>creates a table and then issues ALTER TABLE ... OWNER TO. Oer. I'd better look at that :( > Yeah, we still need to find a solution for that. That was a known problem? No-one had told me! > I'd prefer not to > back out the ALTER OWNER TO changes, but I will if nothing else > presents itself. I'd say that the OWNER TO mode fixes many more bugs than it created, plus they can always dump in the old way with a command line switch. However, of course the best thing is to just fix it, which I guess I'll have a crack at... Chris
>>However, of course the best thing is to just fix it, which I guess I'll >>have a crack at... > > > Given that pg_dump does put out GRANT/REVOKE operations on the sequence, > it's certainly aware that the sequence exists. I suspect this is just a > fixable bug (ie, suppression of output of the sequence CREATE command is > being done at the wrong place). Hrm. This seems to be a bug introduced when I moved the OWNER TO commands to be dumped after each object's definition is dumped, instead of just before their ACL is dumped. The problem is that although the SERIAL sequence has an ACL, it has no object, so it never has an OWNER TO command dumped. I'm trying to think of the solution here. I guess we can move the OWNER TO commands to be dumped in the ACL phase, or we can make a special exception for serial sequences. Also, are there any other objects that are going to have this problem? Off the top of my head it's only serial sequences. What's the best solution? I guess we have to put them back on the acl_pass? Chris
>>What if they change the owner of the serial sequence independently >>anyway? > > I suppose a complete solution would involve forbidding that. We don't > allow you to alter the owner of an index independently of its parent > table ... Problem is existing dump files. Oh, hang on - this is new for 8.0. Hmmm...so what do we do then? I have been seriously pressed for time the last few weeks, so I don't have time to make alter table alter all the serial seqeunces on a table as well :( I've got a mostly working fix for the bug that involves alter owner on the sequence, but let me know if you want me to finish it. Chris
> Hmm ... I hadn't thought of that approach, but it seems pretty > reasonable offhand ... comments? What if they change the owner of the serial sequence independently anyway? Then dumps will be restored incorrectly....not that it matters perhaps... Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> There is, however, an ownership problem with restoring sequences
> in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION
> AUTHORIZATION command and then creates a table, so implicitly-created
> sequences are restored with the correct ownership. In 8.0.0beta2,
> however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather
> creates a table and then issues ALTER TABLE ... OWNER TO.
> Oer. I'd better look at that :(
I think Alvaro's patch to make ALTER TABLE OWNER recurse to serial
sequences fixes all the issues that were raised in this thread ---
does anyone see any remaining problems?
regards, tom lane
On Thu, Sep 23, 2004 at 07:29:47PM -0400, Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > There is, however, an ownership problem with restoring sequences > > in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION > > AUTHORIZATION command and then creates a table, so implicitly-created > > sequences are restored with the correct ownership. In 8.0.0beta2, > > however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather > > creates a table and then issues ALTER TABLE ... OWNER TO. > > > Oer. I'd better look at that :( > > I think Alvaro's patch to make ALTER TABLE OWNER recurse to serial > sequences fixes all the issues that were raised in this thread --- > does anyone see any remaining problems? Looks good so far. I originally noticed the sequence ownership problem when restoring a backup made with pg_dump, and that appears to work now. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/