------------------------------------------------------------------------
-- Copyright 2006 (Y) Yellowbank
--
-- https://www.yellowbank.com/
--
-- This file is part of pam_authtok.
--
-- pam_authtok is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or (at
-- your option) any later version.
--
-- pam_authtok is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- General Public License for more details.
--
-- A copy of the GNU General Public License is included as
-- /doc/GPL.txt.  You can also recieve a copy from the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA
------------------------------------------------------------------------


------------------------------------------------------------------------
SET search_path = iddb, pgcrypto;
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE TABLE person (
  person_id
    y_octet_16
    NOT NULL
    DEFAULT encode( y_uuid_generate_random(), 'hex' )::y_octet_16
    PRIMARY KEY,

  name_last
    TEXT
    NOT NULL,

  name_first
    TEXT
    NOT NULL,

  name_middle
    TEXT,

  honorific
    TEXT,

  suffix
    TEXT,

  sex
    CHAR(1)
    CHECK( sex in ('M','F') ),

  birthday
    DATE,

  md5
    BYTEA
    NOT NULL
);
-- COMMENT ON COLUMN person.username IS
--   'Unique login username';

COMMENT ON COLUMN person.honorific IS
  E'e.g. \'Dr.\', \'Ms.\'';
--  'e.g. \'Dr.\'\, \'Ms.\'';

COMMENT ON COLUMN person.suffix IS
  E'Appended to last name with NO SPACE (so add a space if you like).  E.G. \' III\'\, \'\, Esq.\'';

CREATE INDEX
  person__name_last_idx
ON
  person( name_last );

CREATE INDEX
  person__md5_idx
ON
  person( md5 );

CREATE UNIQUE INDEX
  person__last_first_name_idx
ON
  person( name_last, name_first );
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE VIEW
  person_v
AS
  SELECT
    name_last,
    name_first,
    name_middle,
    honorific,
    suffix,
    sex,
    birthday
  FROM
    person;

CREATE RULE
  person_v_insert
AS
ON INSERT TO
  person_v
DO INSTEAD
(
  INSERT INTO person ( name_last,
                       name_first,
                       name_middle,
                       honorific,
                       suffix,
                       sex,
                       birthday,
                       md5 )
  VALUES ( new.name_last,
           new.name_first,
           new.name_middle,
           new.honorific,
           new.suffix,
           new.sex,
           new.birthday,
           digest( new.name_last ||
                   new.name_first ||
                   new.name_middle ||
                   new.honorific ||
                   new.suffix ||
                   new.sex ||
                   new.birthday,
                   'md5' ) );
);
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE TABLE account (
  username
    VARCHAR(8)
    UNIQUE
    CHECK( username ~ '^[_a-z]{1}[-_a-z0-9]{0,7}$' ),

  created
    TIMESTAMP WITH TIME ZONE
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP,

  crypt_des
    CHAR(13)
    NOT NULL,

  crypt_xdes
    CHAR(20)
    NOT NULL,

  crypt_md5
    CHAR(34)
    NOT NULL,

  crypt_bf
    CHAR(60)
    NOT NULL,

  -- 128 bits
  pass_md5
    CHAR(32)
    NOT NULL,

  -- 160 bits
  pass_sha1
    CHAR(40)
    NOT NULL,

  lanman
    CHAR(32)
    NOT NULL,
  
  ntlm
    CHAR(32)
    NOT NULL,

  -- encrypted password
  pass_enc
    BYTEA
    NOT NULL
);

COMMENT ON COLUMN
  account.pass_md5
IS
  'MD5 hash of password, encoded in hexadecimal';

COMMENT ON COLUMN
  account.pass_sha1
IS
  'SHA1 hash of password, encoded in hexadecimal';

COMMENT ON COLUMN
  account.crypt_des
IS
  'Original UNIX crypt.  Max length 8';

COMMENT ON COLUMN
  account.crypt_xdes
IS
  'Extended DES.  Max length 8';

COMMENT ON COLUMN
  account.crypt_md5
IS
  'MD5 based crypt.  Max length unlimited';

COMMENT ON COLUMN
  account.crypt_bf
IS
  'Blowfish, variant 2a.  Max length 72';

GRANT INSERT ON TABLE
  account
TO
  public;
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE TABLE mhcids (
  person
    y_octet_16
    REFERENCES person( person_id )
    PRIMARY KEY,

  unix_uid
    INTEGER
    UNIQUE
    CHECK( unix_uid > 200 AND unix_uid < 65535 ),

  username
    VARCHAR(8)
    UNIQUE
    REFERENCES account( username ),

  mhc_id
    CHAR(9)
    UNIQUE
    CHECK( mhc_id ~ '^[a-z0-9]{9}$' ),

  access_number
    CHAR(9)
    UNIQUE
    CHECK( access_number ~ '^[a-z0-9]{9}$' ),

  library_code
    CHAR(15)
    UNIQUE
    CHECK( library_code ~ '^[0-9]{15}$' ),

  datatel_id
    CHAR(7)
    UNIQUE
    CHECK( datatel_id ~ '^[0-9]{7}$' ),

  social_security
    CHAR(9)
    UNIQUE
    CHECK( datatel_id ~ '^[0-9]{9}$' )
);
------------------------------------------------------------------------


------------------------------------------------------------------------
-- Each record indicates what the values were prior to the moment they
-- were updated.
CREATE TABLE account_log (
  username
    VARCHAR(8)
    NOT NULL,

  created
    TIMESTAMP WITH TIME ZONE
    NOT NULL,

  updated
    TIMESTAMP WITH TIME ZONE
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP,

  crypt_des
    CHAR(13)
    NOT NULL,

  crypt_xdes
    CHAR(20)
    NOT NULL,

  crypt_md5
    CHAR(34)
    NOT NULL,

  crypt_bf
    CHAR(60)
    NOT NULL,

  -- 128 bits
  pass_md5
    CHAR(32)
    NOT NULL,

  -- 160 bits
  pass_sha1
    CHAR(40)
    NOT NULL,

  lanman
    CHAR(32)
    NOT NULL,
  
  ntlm
    CHAR(32)
    NOT NULL,

  -- encrypted password
  pass_enc
    BYTEA
    NOT NULL
);

CREATE UNIQUE INDEX
  account_log___username_created_ndx
ON
  account_log( username, created );

CREATE INDEX
  account_log__updated_ndx
ON
  account_log( updated );
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE TABLE username_change_log (
  old_username
    VARCHAR(8)
    NOT NULL,

  new_username
    VARCHAR(8)
    NOT NULL,

  changed
    TIMESTAMP WITH TIME ZONE
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX
  username_change_log__idx
ON
  username_change_log( old_username, changed );
------------------------------------------------------------------------


------------------------------------------------------------------------
-- This is a delete rule because we update password entries
-- by first deleting them, and then inserting new ones.
--
CREATE RULE account_delete_rule AS
ON DELETE TO account
DO
(
  INSERT INTO account_log (
    username,
    created,
    crypt_des,
    crypt_xdes,
    crypt_md5,
    crypt_bf,
    pass_md5,
    pass_sha1,
    lanman,
    ntlm,
    pass_enc )
  VALUES (
    old.username,
    old.created,
    old.crypt_des,
    old.crypt_xdes,
    old.crypt_md5,
    old.crypt_bf,
    old.pass_md5,
    old.pass_sha1,
    old.lanman,
    old.ntlm,
    old.pass_enc );  
);

CREATE RULE account__lock_user_insert
AS ON INSERT TO account
WHERE
  CURRENT_USER != 'iddb'
AND
  new.username != CURRENT_USER
DO INSTEAD nothing;

CREATE RULE account__lock_user_update
AS ON UPDATE TO account
WHERE
  CURRENT_USER != 'iddb'
AND
  old.username != CURRENT_USER
DO INSTEAD nothing;

CREATE RULE account__lock_user_delete
AS ON DELETE TO account
WHERE
  CURRENT_USER != 'iddb'
AND
  old.username != CURRENT_USER
DO INSTEAD nothing;
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE VIEW
  account_insert
AS
  SELECT
    username,
    ''::text AS account
  FROM
    account;

GRANT INSERT ON
  account_insert
TO
  public;
------------------------------------------------------------------------


------------------------------------------------------------------------
-- CREATE OR REPLACE FUNCTION sha1(bytea)
-- RETURNS text AS $$
--   SELECT encode(digest($1, 'sha1'), 'hex')
-- $$ LANGUAGE SQL STRICT IMMUTABLE;
------------------------------------------------------------------------


------------------------------------------------------------------------
-- Put this function somewhere private to prevent unauthorized
-- access to encrypted passwords.

CREATE OR REPLACE FUNCTION pass_unenc( bytea )
RETURNS text AS $$
  SELECT pgp_pub_decrypt( $1, dearmor( '
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

lQHhBERqGUYRBAC7bt8WW1hiZvCs2jLRWeF9tGdPzzL2DizR7/QyJpLs4Sv2fk1k
lnasY24OsafgECWgZhMu+rdxcLrN/WoDfc3f823fFbaun9QQpcqWahUkGSdwOgg/
b6UoJ2PJnAMR9oTAOtwPUhwYxJgc0/XHADh+hki5AtSygO0yWXxjwhJEewCgzk5n
2TVwMKIPweTIHpZypZ9EHUUD/jCyP5hdL1W5R78OLh3BAoZiOyQMwhVSNRtllmUL
WRIIrWFjCi/ec+G8hC4cBYOYfuUAagTq9NN8PZGuMUxO8pB6ldPc4+DH9uRyDFiS
ETsmtiJy/9CpvoDl31aHxLkQgsFc3TAHks4wqPcStZagBjlWFgZzY5+patFaVmB9
91YdBAC5Gk8n4VzlbK1Ncw8qQm/G/7PfwNdCU3uFK7xw0LwO/9Jtxgu7limh85wo
2UZ4CSku7ymkDrt+DbA7ubL7hlRezyOyRgg3qy3vTnmgsLjaZRsQgvCpdRWP5Rmo
B7fPA1QkSwLyX1f7Rg2pOWq/YQOLUxwcs29UwOyPLYvO/gB9UP4DAwL0Cha/7ysH
UWD7EySqw+S1zwpFr+iFpJ6ofA1l/GZIBmZ9LskkWUhk+zYmdOZxU6Yd22+Cvg29
PbrO5rQlaWQgZGIgKHRlc3RpbmcpIDxpZGRiQHllbGxvd2JhbmsuY29tPohgBBMR
AgAgBQJEahlGAhsDBgsJCAcDAgQVAggDBBYCAwECHgECF4AACgkQwGrv/SO8VK2P
EQCggMQXddk/+3F1SroYNwxPp+Po2bYAnj0pamoG2uTWVfKntwUFGyh76Z7HnQJj
BERqGUoQCADVZUF2SX/snUvADwFjmM68sHWT1CKc1QYA9uogGWG+3tg/+05L31+2
0cZFJk1hVEtpfpAAlOVkMT0sm9Sgry8QzLYM1a+9wsTlNj2sJ21Zbz5QPPClGbVc
BphqRRXscooyeREUZuXDwRQGydy51+51s6P2HFXlKN4ISY2zMy61rIiYcCX8BJtW
2qIqF12usRs7MuNsgqzXWc0BrTrmj2hMUQbiPREqKJenMfMg0IHlMv7gjSJ2bmLi
LUw7PqJfyIdO53CNqtrZiW+kkTP0TLdsIFkIEcmaXCez2A4Zsy6/hro2R3HBamCi
sF5+BkbptaoA0zEGOmGzhFunHutFELqPAAMFB/4v9RliFLY657f1ouqBOm54WqmX
SYS/VyAf4b8pRacxEujRPa9+8/oG3FL7Zrdv+Z5nzuSw5PvXG4SSjojuTfp2JQAL
Jn4yuAjSoB3YNd8mxq8mXR8ZdYrvLrigZR5crnIPRsT/am/+3wkNBYcsRqfXyJdv
Ra2jpPLD5mcVW1bi/4U3HOs02Q4hM/RqVb1EqFvd6ECxxigFTI2p7VWpXb7dcV0g
B3hA7yZz/SkwRXdwnXDgoPWoRi/H9Fl+ynQld8Um3gdawV4bHIItx9xUx8Muahwt
jIGHGSMLvp/+J+c/VNV4OP4Xd+rPcojRKdXUmVl3lnv9ffr3LrzPnndvnfuT/gMD
AvQKFr/vKwdRYGpFTnz+ODJKkGvWRlRXemHJ5tBFshZKxaR53tqhJpjV2t6VigjU
h3u2LL+VnHD26KY7Gk7Q8wZLqISn/xFkqqWOrZ4TFvrXuWOISQQYEQIACQUCRGoZ
SgIbDAAKCRDAau/9I7xUrcRYAJ96Vw6m9K6d/ym5XS8Qu1Ax0ccALACgxWsDFEke
07owOjmWbNEbR94+6yA=
=H1E5
-----END PGP PRIVATE KEY BLOCK-----
'), 'ycvfa' )
$$ LANGUAGE SQL STRICT IMMUTABLE;
------------------------------------------------------------------------


------------------------------------------------------------------------
-- insert or update strategy
-- * insert new/updated record into temporary table
-- * delete from real table where id matches
-- * insert temporary table records into real table

CREATE TABLE account_temp ( LIKE account );

CREATE RULE account_insert_rule AS
ON INSERT TO account_insert
DO INSTEAD
(
  INSERT INTO account_temp (
    username,
    crypt_des,
    crypt_xdes,
    crypt_md5,
    crypt_bf,
    pass_md5,
    pass_sha1,
    lanman,
    ntlm,
    pass_enc )
  VALUES (
    new.username,
    crypt( new.account, gen_salt('des') ),
    crypt( new.account, gen_salt('xdes') ),
    crypt( new.account, gen_salt('md5') ),
    crypt( new.account, gen_salt('bf') ),
    encode( digest( new.account, 'md5' ), 'hex' ),
    encode( digest( new.account, 'sha1' ), 'hex' ),
    y_lm( new.account ),
    y_ntlm( new.account ),
    pgp_pub_encrypt( new.account, dearmor( '
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

mQGiBERqGUYRBAC7bt8WW1hiZvCs2jLRWeF9tGdPzzL2DizR7/QyJpLs4Sv2fk1k
lnasY24OsafgECWgZhMu+rdxcLrN/WoDfc3f823fFbaun9QQpcqWahUkGSdwOgg/
b6UoJ2PJnAMR9oTAOtwPUhwYxJgc0/XHADh+hki5AtSygO0yWXxjwhJEewCgzk5n
2TVwMKIPweTIHpZypZ9EHUUD/jCyP5hdL1W5R78OLh3BAoZiOyQMwhVSNRtllmUL
WRIIrWFjCi/ec+G8hC4cBYOYfuUAagTq9NN8PZGuMUxO8pB6ldPc4+DH9uRyDFiS
ETsmtiJy/9CpvoDl31aHxLkQgsFc3TAHks4wqPcStZagBjlWFgZzY5+patFaVmB9
91YdBAC5Gk8n4VzlbK1Ncw8qQm/G/7PfwNdCU3uFK7xw0LwO/9Jtxgu7limh85wo
2UZ4CSku7ymkDrt+DbA7ubL7hlRezyOyRgg3qy3vTnmgsLjaZRsQgvCpdRWP5Rmo
B7fPA1QkSwLyX1f7Rg2pOWq/YQOLUxwcs29UwOyPLYvO/gB9ULQlaWQgZGIgKHRl
c3RpbmcpIDxpZGRiQHllbGxvd2JhbmsuY29tPohgBBMRAgAgBQJEahlGAhsDBgsJ
CAcDAgQVAggDBBYCAwECHgECF4AACgkQwGrv/SO8VK2PEQCggMQXddk/+3F1SroY
NwxPp+Po2bYAnj0pamoG2uTWVfKntwUFGyh76Z7HuQINBERqGUoQCADVZUF2SX/s
nUvADwFjmM68sHWT1CKc1QYA9uogGWG+3tg/+05L31+20cZFJk1hVEtpfpAAlOVk
MT0sm9Sgry8QzLYM1a+9wsTlNj2sJ21Zbz5QPPClGbVcBphqRRXscooyeREUZuXD
wRQGydy51+51s6P2HFXlKN4ISY2zMy61rIiYcCX8BJtW2qIqF12usRs7MuNsgqzX
Wc0BrTrmj2hMUQbiPREqKJenMfMg0IHlMv7gjSJ2bmLiLUw7PqJfyIdO53CNqtrZ
iW+kkTP0TLdsIFkIEcmaXCez2A4Zsy6/hro2R3HBamCisF5+BkbptaoA0zEGOmGz
hFunHutFELqPAAMFB/4v9RliFLY657f1ouqBOm54WqmXSYS/VyAf4b8pRacxEujR
Pa9+8/oG3FL7Zrdv+Z5nzuSw5PvXG4SSjojuTfp2JQALJn4yuAjSoB3YNd8mxq8m
XR8ZdYrvLrigZR5crnIPRsT/am/+3wkNBYcsRqfXyJdvRa2jpPLD5mcVW1bi/4U3
HOs02Q4hM/RqVb1EqFvd6ECxxigFTI2p7VWpXb7dcV0gB3hA7yZz/SkwRXdwnXDg
oPWoRi/H9Fl+ynQld8Um3gdawV4bHIItx9xUx8MuahwtjIGHGSMLvp/+J+c/VNV4
OP4Xd+rPcojRKdXUmVl3lnv9ffr3LrzPnndvnfuTiEkEGBECAAkFAkRqGUoCGwwA
CgkQwGrv/SO8VK3EWACePCD19Fiv7NcjWGSgS4jgMY49qLUAoKaGvfRoM19AuFpy
YtpANA/XnOZy
=dy1H
-----END PGP PUBLIC KEY BLOCK-----
')));

  DELETE FROM
    account
  WHERE
    account.username = new.username;

  INSERT INTO
    account
  SELECT
    *
  FROM
    account_temp
  WHERE
    username = new.username;

  DELETE FROM
    account_temp
  WHERE
    username = new.username;
);
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE VIEW
  account_change_username
AS
  SELECT
    username AS old_username,
    ''::text AS new_username
  FROM
    account;

GRANT INSERT ON
  account_change_username
TO
  public;
------------------------------------------------------------------------


------------------------------------------------------------------------
-- CREATE RULE account_change_username_rule AS
-- ON INSERT TO account_change_username
-- DO INSTEAD
-- (
--   UPDATE
--     account
--   SET
--     username = new.new_username
--   WHERE
--     username = new.username;

--   INSERT INTO
--     username_change_log
--   VALUES
-- );
------------------------------------------------------------------------


------------------------------------------------------------------------
CREATE TABLE keys (
  fingerprint
    CHAR(40)
    NOT NULL
    PRIMARY KEY,
  name
    TEXT
    UNIQUE
    NOT NULL,
  public_key
    TEXT
    NOT NULL,
  secret_key
    TEXT
    NOT NULL
);

COMMENT ON COLUMN
  keys.fingerprint
IS
  'hex encoded sha1 fingerprint of public key';

COMMENT ON COLUMN
  keys.name
IS
  'common name';

COMMENT ON COLUMN
  keys.public_key
IS
  'ascii armored public key';

COMMENT ON COLUMN
  keys.secret_key
IS
  'ascii armored secret key';
------------------------------------------------------------------------


------------------------------------------------------------------------
INSERT INTO
  keys ( fingerprint, name, public_key, secret_key )
VALUES ( '3439ca00aeb9833c99f5b841c06aeffd23bc54ad',
         'iddb@yellowbank.com', '
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

mQGiBERqGUYRBAC7bt8WW1hiZvCs2jLRWeF9tGdPzzL2DizR7/QyJpLs4Sv2fk1k
lnasY24OsafgECWgZhMu+rdxcLrN/WoDfc3f823fFbaun9QQpcqWahUkGSdwOgg/
b6UoJ2PJnAMR9oTAOtwPUhwYxJgc0/XHADh+hki5AtSygO0yWXxjwhJEewCgzk5n
2TVwMKIPweTIHpZypZ9EHUUD/jCyP5hdL1W5R78OLh3BAoZiOyQMwhVSNRtllmUL
WRIIrWFjCi/ec+G8hC4cBYOYfuUAagTq9NN8PZGuMUxO8pB6ldPc4+DH9uRyDFiS
ETsmtiJy/9CpvoDl31aHxLkQgsFc3TAHks4wqPcStZagBjlWFgZzY5+patFaVmB9
91YdBAC5Gk8n4VzlbK1Ncw8qQm/G/7PfwNdCU3uFK7xw0LwO/9Jtxgu7limh85wo
2UZ4CSku7ymkDrt+DbA7ubL7hlRezyOyRgg3qy3vTnmgsLjaZRsQgvCpdRWP5Rmo
B7fPA1QkSwLyX1f7Rg2pOWq/YQOLUxwcs29UwOyPLYvO/gB9ULQlaWQgZGIgKHRl
c3RpbmcpIDxpZGRiQHllbGxvd2JhbmsuY29tPohgBBMRAgAgBQJEahlGAhsDBgsJ
CAcDAgQVAggDBBYCAwECHgECF4AACgkQwGrv/SO8VK2PEQCggMQXddk/+3F1SroY
NwxPp+Po2bYAnj0pamoG2uTWVfKntwUFGyh76Z7HuQINBERqGUoQCADVZUF2SX/s
nUvADwFjmM68sHWT1CKc1QYA9uogGWG+3tg/+05L31+20cZFJk1hVEtpfpAAlOVk
MT0sm9Sgry8QzLYM1a+9wsTlNj2sJ21Zbz5QPPClGbVcBphqRRXscooyeREUZuXD
wRQGydy51+51s6P2HFXlKN4ISY2zMy61rIiYcCX8BJtW2qIqF12usRs7MuNsgqzX
Wc0BrTrmj2hMUQbiPREqKJenMfMg0IHlMv7gjSJ2bmLiLUw7PqJfyIdO53CNqtrZ
iW+kkTP0TLdsIFkIEcmaXCez2A4Zsy6/hro2R3HBamCisF5+BkbptaoA0zEGOmGz
hFunHutFELqPAAMFB/4v9RliFLY657f1ouqBOm54WqmXSYS/VyAf4b8pRacxEujR
Pa9+8/oG3FL7Zrdv+Z5nzuSw5PvXG4SSjojuTfp2JQALJn4yuAjSoB3YNd8mxq8m
XR8ZdYrvLrigZR5crnIPRsT/am/+3wkNBYcsRqfXyJdvRa2jpPLD5mcVW1bi/4U3
HOs02Q4hM/RqVb1EqFvd6ECxxigFTI2p7VWpXb7dcV0gB3hA7yZz/SkwRXdwnXDg
oPWoRi/H9Fl+ynQld8Um3gdawV4bHIItx9xUx8MuahwtjIGHGSMLvp/+J+c/VNV4
OP4Xd+rPcojRKdXUmVl3lnv9ffr3LrzPnndvnfuTiEkEGBECAAkFAkRqGUoCGwwA
CgkQwGrv/SO8VK3EWACePCD19Fiv7NcjWGSgS4jgMY49qLUAoKaGvfRoM19AuFpy
YtpANA/XnOZy
=dy1H
-----END PGP PUBLIC KEY BLOCK-----
', '
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

lQHhBERqGUYRBAC7bt8WW1hiZvCs2jLRWeF9tGdPzzL2DizR7/QyJpLs4Sv2fk1k
lnasY24OsafgECWgZhMu+rdxcLrN/WoDfc3f823fFbaun9QQpcqWahUkGSdwOgg/
b6UoJ2PJnAMR9oTAOtwPUhwYxJgc0/XHADh+hki5AtSygO0yWXxjwhJEewCgzk5n
2TVwMKIPweTIHpZypZ9EHUUD/jCyP5hdL1W5R78OLh3BAoZiOyQMwhVSNRtllmUL
WRIIrWFjCi/ec+G8hC4cBYOYfuUAagTq9NN8PZGuMUxO8pB6ldPc4+DH9uRyDFiS
ETsmtiJy/9CpvoDl31aHxLkQgsFc3TAHks4wqPcStZagBjlWFgZzY5+patFaVmB9
91YdBAC5Gk8n4VzlbK1Ncw8qQm/G/7PfwNdCU3uFK7xw0LwO/9Jtxgu7limh85wo
2UZ4CSku7ymkDrt+DbA7ubL7hlRezyOyRgg3qy3vTnmgsLjaZRsQgvCpdRWP5Rmo
B7fPA1QkSwLyX1f7Rg2pOWq/YQOLUxwcs29UwOyPLYvO/gB9UP4DAwL0Cha/7ysH
UWD7EySqw+S1zwpFr+iFpJ6ofA1l/GZIBmZ9LskkWUhk+zYmdOZxU6Yd22+Cvg29
PbrO5rQlaWQgZGIgKHRlc3RpbmcpIDxpZGRiQHllbGxvd2JhbmsuY29tPohgBBMR
AgAgBQJEahlGAhsDBgsJCAcDAgQVAggDBBYCAwECHgECF4AACgkQwGrv/SO8VK2P
EQCggMQXddk/+3F1SroYNwxPp+Po2bYAnj0pamoG2uTWVfKntwUFGyh76Z7HnQJj
BERqGUoQCADVZUF2SX/snUvADwFjmM68sHWT1CKc1QYA9uogGWG+3tg/+05L31+2
0cZFJk1hVEtpfpAAlOVkMT0sm9Sgry8QzLYM1a+9wsTlNj2sJ21Zbz5QPPClGbVc
BphqRRXscooyeREUZuXDwRQGydy51+51s6P2HFXlKN4ISY2zMy61rIiYcCX8BJtW
2qIqF12usRs7MuNsgqzXWc0BrTrmj2hMUQbiPREqKJenMfMg0IHlMv7gjSJ2bmLi
LUw7PqJfyIdO53CNqtrZiW+kkTP0TLdsIFkIEcmaXCez2A4Zsy6/hro2R3HBamCi
sF5+BkbptaoA0zEGOmGzhFunHutFELqPAAMFB/4v9RliFLY657f1ouqBOm54WqmX
SYS/VyAf4b8pRacxEujRPa9+8/oG3FL7Zrdv+Z5nzuSw5PvXG4SSjojuTfp2JQAL
Jn4yuAjSoB3YNd8mxq8mXR8ZdYrvLrigZR5crnIPRsT/am/+3wkNBYcsRqfXyJdv
Ra2jpPLD5mcVW1bi/4U3HOs02Q4hM/RqVb1EqFvd6ECxxigFTI2p7VWpXb7dcV0g
B3hA7yZz/SkwRXdwnXDgoPWoRi/H9Fl+ynQld8Um3gdawV4bHIItx9xUx8Muahwt
jIGHGSMLvp/+J+c/VNV4OP4Xd+rPcojRKdXUmVl3lnv9ffr3LrzPnndvnfuT/gMD
AvQKFr/vKwdRYGpFTnz+ODJKkGvWRlRXemHJ5tBFshZKxaR53tqhJpjV2t6VigjU
h3u2LL+VnHD26KY7Gk7Q8wZLqISn/xFkqqWOrZ4TFvrXuWOISQQYEQIACQUCRGoZ
SgIbDAAKCRDAau/9I7xUrcRYAJ96Vw6m9K6d/ym5XS8Qu1Ax0ccALACgxWsDFEke
07owOjmWbNEbR94+6yA=
=H1E5
-----END PGP PRIVATE KEY BLOCK-----
' );  
------------------------------------------------------------------------


------------------------------------------------------------------------
--  pgp_pub_encrypt(data text, key bytea [, options text] ) RETURNS bytea
--  pgp_pub_encrypt_bytea(data bytea, key bytea [, options text] ) RETURNS bytea
------------------------------------------------------------------------



-- CREATE RULE lock_test_user_update
-- AS ON UPDATE TO test
-- WHERE old.aname = CURRENT_USER
-- DO INSTEAD nothing;
--
-- CREATE RULE lock_test_user_delete
-- AS ON DELETE TO test
-- WHERE old.aname = CURRENT_USER
-- DO INSTEAD nothing;


-- CREATE OR REPLACE FUNCTION tr() RETURNS "trigger" AS $$
-- BEGIN
--   UPDATE othertable SET cnt = cnt + 1 WHERE col = NEW.col;
--   IF NOT FOUND THEN
--    INSERT INTO othertable (col, cnt) VALUES ( NEW.col, 1 );
--   END IF;
--   RETURN NEW;
-- END;
-- $$ LANGUAGE plpgsql;
-- ...or maybe
-- $$ LANGUAGE plpgsql VOLATILE;


-- EXECUTE 'UPDATE tbl SET '
--         || quote_ident(colname)
--         || ' = '
--         || quote_literal(newvalue)
--         || ' WHERE key = '
--         || quote_literal(keyvalue);
--
--  This example demonstrates the use of the quote_ident and quote_literal
--  functions. For safety, expressions containing column and table
--  identifiers should be passed to quote_ident. Expressions containing
--  values that should be literal strings in the constructed command should
--  be passed to quote_literal. Both take the appropriate steps to return
--  the input text enclosed in double or single quotes respectively, with
--  any embedded special characters properly escaped.