pg_dump problem

Started by C. R. Oldhamalmost 25 years ago4 messagesgeneral
Jump to latest
#1C. R. Oldham
cro@ncacasi.org

Good morning,

I tried to dump a pg 7.1 database last night, but got the following error:

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

user_id_sequence is just
'create sequence user_id_sequence'

Thinking it might be a 7.1 problem I upgraded to 7.1.2. Same problem.

I created a test case:
p0 rack[44]% psql -U postgres template1
~/wd/kksmith/www/doc/sql
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# create user test;
CREATE USER
template1=# create database test;
CREATE DATABASE
template1=# \q;
p0 rack[45]% psql -U test test
~/wd/kksmith/www/doc/sql
test=> create sequence user_id_sequence
CREATE
test=> \d
List of relations
Name | Type | Owner
------------------+----------+-------
user_id_sequence | sequence | test
(1 row)

test=> \q
p0 rack[46]% pg_dump -S postgres --no-owner test > ~/test.dump
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.
'.
p0 rack[47]% psql -U test test
~/wd/kksmith/www/doc/sql
test=> grant all on user_id_sequence to test;
CHANGE
test=> \q
p0 rack[49]% pg_dump -S postgres --no-owner test >! ~/test.dump
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.

I'm stumped. Suggestions? Post to pgsql-hackers? Or file a bug report?

--cro
cro@ncacasi.org

#2C. R. Oldham
cro@ncacasi.org
In reply to: C. R. Oldham (#1)
RE: pg_dump problem

cro@nca.asu.edu wrote:

'ERROR: user_id_sequence: Permission denied.

tgl@sss.pgh.pa.us wrote:

Hmm. Which user is pg_dump connecting as?

Ahh! I understand. Exporting a sane value for PG_USER fixed it.

This (and -u) are the only ways to change the user that pg_dump connects
as?

--
/ C. R. (Charles) Oldham | NCA-CASI \
/ Director of Technology | Arizona State University \
/ cro@nca.asu.edu | V:480-965-8703 F:480-965-9423 \

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: C. R. Oldham (#1)
Re: pg_dump problem

"C. R. Oldham" <cro@ncacasi.org> writes:

p0 rack[46]% pg_dump -S postgres --no-owner test > ~/test.dump
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.

Hmm. Which user is pg_dump connecting as? (The -S switch does not
answer this question, btw; that only determines what's emitted into
the output script.) I would expect behavior like this if you are
connecting as someone who's neither a superuser nor the sequence
owner. Check PGUSER and USER environment variables.

regards, tom lane

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: C. R. Oldham (#2)
Re: pg_dump problem

"C. R. Oldham" <cro@ncacasi.org> writes:

Ahh! I understand. Exporting a sane value for PG_USER fixed it.

This (and -u) are the only ways to change the user that pg_dump connects
as?

AFAIR, yes.

regards, tom lane