0

I posted this as an answer on the original question posed on postgreSQL permission denied when reading from file with \i command But I now realise I should have posted as a separate question.

My OS is Fedora 21, I have installed PostgreSQL-9.4

I am trying to read a file using the \i command and error message is Permission denied. I have chmod a+r on the file. On my system there are 3 users [root, damo, postgres] and I have established a group [project] that has 2 members [damo, postgres]. I have used chgrp on all relevant directories which (I believe) should grant permission to user [postgres] to access various files. I access pqsql with the user [postgres] but all of my other work is under user [damo]. Within pgsql using the tab to navigate from the command \i stops after 

    ../../home/damo

and I can go no further. This obviously important but I don't know what it means. So I moved the file to that location and it still does not load. This is the output from ls -l and stat 

    $ ls -l testScript.sql 
    -rw-r--r--. 1 damo project 76 Nov  5 18:18 testScript.sql

    $ stat testScript.sql 
      File: ‘testScript.sql’
      Size: 76          Blocks: 8          IO Block: 4096   regular file
    Device: fd02h/64770d    Inode: 2623547     Links: 1
    Access: (0644/-rw-r--r--)  Uid: ( 1000/    damo)   Gid: ( 1001/ project)
    Context: unconfined_u:object_r:user_home_t:s0
    Access: 2015-11-05 18:18:06.082928881 +0000
    Modify: 2015-11-05 18:18:06.084928866 +0000
    Change: 2015-11-05 18:25:57.041183384 +0000
    Birth: -

Can anyone advance some suggestions?

Community
  • 1
  • 1
fatherdamo
  • 165
  • 1
  • 2
  • 13
  • \i is a psql command, not a PostgreSQL command. Therefore it will be executed by your psql. Can you access the file from where you are running psql? Do you have read permission on all directories as well? – A. Scherbaum Nov 05 '15 at 20:49
  • I'm not sure I understand the difference between psql and PostgreSQL? – fatherdamo Nov 05 '15 at 21:03
  • I did `chgrp` on all directories above /home/damo/anotherLevel/ but psql doesn't allow me to navigate up to them.So I moved the file to where psql would let me see, `chmod` and still no joy. – fatherdamo Nov 05 '15 at 21:05
  • I have now tried `chown postgres testScript.sql` and checked that ownership of the file has changed to postgres. psql still declares permission denied. – fatherdamo Nov 05 '15 at 23:26
  • psql is the commandline client, PostgreSQL is the database server. Usually, the server runs on a different account (it's "postgres" on Unix and Linux). Have you tried doing a "su - postgres" from root, and see if you can access this file as user "postgres"? Or can you simply move the file to /tmp and see if it works from there? – A. Scherbaum Nov 06 '15 at 13:04
  • It turns out there was am access setting on my home directory that was stopping further access. A simple `chmod` sorted it out. Thanks for taking the time, I appreciate it. – fatherdamo Nov 06 '15 at 21:19

1 Answers1

1

You're on Fedora 21, so you likely have SELinux enabled by default. The postgres user probably doesn't have the rights to access the security context user_home_t. The security context is shown by your stat output. You can include it in ls using ls -lZ.

The selinux boolean postgresql_selinux_unconfined_dbadm looks like what you want for that, per getsebool -a.

Check

getsebool postgresql_selinux_unconfined_dbadm

and if it's off, try:

setsebool postgresql_selinux_unconfined_dbadm on

Also, to determine whether selinux is what's denying access, check the system logs or run the sealert tool (SELinux alert browser).

Apart from that, it could well be that one of the directories in the absolute path of the file denies access to your operating system user.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Thanks for the response. `setsebool` was set to on. I used `chmod -R 755 myHomeDirectory` and [postgres] can now access the files. I really need to understand a lot more about how the file permissions are being set on my system. – fatherdamo Nov 06 '15 at 11:19
  • @fatherdamo Oh so a missing 'x' bit on a directory probably – Craig Ringer Nov 06 '15 at 11:20
  • @fatherdamo thank you very much for your feedback. I was stuck for a while now and you got me out of this situation. Best regards. – Olivier C Aug 16 '21 at 12:17