\i psql permission denied

0
votes

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.

1
postgresqlpsqlpermission-deniedsql-scripts
Directories have permissions, too. [ what does cat /path/to/file/mysqlfile.sql do? ] – wildplasser
gives me a permission denied error. Maybe if I create user in postgres as my normal account it will work... – Fletchy1995
psql needs two things: 1) connect to the database (this succeeded) 2) read the file (this failed) – wildplasser

1 Answers

1
votes

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.