Unanswered: Postgresql Permission issues when using "COPY" from stored procedure
OS: Centos 6
Postgresql Version: 9.1.3
I have an SQL (plpgsql) function which copies a table to a csv file. The location of the CSV file is under some other users home directory. I gave full 777 permissions to that directory so that user 'postgres' has write permissions on it.
Moreover I 'su'd to 'postgres user' and from the bash prompt I 'touch'ed a file in the directory where the CSV file is supposed to go. The file is created without any issue.
But when I execute the function I am getting permission denied. I am using 'psql' to run the function. When I changed the write directory to '/tmp' it is working fine and the created csv file owner is 'postgres'. So obviously it is 'postgres' who is creating the file, same as the postgresql process owner
In some forums it is suggested that selinux might be causing this issue. I changed SElinux mode to disabled and rebooted. The issues persisted.
I would appreciate it if anyone can help me solve this.