UNIX-like row permissions in PostgreSQL

create table foo (
  foo_id int primary key,
  content text,
  owner name,      --username of the owner (or could be a key to another table)
  grp name,        --name of the group
  mode varchar(3)  --unix-style file permission mode (ex 777)
);

create extension pg_trgm;

--lets you use indexes with regex:
create index on foo using gin ( mode gin_trgm_ops );
create index on foo (owner);
create index on foo (grp);

insert into foo values
(1, 'bar', 'neil', 'managers', '764'); --764 being owner read/write/delete, group read/write, world read


create function world_read() returns text language sql strict immutable as $$
  select '[4567]$'::text
$$;

/* and similar functions world_write(), group_delete() etc */

select
*
from foo
where
    mode ~ world_read()
    or group in (?) and mode ~ group_read()
    or owner = ? and mode ~ owner_read();

delete
from foo
where 
    mode ~ world_delete()
    or group in (?) and mode ~ group_delete()
    or owner = ? and mode ~ owner_delete();

Duplicity Backup on OpenBSD 5.8

The duplicity 0.6.26 package seems to be broken for OpenBSD 5.8 (i386). Here's how to make it work.

Get the ports tree and unpack it:

pkg_add wget
cd /tmp
wget http://ftp.openbsd.org/pub/OpenBSD/5.8/ports.tar.gz
cd /usr
tar -zxvf /tmp/ports.tar.gz

(this takes a few minutes)

Compile duplicity:

cd /usr/ports/sysutils/duplicity
make install

(go get some coffee)

Install gpg and generate a key:

pkg_add gnupg
gpg --gen-key

(accept the defaults)


Full backup:

duplicity full $sourceDir file:///$targetDir 

Incremental backup:

duplicity incremental $sourceDir file:///$targetDir 

Restore:

duplicity restore file:///$sourceDir $targetDir 

If you're using S3:

• use s3+http://$bucketName as the target
• Your bucket should be in US Standard Region 
• export AWS_ACCESS_KEY_ID=$yourAWSAccessKeyId
• export AWS_SECRET_ACCESS_KEY=$yourAWSSecretKey

Simple Inbound XSS Filter for Spring Security

This is a simple filter to look for XSS in requests. It throws an exception if it finds one.

It looks at all GET and POST parameter names and values, as well as all header names and values.

Examples:

GET /foo?name=<script>alert('');</script>
(exception)

POST /bar
<script>alert('');</script>=someValue
(exception)

PUT /baz
Accept-Language=<script>alert('')</script>
(exception)

Add a Maven dependency for JSoup:

<dependency>
    <groupId>org.jsoup</groupId>
    <artifactId>jsoup</artifactId>
    <version>1.8.3</version>
</dependency>

SimpleInboundXssFilter.java:

public class SimpleInboundXssFilter extends GenericFilterBean {

    private Cleaner cleaner = new Cleaner(Whitelist.none());

    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {

        HttpServletRequest request = (HttpServletRequest) servletRequest;

        Parser parser = Parser.xmlParser();

        /* GET and POST parameters: */
        Map params = servletRequest.getParameterMap();

        for(Map.Entry entry : params.entrySet()) {
            String key = entry.getKey();

            if(!cleaner.isValid(getFragmentAsDocument(key, parser))) {
                throw new InboundXssException();
            }

            String[] values = entry.getValue();
            for(String value : values) {
                if(!cleaner.isValid(getFragmentAsDocument(value, parser))) {
                    throw new InboundXssException();
                }
            }
        }

        Enumeration headerNames = request.getHeaderNames();
        while(headerNames.hasMoreElements()){
            String key = headerNames.nextElement();
            if(!cleaner.isValid(getFragmentAsDocument(key, parser))) {
                throw new InboundXssException();
            }

            Enumeration values = request.getHeaders(key);
            while(values.hasMoreElements()){
                String value = values.nextElement();
                if(!cleaner.isValid(getFragmentAsDocument(value, parser))) {
                    throw new InboundXssException();
                }
            }

        }

        filterChain.doFilter(servletRequest, servletResponse);
    }

    private Document getFragmentAsDocument(CharSequence value, Parser parser) {
        Document fragment = Jsoup.parse(value.toString(), "", parser);
        Document document = Document.createShell("");
        Iterator nodes = fragment.children().iterator();

        while(nodes.hasNext()) {
            document.body().appendChild((Node)nodes.next());
        }

        return document;
    }

    public class InboundXssException extends RuntimeException{}
}

In applicationContext-security.xml add:

<http>
...
<custom-filter ref="xssFilter" before="FIRST"/>
...

</http>

<beans:bean class="com.example.SimpleInboundXssFilter" id="xssFilter"/>

PG Admin copy & paste to Excel

PG Admin copy & paste to Excel doesn't work well by default, at least on Windows.

Here's how to fix it:

In PG Admin, on the main menu, go to

File > Options... > Query tool > Results grid , and set:

Result copy field separator to Tab

and (optionally) check Copy column names.

In Query window, in Output pane, CTRL+A to select all and CTRL+C to copy

Sample:

PG Admin:

Excel:

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>