postgreSQL permission denied when reading from file with \i command

Question

this is my problem:

myname@ubuntu:~$ sudo su postgres -c "psql template1"
Password: 
psql (9.1.6)
Type "help" for help.

template1=# \i /create.sql
/create.sql: Permission denied

I have this problem even when the file is on the Desktop. when I copy the text of create.sql and paste it there it works.

using UBUNTU 12.10, postgresql 9.1

Thank you for the help.

score 17 · Answer 1 · edited Sep 17 '20 at 17:15

17

If you work in windows , you just need to pass the entire path wrapped by a single quotation.

test-# \i 'D:\\person.sql' --- > note here double backslash not single

edited Sep 17 '20 at 17:15

Mahmoud Adel

53
3

answered Aug 08 '20 at 11:11

Sihat Afnan

742
7
14

2

Just saved me a bunch of time. Thanks! – DonCarleone Jan 19 '21 at 19:33
Well worked for me like a charm ! Thank you – Omprakash Gautam Jun 28 '21 at 16:22
Also, remember to escape the backslashes in the path as shown in the answer. – EndlessSpace Aug 07 '22 at 00:18

Craig Ringer · Accepted Answer · 2012-12-03T13:33:06.687

13

The problem is that you've launched psql as the user postgres and you haven't granted the postgres user permission to read the SQL file. You'll need to give it permission. The simplest way is to grant world read rights:

chmod a+r create.sql

You can change the default permissions assigned to files by altering your umask; search for more information. Some programs rather annoyingly ignore the umask and set restrictive file permissions anyway, so you always need to know how to grant permissions. See man chmod and man chown.

BTW, you're using a very convoluted method for launching psql as the postgres user. This'll do just fine:

sudo -u postgres psql template1

Note that /create.sql specifies a file named create.sql in root of the file system (/). I doubt this is what you intended.

You probably want to specify it as a relative path (without the leading /) if it's in your home directory, like:

template1=# \i create.sql

or if it's on the desktop and you've started psql in your home directory:

template1=# \i Desktop/create.sql

edited Dec 03 '12 at 13:33

answered Dec 03 '12 at 12:07

Craig Ringer

307,061
76
688
778

No, it is there. I didnt mean relative. i put it in the root directory to make the path shorter. but i get the same problem when the file is on my desktop. – Euler's formula Dec 03 '12 at 12:15
It is not about the path because when the path is wrong the notation is: "No such file or directory" – Euler's formula Dec 03 '12 at 12:46
@Euler'sformula OK... and what're the permissions on the file? `ls -l create.sql` ? – Craig Ringer Dec 03 '12 at 13:13
the output line is: -rw------- 1 myname myname 2381 Dec 3 13:17 create.sql – Euler's formula Dec 03 '12 at 13:21
@Euler'sformula OK, so `postgres` doesn't have read permission. See updated answer. – Craig Ringer Dec 03 '12 at 13:33
I am working in windows and that permission granting command didn't work for me – Sihat Afnan Aug 08 '20 at 11:06
@SihatAfnan Correct, that's for unix-like operating systems. You need to set the file permissions differently in Windows. – Craig Ringer Aug 13 '20 at 06:14

Joe Casey · Answer 3 · 2022-05-25T23:05:28.260

EDIT: A better solution is to move the SQL file to the tmp folder if you are on linux!

I ran into this same issue on Linux but the accepted solution was not sufficient in my case. I eventually realized that you need to make sure that EVERY folder in the path has the correct permissions.

For example if the home folder does not have the correct permissions it does not matter what permissions you give a file inside of the SQL folder.

/home/username/Documents/SQL

score 0 · Answer 4 · answered Sep 24 '22 at 17:09

0

if you are facing same issue after putting quote then use forward slash for paths

answered Sep 24 '22 at 17:09

Dharmadurai

41
4

score 0 · Answer 5 · answered Jun 07 '23 at 10:17

The problem of permission can be solved by changing the permission of every subdirectory.

chmod og+rX /home /home/user

This should resolve the problem.

P.S. There could be a risk of security as the permission of these files is set to others for read and execution

score 0 · Answer 6 · answered Jul 24 '23 at 13:37

It works because you are basically executing the SQL statements as the current user and not reading the file when you copy and paste the entire content of create.sql straight into the terminal. On the other hand, file access permissions are essential in order to use the \i command in psql.

Make sure that the user using the psql command is allowed to access the file /create.sql and that it has the appropriate permissions. The ls -l command can be used to ensure the permissions:

ls -l /create.sql

Ensure that the user executing the psql command is able to read the file. Using the chmod command, you can modify these permissions if you find them very restrictive:

sudo chmod +r /create.sql

It could also be problematic if the file is kept in a directory with restrictive permissions. Make sure that the necessary permissions are set for the parent directory of /create.sql:

ls -ld /path/to/parent_directory

postgreSQL permission denied when reading from file with \i command

6 Answers6

Linked