[postgis-users] Dump, Restore and Users

[Mark Cave-Ayland]

On Tue, 2007-02-06 at 13:53 +0100, Stefan Schwarzer wrote:
> Hi,
> 
> another headache for me is the problem of having different users and  
> user rights on different machines. Whatever I do, it doesn't work.  
> It's such a "mess" that I can't even really explain what approaches I  
> tried to solve the problem...
> 
> In principal it's pretty easy (working on Mac):
> 
> 	1) I am (UNIX) user "schwarzer" on my machine
> 	2) the Postgres database (with its tables) belongs to user "xyz"
> 
> But then the problem:
> 
> 	1) As far as I understand, the Postgis part must be installed by a  
> superuser
> 	2) I created a Mac-User with the name "xyz" (who has as well admin  
> rights)
> 	3) I entered the following command: sh postgis_restore.pl  
> lwpostgis.sql new_database pgdump_old_database.sql
> 
> Then, I get the following message:
> 
> 	pg_restore: [archiver] could not open input file: Permission denied
> 
> The, I tried it with a "sudo sh...." and got this:
> 
> 	createdb: could not connect to database postgres: FATAL:  role  
> "root" does not exist
> 
> Gush...
> 
> Any help is very much appreciated!
> 
> Thanks a lot!


Hi Stefan,

You must remember that PostgreSQL users are different from the users on
your Mac. So when the documentation states that PostGIS must be
installed by a superuser, it means as a database superuser, not the root
user on your Mac.

For a default install, the database superuser is created as the name of
the user who executed the "initdb" command to create the database
cluster. For example, most of the Linux distros create a system user
called "postgres", and run the database server as the "postgres" user.
Hence if I want a superuser account I can just do:

	su - postgres
	psql -d postgis

Alternatively you can set the PGUSER environment variable to the name of
your database superuser before executing the postgis_restore.pl script.


HTH,

Mark.