Kea  2.1.7-git
pgsql_connection.cc
Go to the documentation of this file.
1 // Copyright (C) 2016-2022 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 
10 #include <database/db_log.h>
11 #include <pgsql/pgsql_connection.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 
39 const int PGSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
40 
41 const char PgSqlConnection::DUPLICATE_KEY[] = ERRCODE_UNIQUE_VIOLATION;
42 const char PgSqlConnection::NULL_KEY[] = ERRCODE_NOT_NULL_VIOLATION;
43 
44 bool PgSqlConnection::warned_about_tls = false;
45 
46 PgSqlResult::PgSqlResult(PGresult *result)
47  : result_(result), rows_(0), cols_(0) {
48  if (!result) {
49  // Certain failures, like a loss of connectivity, can return a
50  // null PGresult and we still need to be able to create a PgSqlResult.
51  // We'll set row and col counts to -1 to prevent anyone going off the
52  // rails.
53  rows_ = -1;
54  cols_ = -1;
55  } else {
56  rows_ = PQntuples(result);
57  cols_ = PQnfields(result);
58  }
59 }
60 
61 void
62 PgSqlResult::rowCheck(int row) const {
63  if (row < 0 || row >= rows_) {
64  isc_throw (db::DbOperationError, "row: " << row
65  << ", out of range: 0.." << rows_);
66  }
67 }
68 
70  if (result_) {
71  PQclear(result_);
72  }
73 }
74 
75 void
76 PgSqlResult::colCheck(int col) const {
77  if (col < 0 || col >= cols_) {
78  isc_throw (DbOperationError, "col: " << col
79  << ", out of range: 0.." << cols_);
80  }
81 }
82 
83 void
84 PgSqlResult::rowColCheck(int row, int col) const {
85  rowCheck(row);
86  colCheck(col);
87 }
88 
89 std::string
90 PgSqlResult::getColumnLabel(const int col) const {
91  const char* label = NULL;
92  try {
93  colCheck(col);
94  label = PQfname(result_, col);
95  } catch (...) {
96  std::ostringstream os;
97  os << "Unknown column:" << col;
98  return (os.str());
99  }
100 
101  return (label);
102 }
103 
105  : conn_(conn), committed_(false) {
106  conn_.startTransaction();
107 }
108 
110  // If commit() wasn't explicitly called, rollback.
111  if (!committed_) {
112  conn_.rollback();
113  }
114 }
115 
116 void
118  conn_.commit();
119  committed_ = true;
120 }
121 
123  if (conn_) {
124  // Deallocate the prepared queries.
125  if (PQstatus(conn_) == CONNECTION_OK) {
126  PgSqlResult r(PQexec(conn_, "DEALLOCATE all"));
127  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
128  // Highly unlikely but we'll log it and go on.
130  .arg(PQerrorMessage(conn_));
131  }
132  }
133  }
134 }
135 
136 std::pair<uint32_t, uint32_t>
138  // Get a connection.
139  PgSqlConnection conn(parameters);
140 
141  // Open the database.
142  conn.openDatabase();
143 
144  const char* version_sql = "SELECT version, minor FROM schema_version;";
145  PgSqlResult r(PQexec(conn.conn_, version_sql));
146  if (PQresultStatus(r) != PGRES_TUPLES_OK) {
147  isc_throw(DbOperationError, "unable to execute PostgreSQL statement <"
148  << version_sql << ", reason: " << PQerrorMessage(conn.conn_));
149  }
150 
151  uint32_t version;
152  PgSqlExchange::getColumnValue(r, 0, 0, version);
153 
154  uint32_t minor;
155  PgSqlExchange::getColumnValue(r, 0, 1, minor);
156 
157  return (make_pair(version, minor));
158 }
159 
160 void
162  // Prepare all statements queries with all known fields datatype
163  PgSqlResult r(PQprepare(conn_, statement.name, statement.text,
164  statement.nbparams, statement.types));
165  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
166  isc_throw(DbOperationError, "unable to prepare PostgreSQL statement: "
167  << " name: " << statement.name
168  << ", reason: " << PQerrorMessage(conn_)
169  << ", text: " << statement.text);
170  }
171 }
172 
173 void
175  const PgSqlTaggedStatement* end_statement) {
176  // Created the PostgreSQL prepared statements.
177  for (const PgSqlTaggedStatement* tagged_statement = start_statement;
178  tagged_statement != end_statement; ++tagged_statement) {
179  prepareStatement(*tagged_statement);
180  }
181 }
182 
183 void
185  string dbconnparameters;
186  string shost = "localhost";
187  try {
188  shost = getParameter("host");
189  } catch(...) {
190  // No host. Fine, we'll use "localhost"
191  }
192 
193  dbconnparameters += "host = '" + shost + "'" ;
194 
195  string sport;
196  try {
197  sport = getParameter("port");
198  } catch (...) {
199  // No port parameter, we are going to use the default port.
200  sport = "";
201  }
202 
203  if (sport.size() > 0) {
204  unsigned int port = 0;
205 
206  // Port was given, so try to convert it to an integer.
207  try {
208  port = boost::lexical_cast<unsigned int>(sport);
209  } catch (...) {
210  // Port given but could not be converted to an unsigned int.
211  // Just fall back to the default value.
212  port = 0;
213  }
214 
215  // The port is only valid when it is in the 0..65535 range.
216  // Again fall back to the default when the given value is invalid.
217  if (port > numeric_limits<uint16_t>::max()) {
218  port = 0;
219  }
220 
221  // Add it to connection parameters when not default.
222  if (port > 0) {
223  std::ostringstream oss;
224  oss << port;
225  dbconnparameters += " port = " + oss.str();
226  }
227  }
228 
229  string suser;
230  try {
231  suser = getParameter("user");
232  dbconnparameters += " user = '" + suser + "'";
233  } catch(...) {
234  // No user. Fine, we'll use NULL
235  }
236 
237  string spassword;
238  try {
239  spassword = getParameter("password");
240  dbconnparameters += " password = '" + spassword + "'";
241  } catch(...) {
242  // No password. Fine, we'll use NULL
243  }
244 
245  string sname;
246  try {
247  sname = getParameter("name");
248  dbconnparameters += " dbname = '" + sname + "'";
249  } catch(...) {
250  // No database name. Throw a "NoDatabaseName" exception
251  isc_throw(NoDatabaseName, "must specify a name for the database");
252  }
253 
254  unsigned int connect_timeout = PGSQL_DEFAULT_CONNECTION_TIMEOUT;
255  string stimeout;
256  try {
257  stimeout = getParameter("connect-timeout");
258  } catch (...) {
259  // No timeout parameter, we are going to use the default timeout.
260  stimeout = "";
261  }
262 
263  if (stimeout.size() > 0) {
264  // Timeout was given, so try to convert it to an integer.
265 
266  try {
267  connect_timeout = boost::lexical_cast<unsigned int>(stimeout);
268  } catch (...) {
269  // Timeout given but could not be converted to an unsigned int. Set
270  // the connection timeout to an invalid value to trigger throwing
271  // of an exception.
272  connect_timeout = 0;
273  }
274 
275  // The timeout is only valid if greater than zero, as depending on the
276  // database, a zero timeout might signify something like "wait
277  // indefinitely".
278  //
279  // The check below also rejects a value greater than the maximum
280  // integer value. The lexical_cast operation used to obtain a numeric
281  // value from a string can get confused if trying to convert a negative
282  // integer to an unsigned int: instead of throwing an exception, it may
283  // produce a large positive value.
284  if ((connect_timeout == 0) ||
285  (connect_timeout > numeric_limits<int>::max())) {
286  isc_throw(DbInvalidTimeout, "database connection timeout (" <<
287  stimeout << ") must be an integer greater than 0");
288  }
289  }
290 
291  std::ostringstream oss;
292  oss << connect_timeout;
293  dbconnparameters += " connect_timeout = " + oss.str();
294 
295  // Connect to Postgres, saving the low level connection pointer
296  // in the holder object
297  PGconn* new_conn = PQconnectdb(dbconnparameters.c_str());
298  if (!new_conn) {
299  isc_throw(DbOpenError, "could not allocate connection object");
300  }
301 
302  if (PQstatus(new_conn) != CONNECTION_OK) {
303  // If we have a connection object, we have to call finish
304  // to release it, but grab the error message first.
305  std::string error_message = PQerrorMessage(new_conn);
306  PQfinish(new_conn);
307  isc_throw(DbOpenError, error_message);
308  }
309 
310  // We have a valid connection, so let's save it to our holder
311  conn_.setConnection(new_conn);
312 }
313 
314 bool
315 PgSqlConnection::compareError(const PgSqlResult& r, const char* error_state) {
316  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
317  // PostgreSQL guarantees it will always be 5 characters long
318  return ((sqlstate != NULL) &&
319  (memcmp(sqlstate, error_state, PGSQL_STATECODE_LEN) == 0));
320 }
321 
322 void
324  PgSqlTaggedStatement& statement) {
325  int s = PQresultStatus(r);
326  if (s != PGRES_COMMAND_OK && s != PGRES_TUPLES_OK) {
327  // We're testing the first two chars of SQLSTATE, as this is the
328  // error class. Note, there is a severity field, but it can be
329  // misleadingly returned as fatal. However, a loss of connectivity
330  // can lead to a NULL sqlstate with a status of PGRES_FATAL_ERROR.
331  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
332  if ((sqlstate == NULL) ||
333  ((memcmp(sqlstate, "08", 2) == 0) || // Connection Exception
334  (memcmp(sqlstate, "53", 2) == 0) || // Insufficient resources
335  (memcmp(sqlstate, "54", 2) == 0) || // Program Limit exceeded
336  (memcmp(sqlstate, "57", 2) == 0) || // Operator intervention
337  (memcmp(sqlstate, "58", 2) == 0))) { // System error
339  .arg(statement.name)
340  .arg(PQerrorMessage(conn_))
341  .arg(sqlstate ? sqlstate : "<sqlstate null>");
342 
343  // Mark this connection as no longer usable.
344  markUnusable();
345 
346  // Start the connection recovery.
347  startRecoverDbConnection();
348 
349  // We still need to throw so caller can error out of the current
350  // processing.
352  "fatal database error or connectivity lost");
353  }
354 
355  // Failure: check for the special case of duplicate entry.
356  if (compareError(r, PgSqlConnection::DUPLICATE_KEY)) {
357  isc_throw(DuplicateEntry, "statement: " << statement.name
358  << ", reason: " << PQerrorMessage(conn_));
359  }
360 
361  // Failure: check for the special case of null key violation.
362  if (compareError(r, PgSqlConnection::NULL_KEY)) {
363  isc_throw(NullKeyError, "statement: " << statement.name
364  << ", reason: " << PQerrorMessage(conn_));
365  }
366 
367  // Apparently it wasn't fatal, so we throw with a helpful message.
368  const char* error_message = PQerrorMessage(conn_);
369  isc_throw(DbOperationError, "Statement exec failed for: "
370  << statement.name << ", status: " << s
371  << "sqlstate:[ " << (sqlstate ? sqlstate : "<null>")
372  << " ], reason: " << error_message);
373  }
374 }
375 
376 void
378  // If it is nested transaction, do nothing.
379  if (++transaction_ref_count_ > 1) {
380  return;
381  }
382 
384  checkUnusable();
385  PgSqlResult r(PQexec(conn_, "START TRANSACTION"));
386  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
387  const char* error_message = PQerrorMessage(conn_);
388  isc_throw(DbOperationError, "unable to start transaction"
389  << error_message);
390  }
391 }
392 
393 bool
395  return (transaction_ref_count_ > 0);
396 }
397 
398 void
400  if (transaction_ref_count_ <= 0) {
401  isc_throw(Unexpected, "commit called for not started transaction - coding error");
402  }
403 
404  // When committing nested transaction, do nothing.
405  if (--transaction_ref_count_ > 0) {
406  return;
407  }
408 
410  checkUnusable();
411  PgSqlResult r(PQexec(conn_, "COMMIT"));
412  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
413  const char* error_message = PQerrorMessage(conn_);
414  isc_throw(DbOperationError, "commit failed: " << error_message);
415  }
416 }
417 
418 void
420  if (transaction_ref_count_ <= 0) {
421  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
422  }
423 
424  // When rolling back nested transaction, do nothing.
425  if (--transaction_ref_count_ > 0) {
426  return;
427  }
428 
430  checkUnusable();
431  PgSqlResult r(PQexec(conn_, "ROLLBACK"));
432  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
433  const char* error_message = PQerrorMessage(conn_);
434  isc_throw(DbOperationError, "rollback failed: " << error_message);
435  }
436 }
437 
438 void
439 PgSqlConnection::createSavepoint(const std::string& name) {
440  if (transaction_ref_count_ <= 0) {
441  isc_throw(InvalidOperation, "no transaction, cannot create savepoint: " << name);
442  }
443 
445  std::string sql("SAVEPOINT " + name);
446  executeSQL(sql);
447 }
448 
449 void
450 PgSqlConnection::rollbackToSavepoint(const std::string& name) {
451  if (transaction_ref_count_ <= 0) {
452  isc_throw(InvalidOperation, "no transaction, cannot rollback to savepoint: " << name);
453  }
454 
455  std::string sql("ROLLBACK TO SAVEPOINT " + name);
456  executeSQL(sql);
457 }
458 
459 void
460 PgSqlConnection::executeSQL(const std::string& sql) {
461  // Use a TaggedStatement so we can call checkStatementError and ensure
462  // we detect connectivity issues properly.
463  PgSqlTaggedStatement statement({0, {OID_NONE}, "run-statement", sql.c_str()});
464  checkUnusable();
465  PgSqlResult r(PQexec(conn_, statement.text));
466  checkStatementError(r, statement);
467 }
468 
471  const PsqlBindArray& in_bindings) {
472  checkUnusable();
473 
474  if (statement.nbparams != in_bindings.size()) {
475  isc_throw (InvalidOperation, "executePreparedStatement:"
476  << " expected: " << statement.nbparams
477  << " parameters, given: " << in_bindings.size()
478  << ", statement: " << statement.name
479  << ", SQL: " << statement.text);
480  }
481 
482  const char* const* values = 0;
483  const int* lengths = 0;
484  const int* formats = 0;
485  if (statement.nbparams > 0) {
486  values = static_cast<const char* const*>(&in_bindings.values_[0]);
487  lengths = static_cast<const int *>(&in_bindings.lengths_[0]);
488  formats = static_cast<const int *>(&in_bindings.formats_[0]);
489  }
490 
491  PgSqlResultPtr result_set;
492  result_set.reset(new PgSqlResult(PQexecPrepared(conn_, statement.name, statement.nbparams,
493  values, lengths, formats, 0)));
494 
495  checkStatementError(*result_set, statement);
496  return (result_set);
497 }
498 
499 void
501  const PsqlBindArray& in_bindings,
502  ConsumeResultRowFun process_result_row) {
503  // Execute the prepared statement.
504  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
505 
506  // Iterate over the returned rows and invoke the row consumption
507  // function on each one.
508  int rows = result_set->getRows();
509  for (int row = 0; row < rows; ++row) {
510  try {
511  process_result_row(*result_set, row);
512  } catch (const std::exception& ex) {
513  // Rethrow the exception with a bit more data.
514  isc_throw(BadValue, ex.what() << ". Statement is <" <<
515  statement.text << ">");
516  }
517  }
518 }
519 
520 void
522  const PsqlBindArray& in_bindings) {
523  // Execute the prepared statement.
524  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
525 }
526 
527 uint64_t
529  const PsqlBindArray& in_bindings) {
530  // Execute the prepared statement.
531  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
532 
533  return (boost::lexical_cast<int>(PQcmdTuples(*result_set)));
534 }
535 
536 } // end of isc::db namespace
537 } // end of isc namespace
We want to reuse the database backend connection and exchange code for other uses, in particular for hook libraries.
RAII wrapper for PostgreSQL Result sets.
const Oid types[PGSQL_MAX_PARAMETERS_IN_QUERY]
OID types.
void startTransaction()
Starts new transaction.
void rollbackToSavepoint(const std::string &name)
Rollbacks to the given savepoint.
void commit()
Commits transaction.
const int PGSQL_DEFAULT_CONNECTION_TIMEOUT
std::vector< int > formats_
Vector of "format" for each value.
boost::shared_ptr< PgSqlResult > PgSqlResultPtr
std::vector< int > lengths_
Vector of data lengths for each value.
bool compareError(const PgSqlResult &r, const char *error_state)
Checks a result set&#39;s SQL state against an error state.
STL namespace.
void insertQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings)
Executes INSERT prepared statement.
void selectQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings, ConsumeResultRowFun process_result_row)
Executes SELECT query using prepared statement.
void rowCheck(int row) const
Determines if a row index is valid.
std::string getColumnLabel(const int col) const
Fetches the name of the column in a result set.
virtual const char * what() const
Returns a C-style character string of the cause of the exception.
Exception thrown on failure to open database.
void executeSQL(const std::string &sql)
Executes the an SQL statement.
void commit()
Commits current transaction.
std::vector< const char * > values_
Vector of pointers to the data values.
#define isc_throw(type, stream)
A shortcut macro to insert known values into exception arguments.
A generic exception that is thrown if a parameter given to a method is considered invalid in that con...
void prepareStatements(const PgSqlTaggedStatement *start_statement, const PgSqlTaggedStatement *end_statement)
Prepare statements.
Exception thrown if name of database is not specified.
void createSavepoint(const std::string &name)
Creates a savepoint within the current transaction.
void checkStatementError(const PgSqlResult &r, PgSqlTaggedStatement &statement)
Checks result of the r object.
A generic exception that is thrown when an unexpected error condition occurs.
bool isTransactionStarted() const
Checks if there is a transaction in progress.
void rollback()
Rollbacks current transaction.
PgSqlTransaction(PgSqlConnection &conn)
Constructor.
int version()
returns Kea hooks version.
static void getColumnValue(const PgSqlResult &r, const int row, const size_t col, std::string &value)
Fetches text column value as a string.
Common PgSql Connector Pool.
virtual ~PgSqlConnection()
Destructor.
void prepareStatement(const PgSqlTaggedStatement &statement)
Prepare Single Statement.
const int DB_DBG_TRACE_DETAIL
Database logging levels.
Definition: db_log.cc:21
Defines the logger used by the top-level component of kea-lfc.
Define a PostgreSQL statement.
size_t size() const
Fetches the number of entries in the array.
uint64_t updateDeleteQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings)
Executes UPDATE or DELETE prepared statement and returns the number of affected rows.
DB_LOG & arg(T first, Args... args)
Pass parameters to replace logger placeholders.
Definition: db_log.h:141
#define PGSQL_STATECODE_LEN
void rowColCheck(int row, int col) const
Determines if both a row and column index are valid.
A generic exception that is thrown if a function is called in a prohibited way.
const char * text
Text representation of the actual query.
void colCheck(int col) const
Determines if a column index is valid.
~PgSqlResult()
Destructor.
PgSqlResultPtr executePreparedStatement(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings=PsqlBindArray())
Executes a prepared SQL statement.
Exception thrown when a specific connection has been rendered unusable either through loss of connect...
void openDatabase()
Open Database.
const char * name
Short name of the query.
static std::pair< uint32_t, uint32_t > getVersion(const ParameterMap &parameters)
Get the schema version.
const size_t OID_NONE
Constants for PostgreSQL data types These are defined by PostgreSQL in <catalog/pg_type.h>, but including this file is extraordinarily convoluted, so we&#39;ll use these to fill-in.
std::map< std::string, std::string > ParameterMap
Database configuration parameter map.
PgSqlHolder conn_
PgSql connection handle.
std::function< void(PgSqlResult &, int)> ConsumeResultRowFun
Function invoked to process fetched row.
Exception thrown on failure to execute a database function.
Key is NULL but was specified NOT NULL.
Definition: db_exceptions.h:37
int nbparams
Number of parameters for a given query.
Database duplicate entry error.
Definition: db_exceptions.h:30