This is firmly in the “putting it up here just in case anyone else has the same problem” category. There is a gotcha that bites when writing a Grails application that uses the acegi security plugin along with a postgresql database as the data source. The problem is that the acegi plugin will, by default, create a domain class with the name User. When you try to run the application with a postgresql datasource, GORM will try to create a table with the name user, which postgresql will not like as it is a reserved word.

This is a particularly annoying bug, because user is not a reserved word in other RDBMs, so the error will only become apparent when you switch from your development HSQLDB to postgres, by which time you will have have references to Users scattered throughout your application.

Thankfully, the fix is not too onerous; either rename your User class to something else (and do a tentative find/replace in your code) or use a custom GORM table mapping:

class User {
  ..
  static mapping = {
      table 'myappname_user'
  }
}