Best practice for adding PostgreSQL extensions?

[conradwt]

Hi, I was wondering, what's the best practice for adding PostgreSQL extensions? I ask this question because I can easily add the Ubuntu package (i.e. postgresql-contib) to the runlist to properly get the package installed using Chef. However, it seems that I need to create this extension as root instead of the deploy user. BTW, I tried to install the UUID extension using a Rails migration during a Capistrano run with the following code:

class EnableUuidOsspExtension < ActiveRecord::Migration
  def self.up
    enable_extension "uuid-ossp"
  end

  def self.down
    disable_extension "uuid-ossp"
  end
end

The above generated the following error messages with the PostgreSQL logs:

2014-09-10 07:07:01 GMT STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2014-09-10 07:16:56 GMT ERROR:  permission denied to create extension "uuid-ossp"
2014-09-10 07:16:56 GMT HINT:  Must be superuser to create this extension.

Shouldn't the deploy role be able to alter the application database? Or is an extension being installed at the PostgreSQL server level?

[jvanbaarsen]

@michiels Can you take a look?

[jvanbaarsen]

ping @michiels

[michiels]

@conradwt Thanks for the question. I'm not too familiar with Postgres extensions. Some questions to figure out what might be wrong:

Did you install the Ubuntu package with the extension before doing the migration with enable_extension? If so. Is it possible that this extension can only be installed on a server-wide level?

If these two things are not the case, it might be that we are not correctly adding permissions to the deploy role and that we need to fix that in the chef recipes. Could you try and verify this?

[conradwt]

@michiels Yes, the extension can only be installed on the server-side. Furthermore, extensions need to be installed as superuser or database owner. Thus, as part of the Chef run, I need to create the extension. So, are there general steps for setting up extensions using Chef?

[jvanbaarsen]

@conradwt Are the extensions installable via apt-get? if so you can add them to the package list: https://github.com/intercity/chef-repo/blob/master/nodes/sample_host.json#L8

Please let me know if this solves your issue :)

[conradwt]

@jvanbaarsen Yes, the package will need to be installed using apt-get. The the database-owner, deployer, will need to create the extension on the database just like any table. I'll give this a try and get back to you.

[jvanbaarsen]

@conradwt Ok! Let me know if it works, if so fell free to open a PR to add this to the documentation!