Forum OpenACS Q&A: Problem dumping under pg 7.1

Posted by C. R. Oldham on 06/13/01 07:31 AM

I'm trying to dump an OpenACS 3.2.5 schema out of postgres so I can
duplicate it on a production server. pg_dump gives me

dumpSequence(user_id_sequence): SELECT failed. Explanation from
backend: 'ERROR: user_id_sequence: Permission denied.

This is with Debian Woody (testing), kernel 2.4.4, pg 7.1 in that
distribution. I've tried different users, including the postgres user.

I first got it with lob_sequence. Thinking it was specific to that
sequence I dropped it. It appears to be a general problem with all
sequences.

I searched the PG lists to no avail. Has anyone seen this?

Thanks.

--cro

2: Response to Problem dumping under pg 7.1 (response to 1)

Posted by Don Baccus on 06/13/01 03:32 PM

This is superficially like a perms problem reported for views, where you could have a view readable by a user but the underlying dummy table not readable by the user, causing PG dump to fail due to a perms problem.

But sequences don't create an underlying dummy table AFAIK...

This problem doesn't occur with simple, vanilla setups (at least, it didn't for me) - what kind of user permissions have you set up? Have you tried granting all permissions on the sequence to the postgres user before dumping?

You may want to talk to the PG development folks about this directly - after upgrading to PG 7.1.2, though, as the view/table problem may've been fixed (and I know some other dump problems were fixed).

3: Response to Problem dumping under pg 7.1 (response to 1)

Posted by C. R. Oldham on 06/13/01 04:11 PM

I upgraded to 7.1.2 and the problem persists.

I'll contact the pg developers and see what they say.
Thanks for the reply, Don!

--cro

4: Response to Problem dumping under pg 7.1 (response to 1)

Posted by C. R. Oldham on 06/13/01 06:57 PM

This was user error.

  pg_dump -S username database

does not connect to postgres as "username".

I exported a sane value for PG_USER to my environment and I was able
to dump the database.