Permissions issue trying to move postgres database to a new folder

Questions / Help
1

I’m trying to move the database of a local app to a new folder.
I’ve changed the config file to point to the new folder.
I’ve rsynced to the new folder using -av. Permissions match the old folder.

When starting the database its giving “Can’t access folder. Permission denied.”

I’ve tried changing the permissions as liberal as possible as a test (777). I’ve tried changing the users… I’m still getting the error.

The configs are right. Its trying to access the correct folder. But no matter the what the permissions are set to, it throws the error.

Can you imagine anything else that would cause a permissions denied error other than the actual permissions?

2

You mentioned changing the users but still – have you ran chown recursively?

Also, is it the same filesystem? Same storage device?

3

Yes… just to experiment, I’ve change everything to 777 recursively.

It’s a local app. The old location was in the root folders domain… the new one was within /home/user. I suspect this might have something to do with it.

4

Well if postgres runs as a root it shouldn’t care where the data is, it should be able to access it everywhere.

Have you checked under which user does it run?

Normally it runs as the postgres user and it would be logical that it has no access to another user’s home directory.

5

I suspect this is the issue.
I’ve tried changing the user. So far no luck.
If I want to give posgres access to the /home/user directory how would I do it?

6

Well chmod 777 should have taken care of it. Not sure what else can be done. I suspect you restarted the service after changing access?

7

shutdown, restarted… I can’t imagine what else would be blocking access?

8

So let’s shrink the tests f.ex. with sudo -u postgres touch /home/another_user/data/location – does that succeed?

9

Have a look at here: postgresql - postgres can't access a 777 file - Stack Overflow (especially the comments), I guess that’s what you’re running into

10

Okay, user postgres can access folders /home/ but not anything in home/users/, regardless of the permissions set.

11

Anyone know if a simlink will have the same constraint? And/or if there are anyworkarounds? I need to store the database in the user directory somehow.

12

Symlinking won’t work.

Can you explain why you need to save the data in that folder?
You could change the access rights on the parent directory as well, but I’m not sure that’s good for security.
Another option might be to run postgres as that user and give the user the necessary rights so it’s able to run postgres.

1 Like
13

Maybe use chown to change ownership of the folder to the postgres user (though that’s kind of strange to have another user own its entire /home/user directory but not /home/user/pgdata but still, you can try it). Or indeed make Postgres run as the user whose home dir that is.

Also I don’t know if it’s actually true but the following SO thread claims that the folder must be indeed chown-ed but also have permissions 700: postgresql - How to relax file permissions conditions on postgres database folder? - Database Administrators Stack Exchange

2 Likes
14

I was able to get it to work using:

chown postgres:postgres postgres
usermod -m -d /home/postgres postgres

I rsynced the directories using -a tag.

Then…
sudo mount --bind to bind to the home folder version of the database.

Everything works. Except on reboot, the database has to be recreated.

Testing the bind, I can save data into the /home postgres folder. It is retained thru boots. But for some reason the database isn’t.

Qubes retains only data in the /home folder between boots. This is the reason the database needed to be within the /home folder.

I can’t imagine why its not retaining the database. Any files/folders added to the /home postgres directory are retained. Just not the database?

The startup script rebinds the folders on each boot. The bind isn’t the problem. It’s just not retaining the database.

15

It’s possible that Qubes is interfering, it’s made to isolate apps by default but here I am above my paygrade and can’t really help.

Still, did you also do chmod 700 on the DB folder?