Wednesday, December 30, 2015

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 */

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

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

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:

pkg_add wget
cd /tmp
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 


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