add auth support to endpoint server about aquameta HOT 1 CLOSED

The big decision here is "What is a user"? The two options are:

1. use PostgreSQL's role system so that there is one role per user no matter whether they're a customer or a superuser or what have you. These roles inherit from "abstract" roles using role inheritance.
2. Use pg role just for abstract roles that can't login, and then have a secondary table which contains additional metadata like username, email, address, what have you, say a "user" table. A user in this frame is an entry in the customer table, not a role. If the customer table had a single role field, they would only be able to be a member of one role at a time, so this doesn't work. If we had a join table between meta.role and user, this wouldn't work either because we would still need to create a role that inherited the permissions of all the roles they were a member of, per-unique-role-combination.

So, we have to have users be real roles which support login and have passwords. The question then is where does the metadata go? For this, I think it's reasonable to still have a user table with traditional things like username, email, etc. that just foreign_keys to a unique PostgreSQL role, one per user.

Last question: What is the username of a concrete user role? Clear choices include email address or uuid. If we choose email address, then changing your account's email address gets a little hairy, but otherwise it's nice. We can start with email address and then if it becomes a problem, switch to uuid.

So:

CREATE TABLE user (
id uuid primary key ...,
role_id meta.role_id,
name text not null default '',
active boolean not null default false,
activation_code uuid not null default gen-uuid()
);

CREATE ROLE user NOLOGIN;

CREATE FUNCTION register (email text, password text) as $$
/*
1. check for existing role, throw exception if found
2. insert into meta.role (name, login) values ({email}, false);
3. insert into meta.role_inheritance (role_id, inherits_role_id) values (meta.role_id('user'), meta.role_id({email})
4. send email to {email}
*/
$$ language plpgsql;

CREATE FUNCTION register confirm (email text, confirmation_code) as $$
/*
1. check for existing user.  throw exceptions for
  a. non-matching code
  b. already active user
  c. 
2. update role set login=true
3. update user set active=true;
4. send email?
*/
$$ language plpgsql

Good things about this approach:

• we can build a "multifaceted" role manager where users can be a member of multiple abstract roles simultaneously
• it's not web specific at all, it's really just nicely/tightly integrated with postgres's role system, so it has general utility outside web stuff

So this is blocked by #9

from aquameta.