davidad/generate_cert.pl

## generate_cert.pl
create or replace function public.generate_cert(token varchar(32), spkac text, device varchar(32)) returns text as $PERL$

my $token = $_[0];
my $spkac = $_[1];
my $device = $_[2];

use Crypt::OpenSSL::CA;
use File::Slurp;

my $rowset = spi_exec_query('select auth.certificate_tokens.cert_serial is not null as has_serial, auth.certificate_tokens.entry_id as entry_id, auth.uid_email.email as email, auth.uid_email.uid as uid, (main.people.name).given, (main.people.name).particle, (main.people.name).family, (auth.certificate_tokens.token_expires < now()) as expired from auth.uid_email, main.people, auth.certificate_tokens where auth.uid_email.uid = main.people.uid and auth.uid_email.email = auth.certificate_tokens.email and auth.certificate_tokens.token_digest = digest('.quote_literal($token).",'sha512');",1);

if($rowset->{processed}<1) {
    die "Invalid token"
}
my $r = $rowset->{rows}[0];
if($r->{expired}!='f') {
    die "Expired token"
}
if($r->{has_serial}!='f') {
    die "Token already has cert"
}

my $s = spi_exec_query('update auth.certificate_tokens set cert_created = now(), cert_expires = (now() + interval \'1 year\'), device_name = '.quote_literal($device).', cert_serial = nextval(\'auth.cert_serial_seq\') where entry_id = '.quote_literal($r->{entry_id}).' returning to_char((cert_created - interval \'1 day\'),\'YYYYMMDDHH24MISSZ\') as not_before, to_char(cert_expires,\'YYYYMMDDHH24MISSZ\') as not_after, cert_serial',1)->{rows}[0];

$spkac =~ s/%2[bB]/+/g;
$spkac =~ s/%2[fF]/\//g;
$spkac =~ s/[^A-Za-z0-9+\/=]//gm;

my $spkacfile = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x.spkac",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
open FILE, ">$spkacfile";
print FILE $spkac;
close FILE;

my $keyfile = read_file("/etc/pg_certs/ca/davidad.org.ca.key");
my $certfile = read_file("/etc/pg_certs/ca/davidad.org.ca.crt");
my $server_private_key = Crypt::OpenSSL::CA::PrivateKey->parse($keyfile, -password => "SEKRIT");
my $server_cert = Crypt::OpenSSL::CA::X509->parse($certfile);
my $server_pubkey = $server_cert->get_public_key();
my $server_dn = $server_cert->get_subject_DN();

my $client_pubkey = Crypt::OpenSSL::CA::PublicKey->validate_SPKAC($spkac);
my $client_cert = Crypt::OpenSSL::CA::X509->new($client_pubkey);
$client_cert->set_serial(sprintf("%#0.8x",$s->{cert_serial}));
my $client_dn = Crypt::OpenSSL::CA::X509_NAME->new(
    countryName => 'US',
    stateOrProvinceName => 'CA',
    localityName => 'San Francisco',
    organizationName => 'davidad.org',
    OU => 'davidad.org client certificate',
    SN => ((length($r->{particle})>0)?"$r->{particle} $r->{family}":$r->{family}),
    GN => $r->{given},
    CN => $r->{uid},
    emailAddress => $r->{email});
$client_cert->set_subject_DN($client_dn);
$client_cert->set_issuer_DN($server_dn);
$client_cert->set_notAfter($s->{not_after});
$client_cert->set_notBefore($s->{not_before});
$client_cert->set_extension("subjectKeyIdentifier",$client_pubkey->get_openssl_keyid);
$client_cert->set_extension("authorityKeyIdentifier",{keyid=>$server_pubkey->get_openssl_keyid});
my $pem = $client_cert->sign($server_private_key,'sha1');

my $outfile = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x.crt",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
open FILE, ">$outfile";
print FILE $pem;
close FILE;

return $pem;

$PERL$ LANGUAGE 'plperlu' security definer volatile;

## generate_p12.pl
create or replace function public.generate_p12(token varchar(32), device varchar(32)) returns text as $PERL$

my $token = $_[0];
my $device = $_[1];

use Crypt::OpenSSL::CA;
use File::Slurp;
use MIME::Base64;

my $rowset = spi_exec_query('select auth.certificate_tokens.cert_serial is not null as has_serial, auth.certificate_tokens.entry_id as entry_id, auth.uid_email.email as email, auth.uid_email.uid as uid, (main.people.name).given, (main.people.name).particle, (main.people.name).family, (auth.certificate_tokens.token_expires < now()) as expired from auth.uid_email, main.people, auth.certificate_tokens where auth.uid_email.uid = main.people.uid and auth.uid_email.email = auth.certificate_tokens.email and auth.certificate_tokens.token_digest = digest('.quote_literal($token).",'sha512');",1);

if($rowset->{processed}<1) {
    die "Invalid token"
}
my $r = $rowset->{rows}[0];
if($r->{expired}!='f') {
    die "Expired token"
}
if($r->{has_serial}!='f') {
    die "Token already has cert"
}

my $s = spi_exec_query('update auth.certificate_tokens set cert_created = now(), cert_expires = (now() + interval \'1 year\'), device_name = '.quote_literal($device).', cert_serial = nextval(\'auth.cert_serial_seq\') where entry_id = '.quote_literal($r->{entry_id}).' returning to_char((cert_created - interval \'1 day\'),\'YYYYMMDDHH24MISSZ\') as not_before, to_char(cert_expires,\'YYYYMMDDHH24MISSZ\') as not_after, cert_serial',1)->{rows}[0];

my $client_file_base = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
my $client_pem_file = "$client_file_base.pem";
my $client_p12_file = "$client_file_base.p12";

system("openssl genrsa -out $client_pem_file 1024");

my $keyfile = read_file("/etc/pg_certs/ca/davidad.org.ca.key");
my $certfile = read_file("/etc/pg_certs/ca/davidad.org.ca.crt");
my $server_private_key = Crypt::OpenSSL::CA::PrivateKey->parse($keyfile, -password => "SEKRIT");
my $server_cert = Crypt::OpenSSL::CA::X509->parse($certfile);
my $server_pubkey = $server_cert->get_public_key();
my $server_dn = $server_cert->get_subject_DN();

my $client_pem = read_file($client_pem_file);
my $client_pubkey = Crypt::OpenSSL::CA::PrivateKey->parse($client_pem)->get_public_key();
my $client_cert = Crypt::OpenSSL::CA::X509->new($client_pubkey);
$client_cert->set_serial(sprintf("%#0.8x",$s->{cert_serial}));
my $client_dn = Crypt::OpenSSL::CA::X509_NAME->new(
    countryName => 'US',
    stateOrProvinceName => 'CA',
    localityName => 'San Francisco',
    organizationName => 'davidad.org',
    OU => 'davidad.org client certificate',
    SN => ((length($r->{particle})>0)?"$r->{particle} $r->{family}":$r->{family}),
    GN => $r->{given},
    CN => $r->{uid},
    emailAddress => $r->{email});
$client_cert->set_subject_DN($client_dn);
$client_cert->set_issuer_DN($server_dn);
$client_cert->set_notAfter($s->{not_after});
$client_cert->set_notBefore($s->{not_before});
$client_cert->set_extension("subjectKeyIdentifier",$client_pubkey->get_openssl_keyid);
$client_cert->set_extension("authorityKeyIdentifier",{keyid=>$server_pubkey->get_openssl_keyid});
my $pem = $client_cert->sign($server_private_key,'sha1');

my $outfile = "$client_file_base.crt";
open FILE, ">$outfile";
print FILE $pem;
close FILE;

system("openssl pkcs12 -export -nodes -out $client_p12_file -in $outfile -inkey $client_pem_file -passout 'pass:SEKRIT2'");
my $p12 = read_file($client_p12_file);
#unlink $client_pem_file;
#unlink $client_p12_file;

return encode_base64($p12,'');

$PERL$ LANGUAGE 'plperlu' security definer volatile;

## loginlogic.lua
local function loginlogic()
    local args = {}
    if ngx.var.request_method == "GET" then
        args = ngx.req.get_uri_args()
    else
        ngx.req.read_body()
        args = ngx.req.get_post_args()
    end

    if((not args.email or #args.email<3) and (not args.token or #args.token<3)) then
        return no_query
    elseif (args.email) then
        local users = pg.query('/db/pre_send_token',{email = args.email})
        if(not users or #users==0) then
            local res, check = have_token(args.email)
            if(check) then
                return {"<p>Well, that's not an email address, but I do have a record of that token.</p>",res}
            else
                return unknown_email(args.email)
            end
        else
            local user = users[1]
            local output = loginwrap(sending_token(user))
            ngx.print(output)
            ngx.flush(true)
            pg.exec('/db/send_token',{email = args.email})
            return [[<script>
                    $("section.login span.status")[0].innerHTML="has sent";
                    </script>]],true
        end
    elseif (not args.keygen) then
        local res = have_token(args.token)
        return res
    else
        local cert = ''
        if (not args.spkac or #args.spkac==0) then
            local p12s = pg.query('/db/generate_p12',{token=args.token,device=(args.device or '')})
            if(#p12s==0) then
                return failed_cert;
            else
                local p12 = p12s[1].p12
                cert = ngx.decode_base64(p12)
                ngx.header['Content-Type']='application/x-pkcs12'
            end
        else
            local certs = pg.query('/db/generate_cert',{token=args.token,spkac=args.spkac,device=(args.device or '')})
            if(#certs==0) then
                return failed_cert;
            else
                local pem = certs[1].cert
                local b64 = ngx.re.gsub(pem,[=[-[^\n]+-|\n]=],'','io')
                cert = ngx.decode_base64(b64)
                ngx.header['Content-Type']='application/x-x509-user-cert'
            end
        end
        ngx.header['Content-Length']=#cert
        if ngx.var.cookie_into then
            ngx.header['Refresh']='1;url='..ngx.var.cookie_into
        end
        ngx.print(cert)
        ngx.exit(0)
    end
end

## tokens.sql
create or replace function public.generate_token() returns text
    as $$ select trim(trailing '=' from translate(encode(gen_random_bytes(8),'base64'),'ABGIMNOPQSUVZaceilmnosuwz01248+/','CDEFHJKLRTWXYbdfghjkpqrtvxy35679')); $$ language sql;

create or replace function send_token_pl() returns trigger as $PERL$

use Email::Simple;
use Email::Simple::Creator;
use Email::Sender::Simple qw(sendmail);
use Email::Sender::Transport::Sendmail;
use Try::Tiny;
my $transport = Email::Sender::Transport::Sendmail->new();

my $sslport = "";

my $u = spi_exec_query('select generate_token() as token, (main.people.name).nick, (main.people.name).prefix_abb, (main.people.name).given_abb, (main.people.name).particle, (main.people.name).family, (main.people.name).suffix_abb, auth.uid_email.email from main.people, auth.uid_email where main.people.uid=auth.uid_email.uid and auth.uid_email.email='.quote_literal($_TD->{new}{email}).';',1)->{rows}[0];
my $token = $u->{token};
spi_exec_query('update auth.certificate_tokens set token_expires=now() where email='.quote_literal($_TD->{new}{email})."and token_expires>now() and entry_id != ".quote_literal($_TD->{new}{entry_id}).";");

my $email = Email::Simple->create(
    header => [
        To => "$u->{prefix_abb} $u->{given_abb} $u->{family}".$u->{suffix_abbr}." <$u->{email}>",
        From => 'Nemaload Authentication <auth@nemaload.davidad.org>',
        Subject => "Authentication Token (requested)",
        'Content-Type' => 'text/plain; charset="utf-8"'
    ],
    body => <<"EOF"
Hi $u->{nick},

To proceed to the next phase, open this link: https://nemaload.davidad.org$sslport/login?token=$token

Or copy the token:
        $token

Please note that this token expires in 10 minutes, but don't worry - you can always request another:
        https://nemaload.davidad.org$sslport/login?email=$u->{email}

Cheers,
- The Nemaload Website
EOF
);

try {
    sendmail($email,{transport=>$transport});
    spi_exec_query('update auth.certificate_tokens set token_digest = digest('.quote_literal($token).",'sha512'), token_sent=clock_timestamp() where entry_id=".quote_literal($_TD->{new}{entry_id}).";");
    return undef;
} catch {
    elog ERROR, "sending failed: $_";
    return "SKIP";
}

$PERL$ LANGUAGE 'plperlu' volatile;

create trigger send_token_trigger
    after insert on auth.certificate_tokens
    for each row execute procedure send_token_pl();

create or replace function public.send_token(varchar(256)) returns table (email varchar(256), expires timestamptz, nick varchar(32), formal_address text)
    as $$
    declare
        entry integer;
        expires timestamptz;
    begin
        begin
            insert into certificate_tokens (email) values ($1) returning entry_id, token_expires into entry, expires;
            return query select $1 as email, expires, (main.people.name).nick, formal_address(main.people.name) as formal_address from people, uid_email where people.uid=uid_email.uid and uid_email.email=$1;
        exception when foreign_key_violation or check_violation then
            insert into unknown_emails (email) values ($1);
            return;
        end;
    end;
    $$ language plpgsql security definer set search_path = auth, main, public, pg_temp;

create or replace function public.pre_send_token(varchar(256)) returns table (email varchar(256), nick varchar(32), formal_address text) as $$
    begin
        return query select $1 as email, (main.people.name).nick, formal_address(main.people.name) as formal_address from people, uid_email where people.uid=uid_email.uid and uid_email.email=$1;
        if not found then
            begin
                insert into unknown_emails (email) values ($1);
            exception when not_null_violation then
                null;
            end;
        end if;
        return;
    end
    $$ language plpgsql security definer set search_path = auth, main, public, pg_temp;
	create or replace function public.generate_cert(token varchar(32), spkac text, device varchar(32)) returns text as $PERL$

	my $token = $_[0];
	my $spkac = $_[1];
	my $device = $_[2];

	use Crypt::OpenSSL::CA;
	use File::Slurp;

	my $rowset = spi_exec_query('select auth.certificate_tokens.cert_serial is not null as has_serial, auth.certificate_tokens.entry_id as entry_id, auth.uid_email.email as email, auth.uid_email.uid as uid, (main.people.name).given, (main.people.name).particle, (main.people.name).family, (auth.certificate_tokens.token_expires < now()) as expired from auth.uid_email, main.people, auth.certificate_tokens where auth.uid_email.uid = main.people.uid and auth.uid_email.email = auth.certificate_tokens.email and auth.certificate_tokens.token_digest = digest('.quote_literal($token).",'sha512');",1);

	if($rowset->{processed}<1) {
	die "Invalid token"
	}
	my $r = $rowset->{rows}[0];
	if($r->{expired}!='f') {
	die "Expired token"
	}
	if($r->{has_serial}!='f') {
	die "Token already has cert"
	}

	my $s = spi_exec_query('update auth.certificate_tokens set cert_created = now(), cert_expires = (now() + interval \'1 year\'), device_name = '.quote_literal($device).', cert_serial = nextval(\'auth.cert_serial_seq\') where entry_id = '.quote_literal($r->{entry_id}).' returning to_char((cert_created - interval \'1 day\'),\'YYYYMMDDHH24MISSZ\') as not_before, to_char(cert_expires,\'YYYYMMDDHH24MISSZ\') as not_after, cert_serial',1)->{rows}[0];

	$spkac =~ s/%2[bB]/+/g;
	$spkac =~ s/%2[fF]/\//g;
	$spkac =~ s/[^A-Za-z0-9+\/=]//gm;

	my $spkacfile = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x.spkac",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
	open FILE, ">$spkacfile";
	print FILE $spkac;
	close FILE;

	my $keyfile = read_file("/etc/pg_certs/ca/davidad.org.ca.key");
	my $certfile = read_file("/etc/pg_certs/ca/davidad.org.ca.crt");
	my $server_private_key = Crypt::OpenSSL::CA::PrivateKey->parse($keyfile, -password => "SEKRIT");
	my $server_cert = Crypt::OpenSSL::CA::X509->parse($certfile);
	my $server_pubkey = $server_cert->get_public_key();
	my $server_dn = $server_cert->get_subject_DN();

	my $client_pubkey = Crypt::OpenSSL::CA::PublicKey->validate_SPKAC($spkac);
	my $client_cert = Crypt::OpenSSL::CA::X509->new($client_pubkey);
	$client_cert->set_serial(sprintf("%#0.8x",$s->{cert_serial}));
	my $client_dn = Crypt::OpenSSL::CA::X509_NAME->new(
	countryName => 'US',
	stateOrProvinceName => 'CA',
	localityName => 'San Francisco',
	organizationName => 'davidad.org',
	OU => 'davidad.org client certificate',
	SN => ((length($r->{particle})>0)?"$r->{particle} $r->{family}":$r->{family}),
	GN => $r->{given},
	CN => $r->{uid},
	emailAddress => $r->{email});
	$client_cert->set_subject_DN($client_dn);
	$client_cert->set_issuer_DN($server_dn);
	$client_cert->set_notAfter($s->{not_after});
	$client_cert->set_notBefore($s->{not_before});
	$client_cert->set_extension("subjectKeyIdentifier",$client_pubkey->get_openssl_keyid);
	$client_cert->set_extension("authorityKeyIdentifier",{keyid=>$server_pubkey->get_openssl_keyid});
	my $pem = $client_cert->sign($server_private_key,'sha1');

	my $outfile = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x.crt",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
	open FILE, ">$outfile";
	print FILE $pem;
	close FILE;

	return $pem;

	$PERL$ LANGUAGE 'plperlu' security definer volatile;
	create or replace function public.generate_p12(token varchar(32), device varchar(32)) returns text as $PERL$

	my $token = $_[0];
	my $device = $_[1];

	use Crypt::OpenSSL::CA;
	use File::Slurp;
	use MIME::Base64;

	my $rowset = spi_exec_query('select auth.certificate_tokens.cert_serial is not null as has_serial, auth.certificate_tokens.entry_id as entry_id, auth.uid_email.email as email, auth.uid_email.uid as uid, (main.people.name).given, (main.people.name).particle, (main.people.name).family, (auth.certificate_tokens.token_expires < now()) as expired from auth.uid_email, main.people, auth.certificate_tokens where auth.uid_email.uid = main.people.uid and auth.uid_email.email = auth.certificate_tokens.email and auth.certificate_tokens.token_digest = digest('.quote_literal($token).",'sha512');",1);

	if($rowset->{processed}<1) {
	die "Invalid token"
	}
	my $r = $rowset->{rows}[0];
	if($r->{expired}!='f') {
	die "Expired token"
	}
	if($r->{has_serial}!='f') {
	die "Token already has cert"
	}

	my $s = spi_exec_query('update auth.certificate_tokens set cert_created = now(), cert_expires = (now() + interval \'1 year\'), device_name = '.quote_literal($device).', cert_serial = nextval(\'auth.cert_serial_seq\') where entry_id = '.quote_literal($r->{entry_id}).' returning to_char((cert_created - interval \'1 day\'),\'YYYYMMDDHH24MISSZ\') as not_before, to_char(cert_expires,\'YYYYMMDDHH24MISSZ\') as not_after, cert_serial',1)->{rows}[0];

	my $client_file_base = sprintf("/etc/pg_certs/clients/client_%s_%s_%0.8x",($r->{email} =~ s/[^\w]+/_/rg),($device =~ s/[^\w]+/_/rg),$s->{cert_serial});
	my $client_pem_file = "$client_file_base.pem";
	my $client_p12_file = "$client_file_base.p12";

	system("openssl genrsa -out $client_pem_file 1024");

	my $keyfile = read_file("/etc/pg_certs/ca/davidad.org.ca.key");
	my $certfile = read_file("/etc/pg_certs/ca/davidad.org.ca.crt");
	my $server_private_key = Crypt::OpenSSL::CA::PrivateKey->parse($keyfile, -password => "SEKRIT");
	my $server_cert = Crypt::OpenSSL::CA::X509->parse($certfile);
	my $server_pubkey = $server_cert->get_public_key();
	my $server_dn = $server_cert->get_subject_DN();

	my $client_pem = read_file($client_pem_file);
	my $client_pubkey = Crypt::OpenSSL::CA::PrivateKey->parse($client_pem)->get_public_key();
	my $client_cert = Crypt::OpenSSL::CA::X509->new($client_pubkey);
	$client_cert->set_serial(sprintf("%#0.8x",$s->{cert_serial}));
	my $client_dn = Crypt::OpenSSL::CA::X509_NAME->new(
	countryName => 'US',
	stateOrProvinceName => 'CA',
	localityName => 'San Francisco',
	organizationName => 'davidad.org',
	OU => 'davidad.org client certificate',
	SN => ((length($r->{particle})>0)?"$r->{particle} $r->{family}":$r->{family}),
	GN => $r->{given},
	CN => $r->{uid},
	emailAddress => $r->{email});
	$client_cert->set_subject_DN($client_dn);
	$client_cert->set_issuer_DN($server_dn);
	$client_cert->set_notAfter($s->{not_after});
	$client_cert->set_notBefore($s->{not_before});
	$client_cert->set_extension("subjectKeyIdentifier",$client_pubkey->get_openssl_keyid);
	$client_cert->set_extension("authorityKeyIdentifier",{keyid=>$server_pubkey->get_openssl_keyid});
	my $pem = $client_cert->sign($server_private_key,'sha1');

	my $outfile = "$client_file_base.crt";
	open FILE, ">$outfile";
	print FILE $pem;
	close FILE;

	system("openssl pkcs12 -export -nodes -out $client_p12_file -in $outfile -inkey $client_pem_file -passout 'pass:SEKRIT2'");
	my $p12 = read_file($client_p12_file);
	#unlink $client_pem_file;
	#unlink $client_p12_file;

	return encode_base64($p12,'');

	$PERL$ LANGUAGE 'plperlu' security definer volatile;
	local function loginlogic()
	local args = {}
	if ngx.var.request_method == "GET" then
	args = ngx.req.get_uri_args()
	else
	ngx.req.read_body()
	args = ngx.req.get_post_args()
	end

	if((not args.email or #args.email<3) and (not args.token or #args.token<3)) then
	return no_query
	elseif (args.email) then
	local users = pg.query('/db/pre_send_token',{email = args.email})
	if(not users or #users==0) then
	local res, check = have_token(args.email)
	if(check) then
	return {"<p>Well, that's not an email address, but I do have a record of that token.</p>",res}
	else
	return unknown_email(args.email)
	end
	else
	local user = users[1]
	local output = loginwrap(sending_token(user))
	ngx.print(output)
	ngx.flush(true)
	pg.exec('/db/send_token',{email = args.email})
	return [[<script>
	$("section.login span.status")[0].innerHTML="has sent";
	</script>]],true
	end
	elseif (not args.keygen) then
	local res = have_token(args.token)
	return res
	else
	local cert = ''
	if (not args.spkac or #args.spkac==0) then
	local p12s = pg.query('/db/generate_p12',{token=args.token,device=(args.device or '')})
	if(#p12s==0) then
	return failed_cert;
	else
	local p12 = p12s[1].p12
	cert = ngx.decode_base64(p12)
	ngx.header['Content-Type']='application/x-pkcs12'
	end
	else
	local certs = pg.query('/db/generate_cert',{token=args.token,spkac=args.spkac,device=(args.device or '')})
	if(#certs==0) then
	return failed_cert;
	else
	local pem = certs[1].cert
	local b64 = ngx.re.gsub(pem,[=[-[^\n]+-\|\n]=],'','io')
	cert = ngx.decode_base64(b64)
	ngx.header['Content-Type']='application/x-x509-user-cert'
	end
	end
	ngx.header['Content-Length']=#cert
	if ngx.var.cookie_into then
	ngx.header['Refresh']='1;url='..ngx.var.cookie_into
	end
	ngx.print(cert)
	ngx.exit(0)
	end
	end
	create or replace function public.generate_token() returns text
	as $$ select trim(trailing '=' from translate(encode(gen_random_bytes(8),'base64'),'ABGIMNOPQSUVZaceilmnosuwz01248+/','CDEFHJKLRTWXYbdfghjkpqrtvxy35679')); $$ language sql;

	create or replace function send_token_pl() returns trigger as $PERL$

	use Email::Simple;
	use Email::Simple::Creator;
	use Email::Sender::Simple qw(sendmail);
	use Email::Sender::Transport::Sendmail;
	use Try::Tiny;
	my $transport = Email::Sender::Transport::Sendmail->new();

	my $sslport = "";

	my $u = spi_exec_query('select generate_token() as token, (main.people.name).nick, (main.people.name).prefix_abb, (main.people.name).given_abb, (main.people.name).particle, (main.people.name).family, (main.people.name).suffix_abb, auth.uid_email.email from main.people, auth.uid_email where main.people.uid=auth.uid_email.uid and auth.uid_email.email='.quote_literal($_TD->{new}{email}).';',1)->{rows}[0];
	my $token = $u->{token};
	spi_exec_query('update auth.certificate_tokens set token_expires=now() where email='.quote_literal($_TD->{new}{email})."and token_expires>now() and entry_id != ".quote_literal($_TD->{new}{entry_id}).";");

	my $email = Email::Simple->create(
	header => [
	To => "$u->{prefix_abb} $u->{given_abb} $u->{family}".$u->{suffix_abbr}." <$u->{email}>",
	From => 'Nemaload Authentication <auth@nemaload.davidad.org>',
	Subject => "Authentication Token (requested)",
	'Content-Type' => 'text/plain; charset="utf-8"'
	],
	body => <<"EOF"
	Hi $u->{nick},

	To proceed to the next phase, open this link: https://nemaload.davidad.org$sslport/login?token=$token

	Or copy the token:
	$token

	Please note that this token expires in 10 minutes, but don't worry - you can always request another:
	https://nemaload.davidad.org$sslport/login?email=$u->{email}

	Cheers,
	- The Nemaload Website
	EOF
	);

	try {
	sendmail($email,{transport=>$transport});
	spi_exec_query('update auth.certificate_tokens set token_digest = digest('.quote_literal($token).",'sha512'), token_sent=clock_timestamp() where entry_id=".quote_literal($_TD->{new}{entry_id}).";");
	return undef;
	} catch {
	elog ERROR, "sending failed: $_";
	return "SKIP";
	}

	$PERL$ LANGUAGE 'plperlu' volatile;

	create trigger send_token_trigger
	after insert on auth.certificate_tokens
	for each row execute procedure send_token_pl();

	create or replace function public.send_token(varchar(256)) returns table (email varchar(256), expires timestamptz, nick varchar(32), formal_address text)
	as $$
	declare
	entry integer;
	expires timestamptz;
	begin
	begin
	insert into certificate_tokens (email) values ($1) returning entry_id, token_expires into entry, expires;
	return query select $1 as email, expires, (main.people.name).nick, formal_address(main.people.name) as formal_address from people, uid_email where people.uid=uid_email.uid and uid_email.email=$1;
	exception when foreign_key_violation or check_violation then
	insert into unknown_emails (email) values ($1);
	return;
	end;
	end;
	$$ language plpgsql security definer set search_path = auth, main, public, pg_temp;

	create or replace function public.pre_send_token(varchar(256)) returns table (email varchar(256), nick varchar(32), formal_address text) as $$
	begin
	return query select $1 as email, (main.people.name).nick, formal_address(main.people.name) as formal_address from people, uid_email where people.uid=uid_email.uid and uid_email.email=$1;
	if not found then
	begin
	insert into unknown_emails (email) values ($1);
	exception when not_null_violation then
	null;
	end;
	end if;
	return;
	end
	$$ language plpgsql security definer set search_path = auth, main, public, pg_temp;