Thread: 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.