0

I'm trying to import a table to a database called 'test' that I've already created as the postgres superuser. The file i.e. the table is in a drive/filesystem that's already mounted.

*I'm logged into the postgres user. And other users have read, write and execute permissions for this file.

When I use the following command

\i '/run/media/qazizarifulislam/Windows_files/Work/RA/learning-postgres/person.sql'

I get the error- /run/media/qazizarifulislam/Windows_files/Work/RA/learning-postgres/person.sql: Permission denied

where,

  • qazizarifulislam is my username
  • Windows_files is the filesystem (It's an NFTS and not an external HDD or SDD)
  • The remaining portions are just subfolders.
  • person.sql is a file that contains lines that creates the table and then has querries that inserts records into the table. (Preview shown below)

enter image description here

How do I fix this/ import the table?

5
  • How did you validate that the file is readable? The OS obviously tells you it isn't. I'm inclined to trust it.
    – mustaccio
    Jan 7 at 0:03
  • Doing an ls -la in the working directory gives me -rwxrwxrwx. Although this was from the qazizarifulislam user. Jan 7 at 0:17
  • I tried cd'ing into the folder where this file is kept from the postgres user and permission was denied here as well. How do I give postgres access to my files? Jan 7 at 0:20
  • 1
    I guess that depends on who "you" are, relative to postgres. May be chmod? In any case, this seems to be off-topic here.
    – mustaccio
    Jan 7 at 1:18
  • You need to distinguish between the OS user named 'postgres', and the PostgreSQL user named 'postgres'.
    – jjanes
    Jan 7 at 21:19
0

Your operating system user needs read access to the file and read as well as execute permissions on all directories in the path. You are probably lacking the latter If not, disable SE-Linux.

2
  • More specifically, I'd say that "the operating system account under which PostgreSQL is running" needs this access.
    – Phill W.
    Jan 7 at 11:35
  • @PhillW. No, this is about the user that started psql. Jan 7 at 15:44
-1

In PostgreSQL you should create a 'role' to access the DB, then use psql with the -U option as your regular linux user.

To create a role; As your postgres user run createuser --interactive

Then see the docs on altering your database 'test' to be owned by that new 'role': https://www.postgresql.org/docs/13/sql-alterdatabase.html

https://www.postgresql.org/docs/13/database-roles.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.