Use a schema rather than a database

[Munger]

Currently, the code creates a new database which requires access to the postgres user credentials. This is a major security hole and not really the way to do it. It should be possible to specify an unprivileged postgres user and use a schema within that user's database.

[hakko]

I believe that you can setup the musiccabinet database using the postgres user, and then run the start script for Subsonic with parameter -Dmusiccabinet.jdbc.username=USERNAME and -Dmusiccabinet.jdbc.password=PASSWORD for a less privileged user of your choice.

See https://github.com/hakko/musiccabinet/blob/master/musiccabinet-server/src/main/resources/applicationContext.xml#L463

[Munger]

But how would this be set up on a hosted server where I have no access to the postgres user? I can created as many schemas as I like in my own database, but nothing outside of that.

[hakko]

OK, you presented it as a security hole so I just posted how to avoid running the service using the postgres credentials. Using the link posted above, I do believe that you can specify database too. The default behavior is to connect to jdbc:postgresql://localhost:5432/musiccabinet, but you can override it using parameter -Dmusiccabinet.jdbc.url=... to point it to another database if you're not allowed to create one called "musiccabinet".

See https://github.com/hakko/subsonic/blob/master/subsonic-main/src/main/webapp/WEB-INF/applicationContext-service.xml#L256

[Munger]

OK. I'll give that a try. I ran into problems when trying to build the source as the test suite seems to make certain assumptions about the location of the database. Give me a couple of days to investigate and perhaps I can present a clearer picture. I migrated to Postgres from MySQL relatively recently,and it too me some time to realise that the MySQl concept of a database maps pretty much directly onto a schema in Postgres. 99% of Postgres users can't actually create a database, but are free to create schemas within their personal database. It's just a question of semantics , but I believe it is schemas you really should be working with.

[hakko]

The test suite works the same way, only that it connects to database musiccabinet-test by default. You can change that too, though, here:

https://github.com/hakko/musiccabinet/blob/master/musiccabinet-server/src/main/resources/local.jdbc.properties

I believe that the vast majority of the users of this project haven't used or heard of PostgreSQL before and install it on a machine that they control. So that's where I'm aiming. I use schemas to logically group tables and functions.

Is your proposal to allow users to name these schemas arbitrarily? Or to use just one schema called musiccabinet? Or prefix all schemas with the word musiccabinet?

[Munger]

A schema called musiccabinet would suffice. Even if the user has full control of the machine, they should not be operating as the postgres user. The postgres security model by default restricts uses to working within their own database, creating schemas where the myssql user might use a database. This is why typing psql at the command line does not require you to enter a password, as you are already authenticated to the system and are dropped into your own database.