HMAC then BCrypt Passwords for a little extra security

HMAC then BCrypt (also known as Peppering) of user passwords can help in the following scenarios:

Scenario 1: Hacker steals your user database, but does not compromise your web server

Scenario 2: Hacker can run SQL Injection on your web server, but can otherwise not gain access to the web server process

Scenario 3: You can keep your HMAC key in a Hardware Security Module

How it works:

1. "Sign" user's password using HMAC and a key known only to the web server (do NOT store this key in the database)

2. BCrypt the signed user's password

This also has the advantage of allowing longer passwords (BCrypt has a limit of around 70 chars).

See https://blog.mozilla.org/webdev/2012/06/08/lets-talk-about-password-storage/ 

A HMAC then BCrypt password encoder for Spring Security:

public class PepperingPasswordEncoder implements PasswordEncoder {

    private final PasswordEncoder actualEncoder;

    private final Mac mac;

    public PepperingPasswordEncoder(final PasswordEncoder actualEncoder, final String key) throws InvalidKeyException, NoSuchAlgorithmException {
        this(actualEncoder, key, "HMacSha1");
    }

    public PepperingPasswordEncoder(final PasswordEncoder actualEncoder, final String key, final String algorithm) throws InvalidKeyException, NoSuchAlgorithmException {
        this.actualEncoder = actualEncoder;

        SecretKeySpec keySpec = new SecretKeySpec(key.getBytes(), algorithm);
        mac = Mac.getInstance(algorithm);
        mac.init(keySpec);
    }

    @Override
    public String encode(final CharSequence charSequence) {
        return actualEncoder.encode(hmac(charSequence));
    }

    public String hmac(final CharSequence value) {
        return hmac(value.toString());
    }

    public String hmac(final String value) {
        return new String(Base64.encode(mac.doFinal(value.getBytes())));
    }

    @Override
    public boolean matches(final CharSequence rawPassword, final String encodedPassword) {
        return actualEncoder.matches(hmac(rawPassword), encodedPassword);
    }
}

In applicationContext-security.xml:

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

    <beans:bean class="com.databasepatterns.spring.security.PepperingPasswordEncoder" id="passwordEncoder">
        <beans:constructor-arg ref="bCryptPasswordEncoder"/>
        <beans:constructor-arg value="mykey"/>
    </beans:bean>

    <authentication-manager>
        <authentication-provider>
            <password-encoder ref="passwordEncoder"/>
            ...
        </authentication-provider>
    </authentication-manager>

Database Authentication with Spring Security

Imagine if you will, a user has an existing user account with a database server. And you want to log that user in to your website, using that database user info. Here's how to do it with Spring Security.

public class DbAuthenticationProvider implements AuthenticationProvider {

    private String url;

    @Override
    public Authentication authenticate(Authentication authentication) throws AuthenticationException {

        Connection connection = null;
        Statement getRoles = null;
        ResultSet rs = null;

        try {

            Properties properties = new Properties();
            properties.put("user", authentication.getName());
            properties.put("password", authentication.getCredentials().toString());

            connection = DriverManager.getConnection(this.url, properties);

        } catch (SQLException exp){
            try { connection.close(); } catch(SQLException exp2){};
            throw new BadCredentialsException("Bad Credentials");
        }

        /* Authentication worked, now get the user's roles */

        try {

            List grantedAuthorities = new ArrayList<>();

            getRoles = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

            /* we're connected to the db as userX, so applicable_roles will only show userX's roles */

            rs = getRoles.executeQuery("select role_name from information_schema.applicable_roles");

            while(rs.next()){
                grantedAuthorities.add(new SimpleGrantedAuthority(rs.getString(1)));
            }

            UserDetails user = new User(authentication.getName(), authentication.getCredentials().toString(), grantedAuthorities);

            return new UsernamePasswordAuthenticationToken(user, authentication.getCredentials(), grantedAuthorities);

        } catch (SQLException exp) {
            throw new AuthenticationServiceException(exp.getLocalizedMessage());
        } finally {
            try { rs.close(); } catch (SQLException exp) {}
            try { getRoles.close(); } catch (SQLException exp) {}
            try { connection.close(); } catch (SQLException exp) {}
        }

    }

    @Override
    public boolean supports(Class aClass) {
        return true;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}

In applicationContext-security.xml, add:

<beans:bean class="com.databasepatterns.jdbc.DbAuthenticationProvider" id="dbAuthenticationProvider">
    <beans:property name="url" value="jdbc:postgresql://localhost:5432/dbname" />
</beans:bean>

<authentication-manager>
    <authentication-provider ref="dbAuthenticationProvider"/>
</authentication-manager>

Sharing records with PostgreSQL Row Security

We can also share protected rows. We can borrow PostgreSQL's built-in aclitem (Access Control List Item) type and aclexplode() function.

This is in no way optimized for performance.

aclitem is "internal", so may change at any time.

create table protected.shareable_docs (
  id int primary key,
  name text not null,
  owner name not null default current_user,
  row_acl aclitem[]
);

/* No joins in updateable views */
create view shareable_docs with ( security_barrier ) as 
  select
    *
  from protected.shareable_docs
  where
    owner = current_user
    or exists (select 1 from aclexplode(row_acl) where privilege_type = 'SELECT' and pg_has_role(grantee, 'member') )
with check option;

grant delete, insert, select, update on shareable_docs to alice, bob;

set role alice;

/* alice creates a doc and shares it with bob */
insert into shareable_docs (id, name, row_acl) values (1, 'shared doc 1', '{bob=r/alice}');

set role bob;

select count(*) from shareable_docs; --result = 1

You'd need to write triggers to prevent users from deleting shared items though.

An ACL Item is structured like this: grantee=privileges/grantor . Grantee is the role (a user or group) that gets the privileges, and grantor is the role that gives the privileges. Here are some of the privilege codes:

r = SELECT (read)
w = UPDATE (write)
d = DELETE
* = WITH GRANT OPTION

So if alice wanted to grant SELECT (WITH GRANT OPTION), UPDATE, and DELETE privileges to role editors, your aclitem would look like this: editors=r*wd/alice

Simple Row Security with PostgreSQL 9.4

PostgreSQL 9.4 makes row security a whole lot easier:

• security_barrier views are update-able
• WITH CHECK OPTION prevents users from inserting, updating, or deleting rows that they can't / won't be able to see

create role alice;

create role bob;

create schema protected;

create table protected.bank_accounts (
 id int primary key,
 name text not null,
 owner name not null default current_user,
 balance decimal(19,2) not null
);

create view bank_accounts with ( security_barrier ) as 
 select
  *
 from protected.bank_accounts
 where
  owner = current_user
with check option;

grant delete, insert, select, update on bank_accounts to alice, bob;

Users can't set owner to a role in which they don't have membership:

set role alice;

insert into bank_accounts values (1, 'chequeing', 'bob', 500);

ERROR:  new row violates WITH CHECK OPTION for view "bank_accounts"

Users can only see their own stuff:

set role alice;

insert into bank_accounts values (1, 'chequeing', 'alice', 500);

select count(*) from bank_accounts; --result = 1

set role bob;

select count(*) from bank_accounts; --result = 0

delete from bank_accounts; --0 rows affected

MS SQL Server to PostgreSQL Quick Copy

You can quickly copy data from an MS SQL Server database to PostgreSQL using the bcp command, iconv, and psql's \copy command.

I copied 100,000 records from one server, to my laptop, to another server in 1.5 seconds.

Sadly, bcp must write to a file first, so you can't pipe it directly. Delete the file when you're done.

In Linux, you could create a named pipe and pretend it's a file, using the mkfifo command.

bcp can only output Unicode in UTF-16, so we must use iconv to convert the output to UTF-8

I assume that you have psql installed, and that it's configured to connect to your PostgreSQL server.

Windows

1. If bcp is not installed (run bcp.exe -v) then install the Microsoft Command Line Utilities 11 for SQL Server
2. If win_iconv is not installed, download it, rename it to iconv.exe and put it in your PATH

Linux

1. If you have not installed the Microsoft ODBC Driver for SQL Server on Linux, you can get it here

In your shell run (this assumes you are using ActiveDirectory/Kerberos auth. Use -U, -P instead of -T if not):

bcp "select * from some_table" queryout results.tsv -T -S serverHostName -w

then:

iconv -f UTF16 -t UTF8 results.tsv | psql -c "\copy some_table from STDIN"

Delete results.tsv

Fuzzy Record Matching in SQL, Part 1

Let's say you have some existing customer records like this:

Haystack

id	Name	Phone
1	Neil	6045551212
2	Neil	(null)
3	(null)	6045551212
4	(null)	(null)

And you have some new customer records like this (say from another company you bought):

Needles

id	Name	Phone
1	Neil	6045551212

And you want to find possible matches. Now obviously Haystack1 and Needles1 match, but Haystack2 and Haystack3 are possible matches too.

Here's a basic way to find relevant matches, ranked:

select
 n.id as needle_id,
 h.id as haystack_id,
 case when n.name = h.name then 1 else 0 end 
 + case when n.phone = h.phone then 1 else 0 end as relevance
from 
 needles n
join 
 haystack h 
on 
 n.name = h.name 
 or n.phone = h.phone
order by 
 relevance desc;

This gives us:

Results

needle_id	haystack_id	relevance
1	1	2
1	2	1
1	3	1

How To Load SQL data After Hibernate Creates Your Schema

Hibernate can create your database schema for you from Java @Entities using the hbm2ddl tool.

You can also tell it to run 1 or more SQL files after it is done creating your schema, for example to load test or reference data.

In persistence.xml, inside <persistence-unit><properties> add

<!-- "value" should be "create" or "create-drop". Warning, this is destructive! -->
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>

<!-- relative to main/resources -->
<property name="hibernate.hbm2ddl.import_files" value="/import.sql, /import2.sql"/>

<!-- Lets you have multiple statements on multiple lines -->
<property name="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />

Then in src/main/resources, add your SQL files, and they will be run.