1

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?

Improve this question
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
    Commented Jan 7, 2022 at 0:03
  • Doing an ls -la in the working directory gives me -rwxrwxrwx. Although this was from the qazizarifulislam user.
    – Bruce Murdock
    Commented Jan 7, 2022 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?
    – Bruce Murdock
    Commented Jan 7, 2022 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
    Commented Jan 7, 2022 at 1:18
  • 1
    You need to distinguish between the OS user named 'postgres', and the PostgreSQL user named 'postgres'.
    – jjanes
    Commented Jan 7, 2022 at 21:19

3 Answers 3

Reset to default
1

The problem was deeper and not really related to user roles. My hard drives weren't being mounted into the mnt directory. They were being mounted into a temporary directory. I changed settings using disks so that they're automatically mounted into mnt. Everything worked after that.

Improve this answer
1
  • I apologize for not including the steps. It's hard to reproduce the issue
    – Bruce Murdock
    Commented Jun 7, 2022 at 1:16
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.

Note that the operating system user I am talking about is not the PostgreSQL user (typically postgres), but the operating system user that started psql.

If you cannot find a permission problem, try to disable SE-Linux.

Improve this answer
0
-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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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