Postgres chkpass on hibernate

In this post we will create a identity manager database in postgres and map it to a hibernate ejb on jboss to provide web services that can authenticate and manage its contents.

In the Postgres database

Chkpass is a postgres field type that uses unix crypt() function, therefore we will use a postgres on unix installation. A .dll can be installed and downloaded in windows, will work on that on a later post.
For this exercice we will use a postgres instance installed in a unix based server, I have a linux fedora one. We will use a jboss server to host our EJBs and eclipse to develop the hibernate components.
First thing, we will create the database in the postgres server.
In my fedora server I run this command with the postgres user (su - postgres):

$ createdb identity_manager

Then we will install the chkpass module to our database, for that, we will need to know the location of our postgres contrib folder, in my case, it is on /usr/share/pgsql/contrib, under that directory we have the available modules to be installed in our database, including the chkpass.sql. To install this module we will execute the following command in our terminal as postgres user:

$ psql -d identity_manager -f /usr/share/pgsql/contrib/chkpass.sql

You should see the results of the script running on ice like this:

SET
psql:/usr/share/pgsql/contrib/chkpass.sql:13: NOTICE: type "chkpass" is not yet defined
DETAIL: Creating a shell type definition.
CREATE FUNCTION
psql:/usr/share/pgsql/contrib/chkpass.sql:18: NOTICE: argument type chkpass is only a shell
CREATE FUNCTION
psql:/usr/share/pgsql/contrib/chkpass.sql:25: WARNING: type attribute "externallength" not recognized
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR
CREATE OPERATOR
COMMENT

Next we will create the database structure by running the following DDL script:

CREATE TABLE idm_user(
user_id bigserial primary key,
user_name VARCHAR(50) NOT NULL,
password chkpass NOT NULL,
UNIQUE (user_name));

We can test our database by inserting and selecting a record, just as the PostgreSQL documentation states in the Appendix F.3. In our exercise, first we will insert our first record:

INSERT INTO idm_user(user_name, password) VALUES('admin', 'secret');

We can test the encrypted password by running a simple select on the database:

SELECT * FROM idm_user;

You should see the password field encrypted. The method that we can use to test that a user knows the persisted password could be a query like this one:

SELECT password = 'secret' AS authenticated FROM idm_user WHERE user_name='admin';

The recordset will return a boolean indicating if we have a password match.
In the following post we will create the hibernate entity that will be mapped to this table. Be tuned.

Related articles, courtesy of Zemanta:

• Free Database Software
• Installing Postgres (PostgreSQL) in Cpanel Server
• PostgreSQL dump/restore and client_encoding
• EnterpriseDB launches PostgreSQL Plus Cloud Service