Postgresql y Sprin Security

Decidí migrar algunas aplicaciones que tengo en base a spring framework 3.1.4 y MySQL hacia PostgreSQL como base de datos, entre las dificultades que encontré es que los manuales de Spring no son muy claro respecto a la migración a una base de datos en particular y por otro lado (sin ser experto en PostgreSQL), no hay mucha informacion realmente util en donde te entreguen los "tips" en la web.

Si necesitan saber de como montar una aplicacion con Spring Framework + Security favor vean este articulo   En caso contrario manos a la obra:

Esquemas de base de datos Spring Security para PostgreSQL

primero es necesario que creen la base de datos en post, en mi caso utilice pgAdmin III para hacerlo gráficamente.

Crear base de datos "secframework"  <-- o el nombre que prefieran.

 CREATE DATABASE secframework

  WITH OWNER = secuser

       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

notar que el dueño del a base de datos es un 'secuser' , en mi caso cree un usuario llamado 'secuser', esto lo pueden reemplazar por el usuario que quiera.

para crear el usuario pueden usar algo como

CREATE ROLE secuser LOGIN
  ENCRYPTED PASSWORD 'md5e24a75ddb6858a5f53aa2f5a169a2f28'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

esto creara un usuario sin mayores privilegios y con el password 'secuser'

luego crear un esquema

CREATE SCHEMA sec;
ALTER SCHEMA sec OWNER TO secuser;
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

luego de que este creado el esquema seria bueno ejecutar las siguientes instrucciones:

ALTER SCHEMA sec OWNER TO secuser;
ALTER ROLE secuser
  SET search_path = sec;
SET search_path = sec, pg_catalog;

con esto le estamos diciendo al postgresql que los objetos deben ser buscados dentro del esquema 'sec' y así podemos evitar el tener que poner como prefijo el nombre del esquema a las tablas, en otras palabra ahora se pueden ejecutar querys como  'select * from user' en vez de 'select * from sec.user'

Esquemas Spring Security.

se deben crear las siguientes tablas (esta el modelo completo, aunque en la practica basta con la tabla de user y authorities)

Tablas Usuarios

 create table users(

      username varchar(50) not null primary key,

      password varchar(50) not null,

      enabled boolean not null);



  create table authorities (

      username varchar(50) not null,

      authority varchar(50) not null,

      constraint fk_authorities_users foreign key(username) references users(username));

      create unique index ix_auth_username on authorities (username,authority);

Tablas Grupos

create table groups (

  id bigserial  primary key,

  group_name varchar_ignorecase(50) not null);



create table group_authorities (

  group_id bigint not null,

  authority varchar(50) not null,

  constraint fk_group_authorities_group foreign key(group_id) references groups(id));



create table group_members (

  id bigserial primary key,

  username varchar(50) not null,

  group_id bigint not null,

  constraint fk_group_members_group foreign key(group_id) references groups(id));

Tabla de persistencia para el login

create table persistent_logins (

  username varchar(64) not null,

  series varchar(64) primary key,

  token varchar(64) not null,

  last_used timestamp not null);

 Tablas para el ACL

create table acl_sid(

  id bigserial not null primary key,

  principal boolean not null,

  sid varchar(100) not null,

  constraint unique_uk_1 unique(sid,principal));



create table acl_class(

  id bigserial not null primary key,

  class varchar(100) not null,

  constraint unique_uk_2 unique(class));



create table acl_object_identity(

  id bigserial primary key,

  object_id_class bigint not null,

  object_id_identity bigint not null,

  parent_object bigint,

  owner_sid bigint,

  entries_inheriting boolean not null,

  constraint unique_uk_3 unique(object_id_class,object_id_identity),

  constraint foreign_fk_1 foreign key(parent_object) references acl_object_identity(id),

  constraint foreign_fk_2 foreign key(object_id_class) references acl_class(id),

  constraint foreign_fk_3 foreign key(owner_sid) references acl_sid(id));



create table acl_entry(

  id bigserial primary key,

  acl_object_identity bigint not null,

  ace_order int not null,

  sid bigint not null,

  mask integer not null,

  granting boolean not null,

  audit_success boolean not null,

  audit_failure boolean not null,

  constraint unique_uk_4 unique(acl_object_identity,ace_order),

  constraint foreign_fk_4 foreign key(acl_object_identity)

      references acl_object_identity(id),

  constraint foreign_fk_5 foreign key(sid) references acl_sid(id));

luego para efectos de prueba se puede ingresar la siguiente data

INSERT INTO authorities VALUES ('admin', 'ROLE_ADMIN');

INSERT INTO authorities VALUES ('admin', 'ROLE_USER');

INSERT INTO authorities VALUES ('user', 'ROLE_USER');



INSERT INTO users VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3', true);

INSERT INTO users VALUES ('user', 'ee11cbb19052e40b07aac0ca060c23ee', true);

esto generará el usuario admin (pass: admin) y el usuario user (pass: user)

finalmente en el spring-security.xml deben editar la URL de conexion para que quede algo asi:
jdbc:postgresql://localhost:5432/secframework"    localhost lo reemplazan por la maquina en donde este instalado el postgress