PostgreSQL COPY TO doesn’t honor Linux group privileges

All we need is an easy explanation of the problem, so here it is.

I have issues executing COPY to command, receiving below error msg:

ERROR: could not open file "/home/pgsql/TMP/out.txt" for writing:
Permission denied

  • Postgres engine is being run as postgres user
  • Postgres user belongs to app group
  • TMP folder has below privileges set:
drwxrws---  2 pgsql app       23 Mar 28 19:47 TMP

I don’t understand why it doesn’t work.

I did quick check login as postgres user and creating file and this worked:

sudo -i -u postgres

If I change privileges to 777 all works as expected (I can do copy to command with no issues)
So as to me it looks postgres doesn’t somehow honor Linux groups rights

Can someone guide me how to resolve that (777 is not an option to me)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Server reboot helped. Privileges are treated as expected
(Not sure if only DB engine is enough to fix that thought – I had to reboot entire machine)

Thanks @Colin for tip

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply