The database that stores the configuration information and assets that you are using with Final Cut Server is built in PostgreSQL. The name of the PostgreSQL database is px. The implementation of PostgreSQL that runs on Mac OS X for Final Cut Server uses port 5433 by default, although only through the localhost. There are two sets of PostgreSQL binaries on a Final Cut Server. The first is in the /Library/Application Support/Final Cut Server/Final Cut Server.bundle/Contents/PostgreSQL/bin directory. However, the tools here do not function. Use the PostgreSQL binary files to manage the database located in the /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin directory. The actual database information is stored in /var/db/FinalCutServer.
Given the above information, to connect to the database you would use the following command:
psql -d px -h 127.0.0.1 -p 5433 -U postgres
You will now be located at a px prompt. From here you can perform a variety of operations, including creating new tables, removing information, performing queries of simply altering fields within the database. Most of these operations can be extremely dangerous. Be very careful if you will be customizing this information as you can VERY easily unlink critical pieces of information from one another and render your Final Cut Server useless. Therefore, changing information on a Final Cut Server should only be done on a test server prior to any augmentation against a production database AND before changing anything make sure to back up the Final Cut Server database.
The pg_hba.conf file located in the /var/db/FinalCutServer/data directory handles all authentication to the Final Cut Server database. Using this file, it is possible to allow users to connect to your database from another host. If you are going to allow connections from other hosts, then you will likely want to create another user with a strong password and only allow that user. Luckily, in the /Library/Application Support/Final Cut Server/Final Cut Server.bundle/Contents/PostgreSQL/bin/ directory there is a createuser shell script that will help you do this (although it won’t do it for you).
These shell scripts also have other uses as well. For example, if you change your mind about the user that was creted withe the createuser script and wish to delete them at a later date you can also remove them using the dropuser shell script in the same location. One warning is that these scripts do not work out of the box, as mentioned. You will need to customize them with the information we previously used for your server, but I mention them here as they can act as guides for your customization of the PostgreSQL environment.
When connecting to PostgreSQL you can also use a graphical application such as Navicat or PGnJ. In this example, I’m going to use PGnJ to connect to the Final Cut Server px database. To do so, first open PostgreSQL. When prompted for the authentication information, enter it as you see here and click on the Connect button. Remember, you can only connect through the server actually running Final Cut Server unless you alter the pg_hba.conf file to allow other hosts to connect and if you do, you will likely want to identify which specific other hosts have access and how they authenticate to the database.
Once you’re connected, click on the disclosure triangle beside store and then click on the disclosure triangle beside tables. Now you will see a listing of each of the tables that Final Cut Server uses to store the data you have put into Final Cut Server. This data is logically structured in a collection of tables, all starting with the letters px, indicating Proximity (the original creators of Artbox, which was purchased by Apple and turned into Final Cut Server).
One example of something you can do using the tables is to change the wording for one of the screens within Final Cut Server. We’re going to go ahead and change the “This asset is linked to” text on the resources pane of an asset to make this features a little more user friendly (original text can be seen below).
First, open up the pxmdgroup table within PGnJ and click on the pxmdgroup table. Then, scroll down to the row for mdgroupid 1754.
Next, alter the text for the name field. In this case we are going to use “Productions that use this asset”.
Now you’ll need to restart PostgreSQL, which can most easily be done by rebooting your computer. Once it comes back online you’ll be able to view the resources for an asset and see that your changes have been made, as can be seen here:
This is only one example of the many things that you can do by augmenting the PostgreSQL database with Final Cut Server. You can also interact with it directly using shell scripts, further enhancing the workflow automations capable for your environment.