7 #include <log/logger.hpp>
10 PostgresJobProvider::PostgresJobProvider( const std::string& server, const std::string& user, const std::string& password, const std::string& database ):
11 c( "dbname=" + database + " host=" + server + " user=" + user + " password=" + password + " client_encoding=UTF-8 application_name=cassiopeia-client" ) {
12 // TODO better connection string generation??
16 std::shared_ptr<Job> PostgresJobProvider::fetchJob() {
17 std::string q = "SELECT id, \"targetId\", task, \"executeFrom\", \"executeTo\", warning FROM jobs WHERE state='open' AND warning < 3";
19 pqxx::result result = txn.exec( q );
22 auto job = std::make_shared<Job>();
24 if( result.size() == 0 ) {
28 job->id = result[0]["id"].as<std::string>();
29 job->target = result[0]["\"targetId\""].as<std::string>();
30 job->task = result[0]["task"].as<std::string>();
31 job->from = result[0]["\"executeFrom\""].as<std::string>( "" );
32 job->to = result[0]["\"executeTo\""].as<std::string>( "" );
33 job->warning = result[0]["warning"].as<std::string>();
35 logger::notef( "Got a job: (id=%s, target=%s, task=%s, from=%s, to=%s, warnings=%s)", job->id, job->target, job->task, job->from, job->to, job->warning );
40 void PostgresJobProvider::finishJob( std::shared_ptr<Job> job ) {
43 std::string q = "UPDATE jobs SET state='done' WHERE id=" + txn.quote( job->id );
44 pqxx::result r = txn.exec( q );
46 if( r.affected_rows() != 1 ) {
47 throw std::runtime_error( "No database entry found." );
53 void PostgresJobProvider::failJob( std::shared_ptr<Job> job ) {
56 std::string q = "UPDATE jobs SET warning = warning + 1 WHERE id=" + txn.quote( job->id );
57 pqxx::result r = txn.exec( q );
59 if( r.affected_rows() != 1 ) {
60 throw std::runtime_error( "No database entry found." );
66 std::shared_ptr<TBSCertificate> PostgresJobProvider::fetchTBSCert( std::shared_ptr<Job> job ) {
68 auto cert = std::make_shared<TBSCertificate>();
69 std::string q = "SELECT md, profile, csr_name, csr_type, keyname FROM certs INNER JOIN profiles ON profiles.id = certs.profile WHERE certs.id=" + txn.quote( job->target );
70 pqxx::result r = txn.exec( q );
73 throw std::runtime_error( "Error, no or multiple certs found" );
78 std::string profileName = ro["keyname"].as<std::string>();
80 cert->md = ro["md"].as<std::string>();
81 std::string profileId = ro["profile"].as<std::string>();
83 while( profileId.size() < 4 ) {
84 profileId = "0" + profileId;
87 cert->profile = profileId + "-" + profileName;
89 cert->csr = ro["csr_name"].as<std::string>();
90 cert->csr_type = ro["csr_type"].as<std::string>();
92 cert->SANs = std::vector<std::shared_ptr<SAN>>();
94 q = "SELECT contents, type FROM \"subjectAlternativeNames\" WHERE \"certId\"=" + txn.quote( job->target );
97 std::cout << "Fetching SANs" << std::endl;
99 for( auto row = r.begin(); row != r.end(); ++row ) {
100 auto nSAN = std::make_shared<SAN>();
101 nSAN->content = row["contents"].as<std::string>();
102 nSAN->type = row["type"].as<std::string>();
103 cert->SANs.push_back( nSAN );
106 q = "SELECT name, value FROM \"certAvas\" WHERE \"certId\"=" + txn.quote( job->target );
109 for( auto row = r.begin(); row != r.end(); ++row ) {
110 auto nAVA = std::make_shared<AVA>();
111 nAVA->name = row["name"].as<std::string>();
112 nAVA->value = row["value"].as<std::string>();
113 cert->AVAs.push_back( nAVA );
119 std::string pgTime( std::string isoTime ) {
120 return isoTime.substr( 0, 8 ) + " " + isoTime.substr( 8, 6 );
123 void PostgresJobProvider::writeBack( std::shared_ptr<Job> job, std::shared_ptr<SignedCertificate> res ) {
125 std::string id = "SELECT id FROM cacerts WHERE keyname=" + txn.quote( res->ca_name );
126 pqxx::result r = txn.exec( id );
130 if( r.size() != 1 ) {
131 throw std::runtime_error( "Error while inserting new ca cert not found" );
133 read_id = r[0]["id"].as<std::string>();
136 std::string serial = res->serial;
137 std::transform( serial.begin(), serial.end(), serial.begin(), ::tolower );
139 if( serial[0] == '0' ) {
140 serial = serial.substr( 1 );
143 std::string q = "UPDATE certs SET crt_name=" + txn.quote( res->crt_name ) + ", serial=" + txn.quote( serial ) + ", \"caid\" = " + txn.quote( read_id ) + ", created=" + txn.quote( pgTime( res->before ) ) + ", expire=" + txn.quote( pgTime( res->after ) ) + " WHERE id=" + txn.quote( job->target );
144 // TODO write more thingies back
148 if( r.affected_rows() != 1 ) {
149 throw std::runtime_error( "Only one row should be updated." );
155 std::pair<std::string, std::string> PostgresJobProvider::getRevocationInfo( std::shared_ptr<Job> job ) {
157 std::string q = "SELECT certs.serial, cacerts.keyname FROM certs INNER JOIN cacerts ON certs.\"caid\" = cacerts.id WHERE certs.id = " + txn.quote( job->target );
159 pqxx::result r = txn.exec( q );
161 if( r.size() != 1 ) {
162 throw std::runtime_error( "Only one row expected but multiple found." );
165 return {r[0][0].as<std::string>(), r[0][1].as<std::string>()};
168 void PostgresJobProvider::writeBackRevocation( std::shared_ptr<Job> job, std::string date ) {
169 logger::notef( "Revoking at %s", date );
171 logger::note( "executing" );
172 pqxx::result r = txn.exec( "UPDATE certs SET revoked = " + txn.quote( pgTime( date ) ) + " WHERE id = " + txn.quote( job->target ) );
174 if( r.affected_rows() != 1 ) {
175 throw std::runtime_error( "Only one row should be updated." );
178 logger::note( "committing" );
180 logger::note( "committed" );