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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Gary Doades" <gpd (AT) gpdnet (DOT) co.uk> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On 4 Sep 2004 at 18:18, Tom Lane wrote:

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Sat, Sep 04, 2004 at 06:18:00PM -0400, Tom Lane wrote:
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...8/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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Christopher Kings-Lynne <chriskl (AT) familyhealth (DOT) 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).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Christopher Kings-Lynne <chriskl (AT) familyhealth (DOT) com.au> writes:
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 ...

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Mon, Sep 06, 2004 at 11:41:48PM -0400, Tom Lane wrote:
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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Hmm ... I hadn't thought of that approach, but it seems pretty
reasonable offhand ... comments?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Christopher Kings-Lynne <chriskl (AT) familyhealth (DOT) com.au> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org