Difference between revisions of "PKI ACME Responder with PostgreSQL Database"

From Dogtag
Jump to: navigation, search
m
m (Installation)
(18 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
= Installation =
 +
 +
<pre>
 +
$ dnf install postgresql-jdbc
 +
$ cd /usr/share/pki/server/common/lib
 +
$ ln -s /usr/share/java/postgresql-jdbc/postgresql.jar
 +
</pre>
 +
 
= Configuration =
 
= Configuration =
  
Line 19: Line 27:
  
 
<pre>
 
<pre>
psql=> create table nonces (
+
psql> create table "nonces" (
     value         varchar(40) primary key,
+
     "valuevarchar primary key,
     expirationTime date not null
+
     "expires" timestamp not null
 
);
 
);
 
</pre>
 
</pre>
Line 28: Line 36:
  
 
<pre>
 
<pre>
psql=> drop table nonces;
+
psql> drop table "nonces";
 +
</pre>
 +
 
 +
= Accounts Table =
 +
 
 +
To create the accounts table:
 +
 
 +
<pre>
 +
psql> create table "accounts" (
 +
    "id"    varchar primary key,
 +
    "status" varchar not null,
 +
    "orders" varchar not null,
 +
    "jwk" varchar not null
 +
);
 +
</pre>
 +
 
 +
To drop the accounts table:
 +
 
 +
<pre>
 +
psql> drop table "accounts";
 +
</pre>
 +
 
 +
= Account Contacts Table =
 +
 
 +
To create the account_contacts table:
 +
 
 +
<pre>
 +
psql> create table "account_contacts" (
 +
    "account_id" varchar not null,
 +
    "contact"    varchar not null
 +
);
 +
</pre>
 +
 
 +
To drop the account_contacts table:
 +
 
 +
<pre>
 +
psql> drop table "account_contacts";
 +
</pre>
 +
 
 +
= Orders Table =
 +
 
 +
To create the orders table:
 +
 
 +
<pre>
 +
psql> create table "orders" (
 +
    "id"          varchar primary key,
 +
    "account_id"  varchar not null,
 +
    "status"      varchar not null,
 +
    "expires"    timestamp not null,
 +
    "not_before"  timestamp,
 +
    "not_after"  timestamp,
 +
    "finalize"    varchar,
 +
    "csr"        varchar,
 +
    "certificate" varchar,
 +
    "resource"    varchar
 +
);
 +
</pre>
 +
 
 +
To drop the orders table:
 +
 
 +
<pre>
 +
psql> drop table "orders";
 +
</pre>
 +
 
 +
= Order Identifiers Table =
 +
 
 +
To create the order_identifiers table:
 +
 
 +
<pre>
 +
psql> create table "order_identifiers" (
 +
    "order_id" varchar not null,
 +
    "type"    varchar not null,
 +
    "value"    varchar not null
 +
);
 +
</pre>
 +
 
 +
To drop the order_identifiers table:
 +
 
 +
<pre>
 +
psql> drop table "order_identifiers";
 +
</pre>
 +
 
 +
= Order Authorizations Table =
 +
 
 +
To create the order_authorizations table:
 +
 
 +
<pre>
 +
psql> create table "order_authorizations" (
 +
    "order_id" varchar not null,
 +
    "url"    varchar not null
 +
);
 +
</pre>
 +
 
 +
To drop the order_authorizations table:
 +
 
 +
<pre>
 +
psql> drop table "order_authorizations";
 +
</pre>
 +
 
 +
= Authorizations Table =
 +
 
 +
To create the authorizations table:
 +
 
 +
<pre>
 +
psql> create table "authorizations" (
 +
    "id"              varchar primary key,
 +
    "account_id"      varchar not null,
 +
    "status"          varchar not null,
 +
    "expires"          timestamp not null,
 +
    "identifier_type"  varchar,
 +
    "identifier_value" varchar,
 +
    "wildcard"        boolean
 +
);
 +
</pre>
 +
 
 +
To drop the authorizations table:
 +
 
 +
<pre>
 +
psql> drop table "authorizations";
 +
</pre>
 +
 
 +
= Authorization Challenges Table =
 +
 
 +
To create the authorization_challenges table:
 +
 
 +
<pre>
 +
psql> create table "authorization_challenges" (
 +
    "id"        varchar not null,
 +
    "authz_id"  varchar not null,
 +
    "type"      varchar not null,
 +
    "url"        varchar not null,
 +
    "token"      varchar not null,
 +
    "status"    varchar not null,
 +
    "validated"  timestamp
 +
);
 +
</pre>
 +
 
 +
To drop the authorization_challenges table:
 +
 
 +
<pre>
 +
psql> drop table "authorization_challenges";
 
</pre>
 
</pre>
  

Revision as of 22:59, 8 November 2019

Installation

$ dnf install postgresql-jdbc
$ cd /usr/share/pki/server/common/lib
$ ln -s /usr/share/java/postgresql-jdbc/postgresql.jar

Configuration

The configuration will be stored in /etc/pki/pki-tomcat/acme/database.json, for example:

{
    "class": "org.dogtagpki.acme.database.PostgreSQLDatabase",
    "parameters": {
        "url": "jdbc:postgresql://localhost:5432/acme",
        "user": "acme",
        "password": "Secret.123"
    }
}

Nonces Table

To create the nonces table:

psql> create table "nonces" (
    "value"   varchar primary key,
    "expires" timestamp not null
);

To drop the nonces table:

psql> drop table "nonces";

Accounts Table

To create the accounts table:

psql> create table "accounts" (
    "id"     varchar primary key,
    "status" varchar not null,
    "orders" varchar not null,
    "jwk" varchar not null
);

To drop the accounts table:

psql> drop table "accounts";

Account Contacts Table

To create the account_contacts table:

psql> create table "account_contacts" (
    "account_id" varchar not null,
    "contact"    varchar not null
);

To drop the account_contacts table:

psql> drop table "account_contacts";

Orders Table

To create the orders table:

psql> create table "orders" (
    "id"          varchar primary key,
    "account_id"  varchar not null,
    "status"      varchar not null,
    "expires"     timestamp not null,
    "not_before"  timestamp,
    "not_after"   timestamp,
    "finalize"    varchar,
    "csr"         varchar,
    "certificate" varchar,
    "resource"    varchar
);

To drop the orders table:

psql> drop table "orders";

Order Identifiers Table

To create the order_identifiers table:

psql> create table "order_identifiers" (
    "order_id" varchar not null,
    "type"     varchar not null,
    "value"    varchar not null
);

To drop the order_identifiers table:

psql> drop table "order_identifiers";

Order Authorizations Table

To create the order_authorizations table:

psql> create table "order_authorizations" (
    "order_id" varchar not null,
    "url"    varchar not null
);

To drop the order_authorizations table:

psql> drop table "order_authorizations";

Authorizations Table

To create the authorizations table:

psql> create table "authorizations" (
    "id"               varchar primary key,
    "account_id"       varchar not null,
    "status"           varchar not null,
    "expires"          timestamp not null,
    "identifier_type"  varchar,
    "identifier_value" varchar,
    "wildcard"         boolean
);

To drop the authorizations table:

psql> drop table "authorizations";

Authorization Challenges Table

To create the authorization_challenges table:

psql> create table "authorization_challenges" (
    "id"         varchar not null,
    "authz_id"   varchar not null,
    "type"       varchar not null,
    "url"        varchar not null,
    "token"      varchar not null,
    "status"     varchar not null,
    "validated"  timestamp
);

To drop the authorization_challenges table:

psql> drop table "authorization_challenges";

See Also