Postgresql dump permission denied

Write into directory where postgres user has write access. For instance /tmp.

$ pg_dump -h localhost mydb >/tmp/tempfile

In your attempt postgres user tries to create a file in some random directory belonging to the other user.


postgres User

As the other correct answers said, the folder in which you are trying save the backup does not have permissions assigned to the postgres user (operating system user account). The postgres user is the one running the backup utility. This user account was created during the Postgres installation process. You may have used a different name, but the default is postgres.

Folder With Permissions

The solution is to either find or create a folder where the postgres user has read-write permissions.

Mac OS X

In Mac OS X (Mountain Lion), I am able to create such a folder in the Finder.

  1. In the Finder, create a new folder. Select it.
    In this example, I created a folder named postgres_backups.
  2. Choose File > Get Info.
  3. Open the disclosure triangle for the Sharing & Permissions section.
  4. Click the Plus button to add another item to the list of users.
    A list of users appears in a "sheet" dialog.
  5. Select the postgres user from the list.
  6. In the Privilege column, for the new postgres row, change the popup menu to Read & Write.
  7. Close the Get Info window. Done.

Now you can direct your Postgres backup files to that folder.

screen shot of a "Get Info" window in the Finder

By the way, I use the pgAdmin app to do backups and restores. Control+click on the desired database and choose Backups…. The pgAdmin app was probably bundled with your Postgres installation.


backup and restore can be done by any unpriviledged user that knows the postgres superuser password by changing permissions on the working directory:

% mkdir backup

% chmod 0777 backup

% su postgres

[enter password]

$ cd backup

$ pg_dump mydb >tempfile

$ exit

"tempfile" will be owned by postgres and same group as the user


sudo su postgres doesn't change the current directory so you're still in linuxuser's home directory and postgres has no permission to write into it. Change to a different directory

Tags:

Postgresql