Postgresql dump permission denied

I get this error while trying to dump database, i entered

What the problem? i’ve just installed fresh postgresql.

6 Answers 6

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

In your attempt postgres user tries to create a file in some random directory belonging to the other 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

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

«tempfile» will be owned by postgres and same group as the 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.

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.

Permission denied when trying to import a CSV file from PGAdmin

I’m using PGAdmin 1.14.3.

When I try to execute an import command:

ERROR: could not open file «C:\Users\denis\Desktop\BP2Project\USA\US.txt» for reading: Permission denied SQL state: 42501

I did look up other similar questions and none of them solved my issue.

I logged in as user «postgres» who is the superuser. I don’t see why I’m missing permissions. I’m on Windows 7.

8 Answers 8

The user Postgres must have read access on the file from which you are about to copy.

Look at this article to see how to modify files’ security access on Windows.

The permissions article mentioned in the answer by Houari and Flimzy is a good reference material, but a direct answer (the quick fix I used) is:

• Right click the folder containing the data file(s) that permission was denied to and then click Properties.
• In the Folder’s Properties window, select the Security tab.
• Click the Edit button.
• In the «Permissions for the folder» window that opened, click the Add. button.
• Type Everyone into the «Enter the object names to select» text area box.
• Click OK and the window will close.
• Verify that the default Read & Execute permissions were set to Allow via the check checkbox in the previous window.
• Click OK and the window will close.
• Click the Apply button in the Folder Properties window.

Now you can run the SQL COPY statement that needs to access those files.

• Once done, return to the Folder’s Properties window.
• Click the Edit button.
• Select the Everyone entry in the «Group or user names:» field.
• Click the Remove button.
• Click OK on the remaining open windows.

The permissions have now been returned to what they were.

Ok, this is how got COPY command working,to export a table to CSV, step by step. Pls note that I am using pgAdmin 111.

1. Create the target folder you want to export a table to. E.g C:\myExports
2. Set a read/write permission on this folder following the steps below :

Right click the folder containing the data file(s) that permission was denied >to and then click Properties.

In the Folder’s Properties window, select the Security tab. Click the Edit button.

In the «Permissions for the folder» window that opened, click the Add. button. Type Everyone into the «Enter the object names to select» text area box.

Click OK and the window will close. Verify that the default Read & Execute permissions were set to Allow via the >check checkbox in the previous window. Click OK and the window will close.

Click the Apply button in the Folder Properties window.

This is the tricky part, inside myExports folder create a blank CSV file with your desired name.E.g employee.csv

Then run the Copy command like this :

copy employee to ‘C:\myExports\employee.csv’ delimiter ‘,’ csv;

employee is the table name in this example..

Hope this helps.

If you don’t want to give permissions to Everyone, you can add permissions to the account that started the service. In the Control Panel — Administrative Tools — Services, copy the account name in the ‘Log On’ tab. (On my system the account is called ‘Network Service’.) Then share the folder with the CSV-file with this user as shown in the answer above.

To solve this problem you must give permission to the CSV file because that CSV file present in a COPY command are read directly by the server, but not client application. So to make this file accessible to a server we must give full read-write permission so that Postgresql user can read and write on that file.

Reference: article showing step by step procedure.

I just ran into this error and even after adding postgres to permissions on the file folder and the file itself, it still didn’t work. So, I put the file in a public folder. On Windows this was the path: «C:\Users\Public\Documents\census.csv.» It worked!

Responses to this problem on different threads go something like this 1. «Tell me exactly what command you used» 2. «Make sure you have right permissions» 3. «Just use /copy»

I just tried giving permissions to Everyone on the cvs file I am trying to copy from, and it is still giving me the permission denied error. I think this functionality is broken and has been broken for multiple consecutive releases over multiple consecutive versions of Windows.

for me and I’ve just spent some long hours on this. I have a central db residing on a HP box running 14.04 postgresql-9.5 pgAdmin3 postgis-2.2, shares are made through a tweeked Samba share. My clients are using a mixture of windows 10.1, 7, 8.1 and I have one ubuntu 14.04 desktop.

I’m working with large tables updating records and normalising data and have built the routines around SQL copy statements from CSV files which were made from the core COPY public.table_1 TO (the share folder I’d set up in Samba https://www.youtube.com/watch?v=ndAYZ0DJ-U4) ‘/srv/samba/share/[filename].csv’

I can then update the database once the tables have been amended with COPY table_1 from ‘/srv/samba/share/test.csv’ USING DELIMITERS ‘,’ WITH NULL AS » CSV HEADER; from any of my clients.

The key as far as I have been able to determine is that the clients doing the updating must be superusers, also everything must tie up in terms of users as there are 4 servers working together here Postgresql, Samba, UNIX and WINS All of my users are registered on each of the servers with the same username and password homogeneity is the main factor.

Postgresql COPY command giving Permissions denied error

I am trying to COPY a file into a table in PostgreSQL. The table owner is postgres and the file owner is postgres .

The file is in /tmp .

Still I am getting the error message:

could not open file «/tmp/file» for reading: Permission denied

I don’t understand what I am doing wrong as all the posts I’ve found say that if I have the file in /tmp and owner is postgres then the COPY command should work.

2 Answers 2

A guess: You are using Fedora, Red Hat Enterprise Linux, CentOS, Scientific Linux, or one of the other distros that enable SELinux by default.

Either and on your particular OS/version the SELinux policies for PostgreSQL do not permit the server to read files outside the PostgreSQL data directory, or the file was created by a service covered by a targeted policy so it has a label that PostgreSQL isn’t allowed to read from.

You can confirm whether or not this is the problem by running, as root:

then re-testing. Run:

to re-enable SELinux after testing. setenforce isn’t permanent; SELinux will be automatically re-enabled on reboot anyway. Disabling SELinux permanently is not usually a good solution for issues like this; if you confirm the issue is SELinux it can be explored further.

Since you have not specified the OS or version you are using, the PostgreSQL version, the exact command you’re running, ls -al on the file, \d+ on the table, etc, it’s hard to give any more detail, or to know if this is more than a guess. Try updating your answer to include all that and an ls —lcontext of the file too.

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server. (source: postgresql documentation)

So the file should be readable (or writable) by the unix user under which postgresql server is running (i.e not your user!). To be absolutly sure, you can try to run sudo -u postgres head /tmp/test.csv (assuming you are allowed to used sudo and assuming the database user is postgres).

If that fails, it might be an issue related to SELinux (as mentioned by Craig Ringer). Under the most common SELinux policy (the «targeted» reference policy), used by Red Hat/Fedora/CentOS, Scientific Linux, Debian and others. the postgresql server process is confined : it can only read/write a few file types.

The denial might not be logged in auditd’s log file ( /var/log/audit/audit.log ) due to a donaudit rule. So the usual SELinux quick test apply e.g: stop SELinux from confining any process by running getenforce;setengorce 0;getenforce , then test postgresql’s COPY . Then re-activate SELinux by running setenforce 1 (this command modify the running state, not the configuration file, so SELinux will be active (Enforcing) after reboot.

The proper way to fix that is to change the SELinux context of the file to load. A quick hack is to run:

But this file labelling will not survive if hte filesystem is relabel or if you create a new file. You will need to create a directory with an SELinux file context mapping :

Any file created in that directory should have the proper file context automatically so postgresql server can read/write it.

(to check the SELinux context under which postgres is running, run ps xaZ | grep «postmaste[r]» | grep -o «[a-z_]*_t» , which should print postgresql_t . To list the context types to which postgresql_t can write, use sesearch -s postgresql_t -A | grep ‘: file.*write’ . the command sesearch belong to the setools-console RPM package).

Permission denied in Postgres [duplicate]

I added the user myuser to Postgres.

Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres .

Then I tried to give all rights to access and modify mydatabase to myuser . I logged into psql as user postgres :

and then I ran this query:

Now I can use myuser to log, but if I try a simple query I get this error:

Am I missing something? Can you help me solve that?

3 Answers 3

You’ve granted CREATE , CONNECT , and TEMPORARY privileges on the database to myuser but you haven’t granted SELECT and INSERT table privileges yet. You’ll need something like:

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to «give all rights» :

Use the command supplied by @Gord for already existing objects.

You will probably want to grant DEFAULT PRIVILEGES , too. So your user myuser can access future objects automatically, too.

Can be done per schema:

If you omit the schema, it applies to the whole database:

Only applies to objects created by the role specified (defaulting to the role that executes this command):

Available since PostgreSQL 9.0.
Don’t forget to GRANT privileges on SEQUENCES in addition if you have any. (For instance as source for default values in a serial column.)

While the other answers to this question are correct, allow me to propose another option. When a database is restored, you may be able to control the user the database is restored as. I am unsure about the specifics of how to do this with pgAdmin3, but the pg_dump and pg_restore utilities include specific options to allow this to be done.

If you use pg_dump —no-owner then the dump file will not include any ownership restoration. When you restore a dump created using —no-owner , the user you used to do the restore will own all of the objects. If you have a custom-format dump file and are using pg_restore, you can use the —no-owner option with pg_restore to have it skip over any ownership restoration when restoring the database.

Note that this will require the user doing the restore to have the necessary rights on the database to create the objects which exist in the dump file. Generally speaking, granting «CREATE» at the database level to the user doing the restore will be sufficient.