Search code examples
postgresqlherokumantis

Deploying Mantis BT on Heroku: Postgresql setup?

I'm trying to deploy Mantis BT on Heroku, using PostgreSQL as the database, as a proof of concept and learning exercise (or perhaps more accurately, as a "climb up a steep learning curve," since I'm a total newbie to all three technologies).

The deploy of the PHP app to Heroku went fine, and accessing the app's URL brings up its admin/install.php page. Provisioning the PostgreSQL database went fine, and gave me a database URL that (obfuscated) looks like this:

postgres://useruseruser:passwordpasswordpassword@ec2-107-21-219-201.compute-1.amazonaws.com:5432/dbnamedbname

I'm able to access the database via psql using those credentials, and the user (predictably) doesn't have the 'usecreatedb' privilege. I can't really make sense of the output of the PostgreSQL \z command, which seems to say I have no privileges on a table I've created:

dbnamedbname=> \z foo;
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges 
--------+------+-------+-------------------+--------------------------
 public | foo  | table |                   | 
(1 row)

but I put that impression down to not really knowing PostgreSQL's idioms yet.

Empirically I've been able to determine that I have the privileges CREATE (for both tables & indexes), SELECT, INSERT, UPDATE, DELETE, ALTER and DROP, which seem to be what Mantis BT requires for its "high-privileged database account."

So, it would seem that I have everything I need to fill in the Mantis BT admin/install.php form:

  • Hostname: ec2-107-21-219-201.compute-1.amazonaws.com
  • Username: useruseruser
  • Password: passwordpasswordpassword
  • Database: dbnamedbname
  • Admin Username: useruseruser
  • Admin Password: passwordpasswordpassword

Two notes here:

  1. Yes, I know the regular DB user should not have all the privileges the Admin user has, but Heroku has only given me one DB username, and (because this is for now just a proof of concept) I didn't want to start down a possible blind alley of trying -- and failing -- to create a second user just yet.
  2. The Mantis BT form says that the Admin username and password are "to create Database if required." Since the database ("dbnamedbname") already exists, I initially thought I could leave these blank, but Mantis BT insists on having values for them (and the install documentation says that if they are not supplied, "the database user will be used").

Yet, when I fill in the form using the values above and click the "Install/Upgrade Database" button, I get a failure indicating that the app could not connect to the database with the credentials provided (the exact same credentials, BTW, that I used to connect to the database using psql):

enter image description here

(One suspicious thing in the above screenshot -- that I haven't yet steeled myself to go hunting for in the code: the obscured Admin password in the page returned by the form submission only shows six bullets, whereas the actual password I pasted was 30 characters long.)

So, questions to anyone who understands how Mantis BT database setup works.

  1. Is it actually passing only six characters worth of password to the DB server, or is that just a UI glitch?
  2. Even after I figure out why it's not connecting to the database -- if that's really true -- is the notation "to create Database if required" (on the Admin username and password entries) really significant? Or will the silly thing go and try to create the database even though the specified database already exists?
  3. If the answer to 2 above is "Yes, it will try to create the database anyway," what's the recommended way to work around that, given how Heroku goes about provisioning PostgreSQL databases?
Solution

  • The "Database connection failed" results from an initial connection attempt in which no database name is specified.

    Attempting to access the server in this way from psql causes the following error:

    $ psql -h ec2-107-21-219-102.compute-1.amazonaws.com -U useruseruser
Password for user useruseruser: 
psql: FATAL: database "useruseruser" does not exist

    Adding the database name as an additional parameter to that Connect() call causes the connection attempt to succeed, and thereafter all table creation steps and checks succeed.

    It's not clear to me whether the problem is with PostgreSQL 9.4.5 per se, or whether it's something in how Heroku in particular configures their PostgreSQL servers.

    Issue filed: https://www.mantisbt.org/bugs/view.php?id=20589

    So, with the code patched, the answers to the questions I asked are:

    1. It passes all parameters as entered, no truncations.
    2. It doesn't attempt to create the database if it already exists.
    3. N/A: Mantis does the right thing.

    As a bonus answer, it's apparently not possible to create a lower-privileged user in a Heroku-provisioned PostgreSQL database -- you're stuck with the one user they give you. Which kinda sucks.