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.

Answer 1

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

Answer 2

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