Обсуждение: pg_dumpall -> database import -> user problem.

Hi,

I've got a problem which I can't find any answers for in the archives
(from simple searches at least) or on Google. I'm running Postgres 7.1.3
on RedHat Linux 7.2.

What I need to be able to do is this:

1. Dump a production database;
2. Create a development database;
3. Import the production database for testing and further development.

The procedure that I'm trying is:

pg_dump production > production.dump
dropdb development
createdb development
psql development -f production.dump

The problem that I'm having is that user permissions aren't being ported
across.

Users from the production database can connect to the development
database. However, when they attempt to select from tables (or do
anything else that they have permissions to in the production database)
they are told "permission_denied". It seems that the database
administrator has to go into the development database and re-issue all
the grant (access) commands.

Is this to be expected?

I haven't seen anything as yet in the createdb or pg_dump manuals that
seems to reflect this behaviour - indeed, it seems that it should be
working _unless_ I specify a "--no-acl".

Any thoughts on this would be appreciated. Apologies if this is answered
in the archives - I did look though and couldn't find anything ;)

Cheers,

-Preston de Guise.

--
'There are no secrets. The networked market knows more than companies do
about their own products. And whether the news is good or bad, they tell
everyone.' http://www.cluetrain.com

Receipt of this email does not constitute permission to add the
originating address to any email list.
--
'There are no secrets. The networked market knows more than companies do
about their own products. And whether the news is good or bad, they tell
everyone.' http://www.cluetrain.com

Receipt of this email does not constitute permission to add the
originating address to any email list.

Preston de Guise <unsane@idl.com.au> writes:
> The procedure that I'm trying is:

> pg_dump production > production.dump
> dropdb development
> createdb development
> psql development -f production.dump

This looks okay to me.  I assume you are running the psql session as the
database superuser?  Does it produce any messages (other than CREATE,
GRANT, etc)?

Have you tried looking at the dump file to see what it's actually doing?
(If the full dump is too bulky to paw through, try pg_dump -s to dump
just schema commands with no data.)

            regards, tom lane

On Tue, 2002-07-02 at 23:43, Tom Lane wrote:
> Preston de Guise <unsane@idl.com.au> writes:
> > The procedure that I'm trying is:
>
> > pg_dump production > production.dump
> > dropdb development
> > createdb development
> > psql development -f production.dump
>
> This looks okay to me.  I assume you are running the psql session as the
> database superuser?  Does it produce any messages (other than CREATE,
> GRANT, etc)?

I've run it as postgres and the user account that created the production
and development databases. Both times it has the same effect.

> Have you tried looking at the dump file to see what it's actually doing?

Yup - it clearly has the "grant..." statements to the userids. (Note -
not the usernames, but to the userids.) The userids in the dump
statements match those in the pg_shadow table and the grant statements
generate no errors.

FWIW, I've always had this problem - have tried this technique before
using Postgres 7.0 and the last 6.x release. I'm starting to wonder
whether it's designed to detect users called "preston" and screw them
around ... lol.

Cheers,

-Preston.

--
'There are no secrets. The networked market knows more than companies do
about their own products. And whether the news is good or bad, they tell
everyone.' http://www.cluetrain.com

Receipt of this email does not constitute permission to add the
originating address to any email list.