running into permission issues when using the COPY command with postgr

Bluish9992 · 2022-12-14 00:13:56

Hello everyone noob here,
trying to learn sql with postgresql 14.6-1 on arch linux kernel: 6.0.12-arch1-1 ,
the current topic of the book i'm reading, has me doing this command

```sql
COPY char_data_types TO '/home/user/path/to/file.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|');
```

but i am getting this error:

```
[2022-12-13 18:49:30] [42501] ERROR: could not open file "/home/user/path/to/file.txt" for writing: Permission denied
[2022-12-13 18:49:30] Hint: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.
```

I thought I could get around this by fiddling around with the directory permissions I was trying to write to so i tried a couple of things, like created the file first and then allowed write permissions on it to all with `chmod a+rwx` or to the directory where i was tyring to have postgres create the file, even created a directory and did `chown directory postgres; chgrp directory postgress` and still an error.

interestingly when i logon as the `postgres` user by doing `sudo -iu postgres` i seem to not have access to any directory outside of `/var/lib/postgres/data` (by not having access i mean not being able to write files with `touch`, and even more i am not able to actually cd into any subdirectory in `/home`). I also gave added the `postgres` user to the `users` and `wheel` groups by doing `usermod -aG users postgres; usermod -aG wheel postgres;` but still same error , i even gave it root access but still no luck.

At the postgres side of thngs, I did a couple of postgres commands while in the psql shell that i found online that i thought could help was to run a couple of `GRANT` commands. i can't remember all of them but here is the output of the psql when I run `\du`
```
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+--------------------------------------
malup | Superuser | {pg_read_all_data,pg_write_all_data}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {pg_read_all_data,pg_write_all_data}
```
But still same error.

I found a quick a suggestion to just write the file to `/tmp` directory so i don't have to mess with permissions. when i do that, i don't get the error, but the file is not there for some reason. I don't know what's going on about that.

The only way i was able to find success in having the `COPY` command actually write to a file is if i do pass in the `postgres/data` dir in the command instead:
```sql
COPY char_data_types TO '/var/lib/postgres/data/file.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|');
```
Is there a way to to actually allow this postgres to write to other user directories in my system? i don't think it's a file-permission-related issue anymore.

I also am aware that that i can use the `\copy` from psql but I want to try it from an sql script.

Last edited by Bluish9992 (2022-12-14 01:33:43)

2ManyDogs · 2022-12-14 00:18:10

Bluish9992, welcome to the forum. Markdown is not supported here -- please use BBCode tags instead.

https://wiki.archlinux.org/title/Genera … s_and_code
https://bbs.archlinux.org/help.php#bbcode

V1del · 2022-12-14 00:44:39

The systemd service of postgres protects and prohibits access to large parts of the file system. https://github.com/archlinux/svntogit-p … ervice#L29 you can of course alter these in an override assuming you are aware of the potential risks/assuming this is just for a local database used for learning you can make a few things less strict. https://wiki.archlinux.org/title/System … ided_units

Last edited by V1del (2022-12-14 08:29:04)

cfr · 2022-12-14 05:59:34

Bluish9992 wrote:

I also gave added the `postgres` user to the `users` and `wheel` groups by doing `usermod -aG users postgres; usermod -aG wheel postgres;` but still same error , i even gave it root access but still no luck.

This is a Bad Idea. If it helped, it would be an Even Worse Idea. The reason these special users exist is typically precisely so that the user is NOT in groups such as users and wheel.

Arch Linux

#1 2022-12-14 00:13:56

running into permission issues when using the COPY command with postgr

#2 2022-12-14 00:18:10

Re: running into permission issues when using the COPY command with postgr

#3 2022-12-14 00:44:39

Re: running into permission issues when using the COPY command with postgr

#4 2022-12-14 05:59:34

Re: running into permission issues when using the COPY command with postgr

Board footer