Re: 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(at)gpdnet(dot)co(dot)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(at)gpdnet(dot)co(dot)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(at)gpdnet(dot)co(dot)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(at)fuhr(dot)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

>>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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

>>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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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(at)familyhealth(dot)com(dot)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(at)fuhr(dot)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

> 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(at)familyhealth(dot)com(dot)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

>>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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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(at)familyhealth(dot)com(dot)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/