Programming Synology

Edit Synology Photo Station Album Permissions via Script

I’m using Synology Photo Station for storing all my pictures and share access to all family members. Being an IT-Guy I’m very concerned about security and especially the access rights to all albums. Unfortunately, Synology marks a new album always as public. Instructing my family to set the correct album permissions didn’t work because they’re normal users and they don’t want to do any settings for albums. They just want to access and upload their photos.

Synology Photo Station Permissions

At this point, I decided to have a look on the Postgres SQL Database that is being used by Synology Photo Station for storing all meta-data. I installed the community package phpPGAdmin on my Synology to browse the Postgres Database:

phpPgAdmin

After some tests I identified the tables that were used for storing the permissions on the albums:

  • photo_share
  • photo_access_right_for_dsm_account
  • photo_manage_right_for_dsm_account
  • photo_upload_right_for_dsm_account
  • The photo_share table contains an entry for each album that is being created. The important column is the boolean field “public”. This must be set to “FALSE” to disable public album access.

    Photo Station 6 Permission Tables

    The SQL statement for setting the “public” field to “FALSE” for all entries will be:

    UPDATE photo_share SET public=FALSE WHERE public=TRUE
    

    Note: If you set the field “public” to “FALSE” without editing the following tables only members of the admin group will have access to the albums.

    Now it’s time to update the three permission tables (access, manage and upload). All three tables have the same structure:

    userid Linux userid of the DSM user
    shareid Reference to “shareid” column in photo_share table
    create_time Timestamp when the permission entry was created

    First of all, I dropped everything from the three tables to start without any orphaned permissions:

    DELETE FROM photo_access_right_for_dsm_account;
    DELETE FROM photo_upload_right_for_dsm_account;
    DELETE FROM photo_manage_right_for_dsm_account;
    

    At this point all permission entries are being removed from Photo Station only members of the admin group will have access:

    Permissions cleared

    The next step is to add the correct permissions to all albums. I decided to give all my family members access, upload and manage permissions because I don’t want to restrict them. To store the permissions inside the database, I needed to get the userid for the users. This can easily be done using a telnet or SSH session to the Synology:

    Get userid

    Now I had everything in place to build the SQL query to insert one entry for each shareid (aka album) and each userid in each of the three tables (six queries total):

    INSERT INTO photo_access_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_upload_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_manage_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_access_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_upload_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_manage_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;
    

    That’s it – all permission are set correctly:

    Photo Station 6 Correct Permissions

    The next step will be to put everything in a shell script to automate this task using the Synology task scheduler:

    #!/bin/bash
    
    # Set all Albums to private
    /usr/syno/pgsql/bin/psql -U admin -d photo -c "UPDATE photo_share SET public=FALSE WHERE public=TRUE;"
    
    # Remove all permission entries
    /usr/syno/pgsql/bin/psql -U admin -d photo -c "
    DELETE FROM photo_access_right_for_dsm_account;
    DELETE FROM photo_upload_right_for_dsm_account;
    DELETE FROM photo_manage_right_for_dsm_account;"
    
    # Add new permissions
    /usr/syno/pgsql/bin/psql -U admin -d photo -c "
    INSERT INTO photo_access_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_upload_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_manage_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1028',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_access_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_upload_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;
    
    INSERT INTO photo_manage_right_for_dsm_account (userid, shareid, create_time)
    SELECT '1030',shareid, now() 
    FROM photo_share;"
    

    I configured the script to run once a day – this should be sufficient for my needs. Maybe someday I will find a trigger to execute the script whenever my Synology wakes up from sleep.

    You may also like
    VBScript: Change Word Document Template
    Update environment variables in current process
    Synology DS Photo+ DLNA Mirroring Reverse Engineering Part 1
    5 Comments
    • 2016-08-03T19:51:11+00:000000001131201608 at 19:51
      Reply

      Hello Michael,
      I have been trying to install phpPgAdmin from community packages but it does not work for me. Then i tried adminer which installs fine on DSM 6 but then throws this error when trying to login with my admin account: SQLSTATE[08006] [7] FATAL: no pg_hba.conf entry for host “127.0.0.1”, user “admin”, database “photo”

      Perhaps you could point me in the right direction ?
      Did you install dsm6 yet ?
      Dit you find that the location for the database changed ?

      Christian

    • John
      2016-03-27T16:43:21+00:000000002131201603 at 16:43
      Reply

      Is it possible that the directory for postgres queries has been moved to a different location with DSM 6.0? Above is not working for me anymore, however there is a “new” directory /volume1/@database/pgsql/bin/psql, but I always get a “permission denied” when trying to execute queries?!

      • 2016-04-28T09:15:48+00:000000004830201604 at 09:15
        Reply

        Hello John,

        I haven’t updated to DSM 6 – maybe I will do that within the next weeks.

    • Mathias
      2016-01-24T18:40:39+00:000000003931201601 at 18:40
      Reply

      Great article 🙂
      Just one question – why didn’t you change the web application so that the default folder type offered is “Private”?

      • 2016-01-26T12:02:15+00:000000001531201601 at 12:02
        Reply

        Hey Mathias,

        how can I change the default folder type that is being offered? That would be a great feature. BTW: I needed the SQL-way for fixing my over 400 existing albums 🙂

    Leave Your Comment

    Your Comment*

    Your Name*
    Your Webpage