\i psql permission denied

Question

I am trying to run a .sql script that uses postgresql. I have installed postgresql, and created a database to run the script in however I continuously get permission denied error on the file. Looking for a solution to issue or workaround to allow me to execute the psql script.

What I have Tried: BASH:

chmod 777 mysqlfile.sql
chown postgres mysqlfile.sql

psql commands:

\i /path/to/file/mysqlfile.sql
psql -U postgres -d testdb -a -f mysqlfile.sql

I have already looked at the following solutions and they did not resolve my issue: Sol1, Sol2, I used this site as instructions for setting up postgresql.

Currently I am getting a permission denied error when trying to run the commands I used above, an ideal solution/workaround would be to execute the sql script in postgres on a fresh database.

Directories have permissions, too. [ what does `cat /path/to/file/mysqlfile.sql` do? ] — wildplasser, Apr 01 '21 at 16:45
gives me a permission denied error. Maybe if I create user in postgres as my normal account it will work... — Fletchy, Apr 01 '21 at 16:49
psql needs two things: 1) connect to the database (this succeeded) 2) read the file (this failed) — wildplasser, Apr 01 '21 at 16:55

score 3 · Accepted Answer · answered Apr 01 '21 at 17:05

I was able to find a solution after @wildplasser 's comment. Below I have commands run, in order, for workaround then explanation comes after.

cs@cs: sudo -u -i postgres
postgres@cs: psql
(postgres) psql: CREATE DATABASE cs;
(postgres) psql: GRANT ALL PRIVILEGES ON DATABASE cs TO cs;
(postgres) psql: exit 
postgres@cs: exit
cs@cs: psql
(cs) psql: \i /path/to/file/mysqlfile.sql

Why I had to do this way:

When you connect to psql as someone other than postgres it also looks for a database to connect to as that name. So I had create a database (cs) which is the same name as my local account (cs) or else it would error out. I also gave myself all permissions on that database. I was then able to execute the command as expected.

This solution is a little gross. I'll monitor this thread for a while and if something better comes along I'll gladly switch the answer.

No `psql` is looking for `-d` database name, `-U` username, `-h` hostname and/or `-p` port number. In the absence of explicit declarations it will fall back to defaults. So if you want to connect to a specific database as a specifier user then use the arguments above to do so. This all spelled out here [psql](https://www.postgresql.org/docs/current/app-psql.html) and here [Key words](https://www.postgresql.org/docs/12/libpq-connect.html#LIBPQ-PARAMKEYWORDS). — Adrian Klaver, Apr 01 '21 at 17:52
Yes I saw that solution in one of the links from my initial question I posted. — Fletchy, Apr 02 '21 at 15:58

\i psql permission denied

1 Answers1