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