0

I installed postgresql-common and postgresql-9.4 with the package manager apt-get. I changed my database system from OSX to Debian 8.1 after which I have had difficulties with Permission denied errors. The user postgres exists (CREATE USER postgres;) and database detector exists CREATE DATABASE detector WITH OWNER=postgres;).

I run successfully

masi@pc212:~$ sudo -u postgres psql detector -c "DROP TABLE measurements;"DROP TABLE
masi@pc212:~$ sudo -u postgres psql detector -c "CREATE TABLE measurements ( m_id SERIAL PRIMARY KEY NOT NULL, m_size INTEGER NOT NULL );"
CREATE TABLE

but the same unsuccessfully in Dropbox -directory

masi@pc212:~$ cd Dropbox/
masi@pc212:~/Dropbox$ sudo -u postgres psql detector -c "DROP TABLE measurements;"
could not change directory to "/home/masi/Dropbox": Permission denied
DROP TABLE
masi@pc212:~/Dropbox$ sudo -u postgres psql detector -c "CREATE TABLE measurements ( m_id SERIAL PRIMARY KEY NOT NULL, m_size INTEGER NOT NULL );"
could not change directory to "/home/masi/Dropbox": Permission denied
CREATE TABLE

Settings

The command psql is in the SECURE_PATH in /etc/sudoers: 

Defaults        secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"

The command which psql gives /usr/bin/psql.

The real directory where I keep the code is /home/masi/Dropbox/det/ where possibly the Dropbox installation is affecting thing:

  • drwxr-xr-x 32 masi masi 4096 Jul 14 10:27 masi/
  • drwx------ 26 masi masi 4096 Jul 13 16:05 Dropbox/
  • drwxr-xr-x 8 masi developers 4096 Jul 14 09:22 det/

where I can change the Dropbox to

  • drwx------ 26 masi developers 4096 Jul 13 16:05 Dropbox/

but not able to increase permissions because I start to get ls: cannot access ../../Dropbox/: Permission denied although having fully open permissions. This is a very strange behaviour that here fully open permissions lead to such a behaviour.

Similar errors

  • this thread about nautilus-dropbox but no nautilus-dropbox in my system

Why Dropbox is causing such a problem to PostgreSQL?

Community
  • 1
  • 1
Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697
  • 2
    I'm going to wildly guess that you're trying to run the `sudo` command while in `/home/masi/det`, and that the `postgres` user does not have access to that directory. You should either loosen up permissions to that directory, or run the script from somewhere that has more open permissions. – Anya Shenanigans Jul 14 '15 at 07:53
  • @Petesh Thank you for your comment! I added the complete Permission and owner path to the existing folder in the body of the question. The code which I am trying to run is actually inside Dropbox which I have thought should not affect the issue. – Léo Léopold Hertz 준영 Jul 14 '15 at 08:23
  • 1
    You should be able to open up the Dropbox directory just enough to allow the postgres commands to run using: `chmod go=x /home/masi/Dropbox` (run as yourself). This should allow the subsequent `sudo -u postgres` commands. You generally have to open up access to the directory and *all parent directories* enough to allow commands to work with files in the directory. Execute permission should be enough to allow this. – Anya Shenanigans Jul 14 '15 at 11:47
  • 1
    You seem to claim that `drwx------ 26 masi developers 4096 Jul 13 16:05 Dropbox/` is fully open permission, but that's just showing permission to the `masi` user - you're using sudo to become the postgres user, which surely doesn't have access to your Dropbox folder. `chmod o+rwx /home/masi/Dropbox/` would give access to the postgres user (and anyone else). – nos Jul 29 '15 at 00:47

2 Answers2

1

content of dropbox is encrypted and must be mapped, not every users have access to $HOME/Dropbox on your system. When you use unix file manager line Nautilus or Nemo, Dropbox have to install a plugin so they can browse the files as if it was only a simple directory.

This directory can only be read / write from your user that installed dropbox and usually PostgreSQL runs under it's own user in a different group. you may add the postgreSQL user to your group that have access to $HOME/Dropbox (your user that installed and configured Dropbox)

Alternatly, you might do a script that cp from Dropbox to a normal folder if you intention was to create a live backup even I would discourage using sync cloud service for that for many reasons the first one being this http://support.code42.com/CrashPlan/Latest/Backup/Comparing_Cloud_Backup_And_Cloud_Storage.

EDIT To add postgresql to act as your user you have your own group or to a shared group

Easiest way would be to do ls -la $HOME/Dropbox and see what is the user and group assosiated with ./ then add this specific group to the user postgres

you can type groups postgres to look at groups that this user already have access and to confirm once you added the group.

that way postgres's user would have same access as you. But for security, that might not be what a sysadmin would recommend (which I am not). I would more look like : create a new group (lets say my_dropbox_group) change rights to execute / read / write for that group. then add your user and postgres to the my_dropbox_group's group that way you would restrict postgres to dropbox only and your user would have same access with the my_dropbox_group group. If you have multiple users with each one having a personnal Dropbox, you have to create different groups so peoples can't access other dropboxes, like dropbox_mike and dropbox_paul being two differents dropbox account and you could manage access manually from that

Hope I didn't mixed up thing with the second part

zeachco
  • 754
  • 4
  • 16
  • What is the negative side of this `sudo chmod -R go=rx /home/masi/Dropbox`? – Léo Léopold Hertz 준영 Jul 27 '15 at 18:39
  • 1
    Nothing with the command itself, I just think that having a cloud sync service runing behind core files of any application is risky as there is a lots of situations where this could go wrong. 1. let say you reach you quota, some files will begin to sync but not others, may cause application corruption or being stuck between 2 versions. 2. We don't control when Dropbox is reading / writing, if it decide to write when it should be you application, you get I/O error that the application must handle 3. if you erase files by mistake, changes are replicated everywhere instantly... – zeachco Jul 28 '15 at 15:21
  • For that reason, I have Dropbox Pro with 120 GB and paid backup / version control system. Plus I have my local backups. Any systematic review about the issue is more than welcome! - How can you do the addition of Postgres to group which has access to $HOME/Dropbox I did not manage to get this stable. – Léo Léopold Hertz 준영 Jul 28 '15 at 20:10
  • 1
    In that case, you might not have to worry about quotas =3 for the group thing : `ls -la $HOME/Dropbox` oh wait.. let me update my answer for code readability – zeachco Jul 29 '15 at 00:12
0

Some piece of answer in Petesh's comment but also read -flag is required. Run 

sudo chmod -R go=rx /home/masi/Dropbox

but avoid recursive if you can. The command allows the subsequent sudo -u postgres commands. You generally have to open up access to the directory and all parent directories enough to allow commands to work with files in the directory. Execute s not enough in all cases; read -flag is also required.

Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697