PhotoSupreme Server using Amazon RDS PostgreSQL?

[eqbridges]

Hi,

I would like to use Amazon's RDS PostgreSQL service for hosting Photo Supreme's catalog. But have run into this error:

Severity: ERROR
SQL State: 42501
Primary Error: must be superuser to create superusers

It appears that PhotoSupreme tries to create a user with 'superuser' privileges (I guess it's idimager_main). But RDS reserves the superuser privilege (the master user created during setup is granted a role known as "rds_superuser" which is a subset of the superuser's permissions).

Does PhotoSupreme truly need to depend on a user with superuser privileges? The superuser privilege has very broad permissions and is typically not intended for use by applications.

Is there a subset of permissions that I can assign to a user so that PhotoSupreme is able to do what it needs to do, and so that I can run the database via Amazon RDS?

I would like, if possible, to create a database with the idimager_main user account -- with a subset of permissions -- and then run the scripts under "sqlscripts" to create the schema.

Thanks!

[Hert]

The idimager_main user doesn't need very advanced privileges. And you may certainly downtune it to get it working on Amazon's RDS service. I'm not a PostgreSQL administrator and can't tell you exactly what could be revoked. The user should be able to create databases, make any adjustment in the database scheme, make backups and restores (this may not be very relevant on RDS). If you're an experienced PostgreSQL dba then looking at the database scripts may give you a better idea of what's needed. The database scripts can be found in the Resources folder in the installation folder. I'm very curious if you can get it up and running

[eqbridges]

While I'm not a DBA, I've worked with PostgreSQL as a software developer on various projects since v7.2. I'd be glad to spend an hour or so tweaking the permissions to see what may work.

I dug around under resources, but couldn't find a script to create idimager_main -- it appears to be done in the Photo Supreme binary itself ("IDimagerSU" -- at least that's the only place in the installation where I could find a reference to that user name).

Would it be possible to externalize the script that generates the user account to a file that i can tweak?

[Hert]

You're correct. I looked it up and for the PostgreSQL version, the idimager_main user is created from inside the application. But there's still hope

PSU will only create the user when it doesn't exist yet. And so you can create the idimager_main user yourself before running the database creation routines. Please send me an email for more details.
support@idimager.com

[eqbridges]

I followed these steps:

• created a PostgreSQL RDS instance on Amazon (1)
• created an idimager_main user manually with only the "CREATEDB" permission
• ran the two scripts for building the schema (postgresql9_ididb.sql, postgresql9_tmbdb.sql -- obtained from "/Applications/Photo Supreme.app/Contents/Resources/sqlscripts")
• started PhotoSupreme, was prompted to either replace the database or not. I chose not to. (2)
• next I was prompted to link to the existing database (2)

After this the application started up successfully!

(1): This script https://github.com/ebridges/rds-photo-supreme-catalog lays out the necessary objects required in RDS to get this working, but is still incomplete. I'll try to document the tweaks I did to get it to work (mostly security group rules for connectivity, and including configuration per the Photo Supreme setup document).
(2): The messages in the two prompts at startup were ambiguous and confusing, I think they can be improved.

[Hert]

That is fantastic news! And thank you for documenting it. If possible (and when complete) I'd like to include some of your documentation in the standard installation instructions for the PostgreSQL version.

[eqbridges]

Hi Hert,

After having to work on some other stuff briefly, I got this all working (see the updated notes in the GitHub repo noted above) against my archive of 75k images.

PhotoSupreme starts up fine, and it lists all of the images across all of the folders in my archive. However, thumbnails are not appearing at all. Not sure where to look for error messages (if any).

Please advise, thanks!

[Hert]

It looks like your thumbnail database is not accessible. Did you successfully create it? And is the server name identical as that from the main catalog database?

[eqbridges]

I ran the following two scripts after creating the database instance:

Code: Select all

/Applications/Photo Supreme.app/Contents/Resources/sqlscripts/postgresql9_ididb.sql
/Applications/Photo Supreme.app/Contents/Resources/sqlscripts/postgresql9_tmbdb.sql

I assume the second one (which creates the "idthumbs" table) is the thumbnail database? Is that correct?

So it seems that the "idthumbs" table is empty. Which might be a symptom of the problem....

Also, it appears that this table does not anything resembling a "server name" column in it. How do I confirm that it's correct?

[Hert]

PSU expects the thumbs database to be configured on the same server where the catalog database is also hosted

[weidmic]

Hert,

PSU expects the thumbs database to be configured on the same server where the catalog database is also hosted

Is it possible to have the Thumbs DB on a local computer?

Regards,
Michael

[Hert]

The thumbs database must be on the same database server instance as the catalog database. You can have the thumbs db on your local computer if the catalog db is also on the local computer.

[eqbridges]

Hert, thanks for the reply. Can you please clarify the assumptions I'm making? You refer to a "thumbs database", however it is not clear what you mean by that.

The only reference I can find to something that can store thumbnail information is the abovementioned table "idthumbs"; however a sole table does not a database make.

In any case, the "idthumbs" table is hosted on the same database instance (i.e. host & port) as the other tables created by the abovementioned scripts.

[Hert]

That is indeed the thumbs database, and theres's indeed one table in that database.

[eqbridges]

Hi Hert,

Just to clarify, I'm attempting to troubleshoot an issue where image thumbnails are not appearing when I host the catalog on an RDS instance of Postgres. The answers I'm getting thus far are getting me confused.

1. should 'idthumbs' be in the "photosupreme" database ("database" in an RDBMS sense) as all of the other tables in the catalog? if not what is the name of the database that it should be in?
2. you ask "is the server name identical as that from the main catalog database" -- I'm not sure how to confirm this. Can you be more specific?

Please advise, thank you. Ed