[java] Spring Security with roles and permissions

This is the simplest way to do it. Allows for group authorities, as well as user authorities.

-- Postgres syntax

create table users (
  user_id serial primary key,
  enabled boolean not null default true,
  password text not null,
  username citext not null unique
);

create index on users (username);

create table groups (
  group_id serial primary key,
  name citext not null unique
);

create table authorities (
  authority_id serial primary key,
  authority citext not null unique
);

create table user_authorities (
  user_id int references users,
  authority_id int references authorities,
  primary key (user_id, authority_id)
);

create table group_users (
  group_id int references groups,
  user_id int referenecs users,
  primary key (group_id, user_id)
);

create table group_authorities (
  group_id int references groups,
  authority_id int references authorities,
  primary key (group_id, authority_id)
);

Then in META-INF/applicationContext-security.xml

<beans:bean class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder" id="passwordEncoder" />

<authentication-manager>
    <authentication-provider>

        <jdbc-user-service
                data-source-ref="dataSource"

                users-by-username-query="select username, password, enabled from users where username=?"

                authorities-by-username-query="select users.username, authorities.authority from users join user_authorities using(user_id) join authorities using(authority_id) where users.username=?"

                group-authorities-by-username-query="select groups.id, groups.name, authorities.authority from users join group_users using(user_id) join groups using(group_id) join group_authorities using(group_id) join authorities using(authority_id) where users.username=?"

                />

          <password-encoder ref="passwordEncoder" />

    </authentication-provider>
</authentication-manager>