Error: PostreSQL and Spring-Secure-Core


I recently tried to use Grails with the Spring-Security-Core plugin and PostgreSQL and encountered some strange behavior. I eventually solved the problem and thought someone else might run into the same problem so here is the whole story.

I was using the default in-memory database when I first installed the Spring-Security-Core Plugin … I could make use of the security functionality, everything worked just fine and life was good. But when I switched the in-memory data store with PostgreSQL running on my localhost I couldn’t get my application to run … I always got exceptions during the startup of the app.

After a fair amount of research I realized that the problem originates from the keyword ‘user’ which is used by Grails as the default tablename when mapping the ‘User’ class of the Spring-Security-Core plugin. The problem with this is that ‘user’ is a reserved keyword in PostgreSQL and therefore cannot be used as a tablename that easily.

To resolve this issue there are two simple solutions:

1. You can define a different mapping of the ‘User’ class like so:

class User {
String username
String password
...
static mapping = {
table 'users'
password column: '`password`'
}
}

Here the User class is mapped to the table ‘users’ instead of ‘user’ thus avoiding PostgreSQLs keyword issue.

2. Another solution is to use backticks in the tablename.

class User {
String username
String password
...
static mapping = {
table '`user`'
password column: '`password`'
}
}

The backticks will cause hibernate to escape the  tablename, thus avoiding the keyword issue.

After applying one of these solutions I was able to use the Spring-Security-Core plugin backed by PostgreSQL and everything worked as expected.

I hope this post helps out people experiencing the same issue.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s