beargiles

--
-- Create user-defined record type for symmetric keys
--
CREATE TYPE skey AS (
  key_id int4,
  key bytea,
  type text
);

--

beargiles

-- ---------------------------------------
-- Preparation
-- ---------------------------------------
CREATE SCHEMA pgcrypto;
CREATE EXTENSION pgcrypto WITH SCHEMA pgcrypto;

CREATE USER key_manager;
CREATE SCHEMA key_manager;
ALTER SCHEMA key_manager OWNER TO key_manager;
ALTER ROLE key_manager SET search_path TO key_manager;

beargiles

/**
 * Permissive SecurityManager
 *
 * This SecurityManager can still be highly restricive but it allows
 * access to a sensitive service.
 */
public class PermissiveSecurityManager extends SecurityManager {
    private final List<String> hosts = new ArrayList<>();

    public PermissiveSecurityManager(String... hosts) {

beargiles

create or replace function get_host_enum(hostname text)
  returns host_enum as
$$
declare
   _value host_enum;
begin
   select hosts1.key from hosts1 where hosts1.hostname = $1 into _value;

   if not found then
      insert into hosts1(hostname) values ($1) on conflict do nothing;