CODEWITHSUNDEEP
postgresqlpsqlpermission-deniedsql-scripts
Fletchy
Fletchy

Reputation: 351

\i psql permission denied

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.

Upvotes: 2

Views: 6814

Answers (1)

Fletchy
Fletchy

Reputation: 351

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.

Upvotes: 3

Related Questions