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();
Wednesday, December 30, 2015
UNIX-like row permissions in PostgreSQL
Monday, December 14, 2015
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:
(this takes a few minutes)
Compile duplicity:
(go get some coffee)
Install gpg and generate a key:
(accept the defaults)
Full backup:
Incremental backup:
Restore:
If you're using S3:
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
Saturday, August 29, 2015
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:
Add a Maven dependency for JSoup:
SimpleInboundXssFilter.java:
In applicationContext-security.xml add:
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: */ Mapparams = 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"/>
Saturday, April 18, 2015
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:
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:
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:
Friday, March 13, 2015
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:
In applicationContext-security.xml:
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>
Monday, March 9, 2015
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.
In applicationContext-security.xml, add:
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 { ListgrantedAuthorities = 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>
Subscribe to:
Posts (Atom)