1 // Copyright (C) 2016-2021 Internet Systems Consortium, Inc. ("ISC")
2 //
3 // This Source Code Form is subject to the terms of the Mozilla Public
4 // License, v. 2.0. If a copy of the MPL was not distributed with this
5 // file, You can obtain one at http://mozilla.org/MPL/2.0/.
6
7 #include <config.h>
8
9 #include <database/db_log.h>
10 #include <pgsql/pgsql_connection.h>
11 #include <pgsql/pgsql_exchange.h>
12
13 // PostgreSQL errors should be tested based on the SQL state code. Each state
14 // code is 5 decimal, ASCII, digits, the first two define the category of
15 // error, the last three are the specific error. PostgreSQL makes the state
16 // code as a char[5]. Macros for each code are defined in PostgreSQL's
17 // server/utils/errcodes.h, although they require a second macro,
18 // MAKE_SQLSTATE for completion. For example, duplicate key error as:
19 //
20 // #define ERRCODE_UNIQUE_VIOLATION MAKE_SQLSTATE('2','3','5','0','5')
21 //
22 // PostgreSQL deliberately omits the MAKE_SQLSTATE macro so callers can/must
23 // supply their own. We'll define it as an initialization list:
24 #define MAKE_SQLSTATE(ch1,ch2,ch3,ch4,ch5) {ch1,ch2,ch3,ch4,ch5}
25 // So we can use it like this: const char some_error[] = ERRCODE_xxxx;
26 #define PGSQL_STATECODE_LEN 5
27 #include <utils/errcodes.h>
28
29 #include <sstream>
30
31 using namespace std;
32
33 namespace isc {
34 namespace db {
35
36 // Default connection timeout
37
38 /// @todo: migrate this default timeout to src/bin/dhcpX/simple_parserX.cc
39 const int PGSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
40
41 const char PgSqlConnection::DUPLICATE_KEY[] = ERRCODE_UNIQUE_VIOLATION;
42
PgSqlResult(PGresult * result)43 PgSqlResult::PgSqlResult(PGresult *result)
44 : result_(result), rows_(0), cols_(0) {
45 if (!result) {
46 // Certain failures, like a loss of connectivity, can return a
47 // null PGresult and we still need to be able to create a PgSqlResult.
48 // We'll set row and col counts to -1 to prevent anyone going off the
49 // rails.
50 rows_ = -1;
51 cols_ = -1;
52 } else {
53 rows_ = PQntuples(result);
54 cols_ = PQnfields(result);
55 }
56 }
57
58 void
rowCheck(int row) const59 PgSqlResult::rowCheck(int row) const {
60 if (row < 0 || row >= rows_) {
61 isc_throw (db::DbOperationError, "row: " << row
62 << ", out of range: 0.." << rows_);
63 }
64 }
65
~PgSqlResult()66 PgSqlResult::~PgSqlResult() {
67 if (result_) {
68 PQclear(result_);
69 }
70 }
71
72 void
colCheck(int col) const73 PgSqlResult::colCheck(int col) const {
74 if (col < 0 || col >= cols_) {
75 isc_throw (DbOperationError, "col: " << col
76 << ", out of range: 0.." << cols_);
77 }
78 }
79
80 void
rowColCheck(int row,int col) const81 PgSqlResult::rowColCheck(int row, int col) const {
82 rowCheck(row);
83 colCheck(col);
84 }
85
86 std::string
getColumnLabel(const int col) const87 PgSqlResult::getColumnLabel(const int col) const {
88 const char* label = NULL;
89 try {
90 colCheck(col);
91 label = PQfname(result_, col);
92 } catch (...) {
93 std::ostringstream os;
94 os << "Unknown column:" << col;
95 return (os.str());
96 }
97
98 return (label);
99 }
100
PgSqlTransaction(PgSqlConnection & conn)101 PgSqlTransaction::PgSqlTransaction(PgSqlConnection& conn)
102 : conn_(conn), committed_(false) {
103 conn_.startTransaction();
104 }
105
~PgSqlTransaction()106 PgSqlTransaction::~PgSqlTransaction() {
107 // If commit() wasn't explicitly called, rollback.
108 if (!committed_) {
109 conn_.rollback();
110 }
111 }
112
113 void
commit()114 PgSqlTransaction::commit() {
115 conn_.commit();
116 committed_ = true;
117 }
118
~PgSqlConnection()119 PgSqlConnection::~PgSqlConnection() {
120 if (conn_) {
121 // Deallocate the prepared queries.
122 if (PQstatus(conn_) == CONNECTION_OK) {
123 PgSqlResult r(PQexec(conn_, "DEALLOCATE all"));
124 if (PQresultStatus(r) != PGRES_COMMAND_OK) {
125 // Highly unlikely but we'll log it and go on.
126 DB_LOG_ERROR(PGSQL_DEALLOC_ERROR)
127 .arg(PQerrorMessage(conn_));
128 }
129 }
130 }
131 }
132
133 std::pair<uint32_t, uint32_t>
getVersion(const ParameterMap & parameters)134 PgSqlConnection::getVersion(const ParameterMap& parameters) {
135 // Get a connection.
136 PgSqlConnection conn(parameters);
137
138 // Open the database.
139 conn.openDatabase();
140
141 const char* version_sql = "SELECT version, minor FROM schema_version;";
142 PgSqlResult r(PQexec(conn.conn_, version_sql));
143 if (PQresultStatus(r) != PGRES_TUPLES_OK) {
144 isc_throw(DbOperationError, "unable to execute PostgreSQL statement <"
145 << version_sql << ", reason: " << PQerrorMessage(conn.conn_));
146 }
147
148 uint32_t version;
149 PgSqlExchange::getColumnValue(r, 0, 0, version);
150
151 uint32_t minor;
152 PgSqlExchange::getColumnValue(r, 0, 1, minor);
153
154 return (make_pair(version, minor));
155 }
156
157 void
prepareStatement(const PgSqlTaggedStatement & statement)158 PgSqlConnection::prepareStatement(const PgSqlTaggedStatement& statement) {
159 // Prepare all statements queries with all known fields datatype
160 PgSqlResult r(PQprepare(conn_, statement.name, statement.text,
161 statement.nbparams, statement.types));
162 if (PQresultStatus(r) != PGRES_COMMAND_OK) {
163 isc_throw(DbOperationError, "unable to prepare PostgreSQL statement: "
164 << statement.text << ", reason: " << PQerrorMessage(conn_));
165 }
166 }
167
168 void
prepareStatements(const PgSqlTaggedStatement * start_statement,const PgSqlTaggedStatement * end_statement)169 PgSqlConnection::prepareStatements(const PgSqlTaggedStatement* start_statement,
170 const PgSqlTaggedStatement* end_statement) {
171 // Created the PostgreSQL prepared statements.
172 for (const PgSqlTaggedStatement* tagged_statement = start_statement;
173 tagged_statement != end_statement; ++tagged_statement) {
174 prepareStatement(*tagged_statement);
175 }
176 }
177
178 void
openDatabase()179 PgSqlConnection::openDatabase() {
180 string dbconnparameters;
181 string shost = "localhost";
182 try {
183 shost = getParameter("host");
184 } catch(...) {
185 // No host. Fine, we'll use "localhost"
186 }
187
188 dbconnparameters += "host = '" + shost + "'" ;
189
190 string sport;
191 try {
192 sport = getParameter("port");
193 } catch (...) {
194 // No port parameter, we are going to use the default port.
195 sport = "";
196 }
197
198 if (sport.size() > 0) {
199 unsigned int port = 0;
200
201 // Port was given, so try to convert it to an integer.
202 try {
203 port = boost::lexical_cast<unsigned int>(sport);
204 } catch (...) {
205 // Port given but could not be converted to an unsigned int.
206 // Just fall back to the default value.
207 port = 0;
208 }
209
210 // The port is only valid when it is in the 0..65535 range.
211 // Again fall back to the default when the given value is invalid.
212 if (port > numeric_limits<uint16_t>::max()) {
213 port = 0;
214 }
215
216 // Add it to connection parameters when not default.
217 if (port > 0) {
218 std::ostringstream oss;
219 oss << port;
220 dbconnparameters += " port = " + oss.str();
221 }
222 }
223
224 string suser;
225 try {
226 suser = getParameter("user");
227 dbconnparameters += " user = '" + suser + "'";
228 } catch(...) {
229 // No user. Fine, we'll use NULL
230 }
231
232 string spassword;
233 try {
234 spassword = getParameter("password");
235 dbconnparameters += " password = '" + spassword + "'";
236 } catch(...) {
237 // No password. Fine, we'll use NULL
238 }
239
240 string sname;
241 try {
242 sname = getParameter("name");
243 dbconnparameters += " dbname = '" + sname + "'";
244 } catch(...) {
245 // No database name. Throw a "NoDatabaseName" exception
246 isc_throw(NoDatabaseName, "must specify a name for the database");
247 }
248
249 unsigned int connect_timeout = PGSQL_DEFAULT_CONNECTION_TIMEOUT;
250 string stimeout;
251 try {
252 stimeout = getParameter("connect-timeout");
253 } catch (...) {
254 // No timeout parameter, we are going to use the default timeout.
255 stimeout = "";
256 }
257
258 if (stimeout.size() > 0) {
259 // Timeout was given, so try to convert it to an integer.
260
261 try {
262 connect_timeout = boost::lexical_cast<unsigned int>(stimeout);
263 } catch (...) {
264 // Timeout given but could not be converted to an unsigned int. Set
265 // the connection timeout to an invalid value to trigger throwing
266 // of an exception.
267 connect_timeout = 0;
268 }
269
270 // The timeout is only valid if greater than zero, as depending on the
271 // database, a zero timeout might signify something like "wait
272 // indefinitely".
273 //
274 // The check below also rejects a value greater than the maximum
275 // integer value. The lexical_cast operation used to obtain a numeric
276 // value from a string can get confused if trying to convert a negative
277 // integer to an unsigned int: instead of throwing an exception, it may
278 // produce a large positive value.
279 if ((connect_timeout == 0) ||
280 (connect_timeout > numeric_limits<int>::max())) {
281 isc_throw(DbInvalidTimeout, "database connection timeout (" <<
282 stimeout << ") must be an integer greater than 0");
283 }
284 }
285
286 std::ostringstream oss;
287 oss << connect_timeout;
288 dbconnparameters += " connect_timeout = " + oss.str();
289
290 // Connect to Postgres, saving the low level connection pointer
291 // in the holder object
292 PGconn* new_conn = PQconnectdb(dbconnparameters.c_str());
293 if (!new_conn) {
294 isc_throw(DbOpenError, "could not allocate connection object");
295 }
296
297 if (PQstatus(new_conn) != CONNECTION_OK) {
298 // If we have a connection object, we have to call finish
299 // to release it, but grab the error message first.
300 std::string error_message = PQerrorMessage(new_conn);
301 PQfinish(new_conn);
302 isc_throw(DbOpenError, error_message);
303 }
304
305 // We have a valid connection, so let's save it to our holder
306 conn_.setConnection(new_conn);
307 }
308
309 bool
compareError(const PgSqlResult & r,const char * error_state)310 PgSqlConnection::compareError(const PgSqlResult& r, const char* error_state) {
311 const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
312 // PostgreSQL guarantees it will always be 5 characters long
313 return ((sqlstate != NULL) &&
314 (memcmp(sqlstate, error_state, PGSQL_STATECODE_LEN) == 0));
315 }
316
317 void
checkStatementError(const PgSqlResult & r,PgSqlTaggedStatement & statement)318 PgSqlConnection::checkStatementError(const PgSqlResult& r,
319 PgSqlTaggedStatement& statement) {
320 int s = PQresultStatus(r);
321 if (s != PGRES_COMMAND_OK && s != PGRES_TUPLES_OK) {
322 // We're testing the first two chars of SQLSTATE, as this is the
323 // error class. Note, there is a severity field, but it can be
324 // misleadingly returned as fatal. However, a loss of connectivity
325 // can lead to a NULL sqlstate with a status of PGRES_FATAL_ERROR.
326 const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
327 if ((sqlstate == NULL) ||
328 ((memcmp(sqlstate, "08", 2) == 0) || // Connection Exception
329 (memcmp(sqlstate, "53", 2) == 0) || // Insufficient resources
330 (memcmp(sqlstate, "54", 2) == 0) || // Program Limit exceeded
331 (memcmp(sqlstate, "57", 2) == 0) || // Operator intervention
332 (memcmp(sqlstate, "58", 2) == 0))) { // System error
333 DB_LOG_ERROR(PGSQL_FATAL_ERROR)
334 .arg(statement.name)
335 .arg(PQerrorMessage(conn_))
336 .arg(sqlstate ? sqlstate : "<sqlstate null>");
337
338 // Mark this connection as no longer usable.
339 markUnusable();
340
341 // Start the connection recovery.
342 startRecoverDbConnection();
343
344 // We still need to throw so caller can error out of the current
345 // processing.
346 isc_throw(DbConnectionUnusable,
347 "fatal database error or connectivity lost");
348 }
349
350 // Apparently it wasn't fatal, so we throw with a helpful message.
351 const char* error_message = PQerrorMessage(conn_);
352 isc_throw(DbOperationError, "Statement exec failed for: "
353 << statement.name << ", status: " << s
354 << "sqlstate:[ " << (sqlstate ? sqlstate : "<null>")
355 << " ], reason: " << error_message);
356 }
357 }
358
359 void
startTransaction()360 PgSqlConnection::startTransaction() {
361 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, PGSQL_START_TRANSACTION);
362 checkUnusable();
363 PgSqlResult r(PQexec(conn_, "START TRANSACTION"));
364 if (PQresultStatus(r) != PGRES_COMMAND_OK) {
365 const char* error_message = PQerrorMessage(conn_);
366 isc_throw(DbOperationError, "unable to start transaction"
367 << error_message);
368 }
369 }
370
371 void
commit()372 PgSqlConnection::commit() {
373 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, PGSQL_COMMIT);
374 checkUnusable();
375 PgSqlResult r(PQexec(conn_, "COMMIT"));
376 if (PQresultStatus(r) != PGRES_COMMAND_OK) {
377 const char* error_message = PQerrorMessage(conn_);
378 isc_throw(DbOperationError, "commit failed: " << error_message);
379 }
380 }
381
382 void
rollback()383 PgSqlConnection::rollback() {
384 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, PGSQL_ROLLBACK);
385 checkUnusable();
386 PgSqlResult r(PQexec(conn_, "ROLLBACK"));
387 if (PQresultStatus(r) != PGRES_COMMAND_OK) {
388 const char* error_message = PQerrorMessage(conn_);
389 isc_throw(DbOperationError, "rollback failed: " << error_message);
390 }
391 }
392
393 } // end of isc::db namespace
394 } // end of isc namespace
395