Kea  2.3.5-git
pgsql_connection.cc
Go to the documentation of this file.
1 // Copyright (C) 2016-2023 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 std::string
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  unsigned int port = 0;
196  try {
197  setIntParameterValue("port", 0, numeric_limits<uint16_t>::max(), port);
198 
199  } catch (const std::exception& ex) {
201  }
202 
203  // Add port to connection parameters when not default.
204  if (port > 0) {
205  std::ostringstream oss;
206  oss << port;
207  dbconnparameters += " port = " + oss.str();
208  }
209 
210  string suser;
211  try {
212  suser = getParameter("user");
213  dbconnparameters += " user = '" + suser + "'";
214  } catch(...) {
215  // No user. Fine, we'll use NULL
216  }
217 
218  string spassword;
219  try {
220  spassword = getParameter("password");
221  dbconnparameters += " password = '" + spassword + "'";
222  } catch(...) {
223  // No password. Fine, we'll use NULL
224  }
225 
226  string sname;
227  try {
228  sname = getParameter("name");
229  dbconnparameters += " dbname = '" + sname + "'";
230  } catch(...) {
231  // No database name. Throw a "NoDatabaseName" exception
232  isc_throw(NoDatabaseName, "must specify a name for the database");
233  }
234 
235  unsigned int connect_timeout = PGSQL_DEFAULT_CONNECTION_TIMEOUT;
236  unsigned int tcp_user_timeout = 0;
237  try {
238  // The timeout is only valid if greater than zero, as depending on the
239  // database, a zero timeout might signify something like "wait
240  // indefinitely".
241  setIntParameterValue("connect-timeout", 1, numeric_limits<int>::max(), connect_timeout);
242  // This timeout value can be 0, meaning that the database client will
243  // follow a default behavior. Earlier Postgres versions didn't have
244  // this parameter, so we allow 0 to skip setting them for these
245  // earlier versions.
246  setIntParameterValue("tcp-user-timeout", 0, numeric_limits<int>::max(), tcp_user_timeout);
247 
248  } catch (const std::exception& ex) {
250  }
251 
252  // Append timeouts.
253  std::ostringstream oss;
254  oss << " connect_timeout = " << connect_timeout;
255  if (tcp_user_timeout > 0) {
256  oss << " tcp_user_timeout = " << tcp_user_timeout * 1000;
257  }
258  dbconnparameters += oss.str();
259 
260  return (dbconnparameters);
261 }
262 
263 void
265  std::string dbconnparameters = getConnParameters();
266  // Connect to Postgres, saving the low level connection pointer
267  // in the holder object
268  PGconn* new_conn = PQconnectdb(dbconnparameters.c_str());
269  if (!new_conn) {
270  isc_throw(DbOpenError, "could not allocate connection object");
271  }
272 
273  if (PQstatus(new_conn) != CONNECTION_OK) {
274  // If we have a connection object, we have to call finish
275  // to release it, but grab the error message first.
276  std::string error_message = PQerrorMessage(new_conn);
277  PQfinish(new_conn);
278  isc_throw(DbOpenError, error_message);
279  }
280 
281  // We have a valid connection, so let's save it to our holder
282  conn_.setConnection(new_conn);
283 }
284 
285 bool
286 PgSqlConnection::compareError(const PgSqlResult& r, const char* error_state) {
287  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
288  // PostgreSQL guarantees it will always be 5 characters long
289  return ((sqlstate != NULL) &&
290  (memcmp(sqlstate, error_state, PGSQL_STATECODE_LEN) == 0));
291 }
292 
293 void
295  PgSqlTaggedStatement& statement) {
296  int s = PQresultStatus(r);
297  if (s != PGRES_COMMAND_OK && s != PGRES_TUPLES_OK) {
298  // We're testing the first two chars of SQLSTATE, as this is the
299  // error class. Note, there is a severity field, but it can be
300  // misleadingly returned as fatal. However, a loss of connectivity
301  // can lead to a NULL sqlstate with a status of PGRES_FATAL_ERROR.
302  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
303  if ((sqlstate == NULL) ||
304  ((memcmp(sqlstate, "08", 2) == 0) || // Connection Exception
305  (memcmp(sqlstate, "53", 2) == 0) || // Insufficient resources
306  (memcmp(sqlstate, "54", 2) == 0) || // Program Limit exceeded
307  (memcmp(sqlstate, "57", 2) == 0) || // Operator intervention
308  (memcmp(sqlstate, "58", 2) == 0))) { // System error
310  .arg(statement.name)
311  .arg(PQerrorMessage(conn_))
312  .arg(sqlstate ? sqlstate : "<sqlstate null>");
313 
314  // Mark this connection as no longer usable.
315  markUnusable();
316 
317  // Start the connection recovery.
318  startRecoverDbConnection();
319 
320  // We still need to throw so caller can error out of the current
321  // processing.
323  "fatal database error or connectivity lost");
324  }
325 
326  // Failure: check for the special case of duplicate entry.
327  if (compareError(r, PgSqlConnection::DUPLICATE_KEY)) {
328  isc_throw(DuplicateEntry, "statement: " << statement.name
329  << ", reason: " << PQerrorMessage(conn_));
330  }
331 
332  // Failure: check for the special case of null key violation.
333  if (compareError(r, PgSqlConnection::NULL_KEY)) {
334  isc_throw(NullKeyError, "statement: " << statement.name
335  << ", reason: " << PQerrorMessage(conn_));
336  }
337 
338  // Apparently it wasn't fatal, so we throw with a helpful message.
339  const char* error_message = PQerrorMessage(conn_);
340  isc_throw(DbOperationError, "Statement exec failed for: "
341  << statement.name << ", status: " << s
342  << "sqlstate:[ " << (sqlstate ? sqlstate : "<null>")
343  << " ], reason: " << error_message);
344  }
345 }
346 
347 void
349  // If it is nested transaction, do nothing.
350  if (++transaction_ref_count_ > 1) {
351  return;
352  }
353 
355  checkUnusable();
356  PgSqlResult r(PQexec(conn_, "START TRANSACTION"));
357  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
358  const char* error_message = PQerrorMessage(conn_);
359  isc_throw(DbOperationError, "unable to start transaction"
360  << error_message);
361  }
362 }
363 
364 bool
366  return (transaction_ref_count_ > 0);
367 }
368 
369 void
371  if (transaction_ref_count_ <= 0) {
372  isc_throw(Unexpected, "commit called for not started transaction - coding error");
373  }
374 
375  // When committing nested transaction, do nothing.
376  if (--transaction_ref_count_ > 0) {
377  return;
378  }
379 
381  checkUnusable();
382  PgSqlResult r(PQexec(conn_, "COMMIT"));
383  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
384  const char* error_message = PQerrorMessage(conn_);
385  isc_throw(DbOperationError, "commit failed: " << error_message);
386  }
387 }
388 
389 void
391  if (transaction_ref_count_ <= 0) {
392  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
393  }
394 
395  // When rolling back nested transaction, do nothing.
396  if (--transaction_ref_count_ > 0) {
397  return;
398  }
399 
401  checkUnusable();
402  PgSqlResult r(PQexec(conn_, "ROLLBACK"));
403  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
404  const char* error_message = PQerrorMessage(conn_);
405  isc_throw(DbOperationError, "rollback failed: " << error_message);
406  }
407 }
408 
409 void
410 PgSqlConnection::createSavepoint(const std::string& name) {
411  if (transaction_ref_count_ <= 0) {
412  isc_throw(InvalidOperation, "no transaction, cannot create savepoint: " << name);
413  }
414 
416  std::string sql("SAVEPOINT " + name);
417  executeSQL(sql);
418 }
419 
420 void
421 PgSqlConnection::rollbackToSavepoint(const std::string& name) {
422  if (transaction_ref_count_ <= 0) {
423  isc_throw(InvalidOperation, "no transaction, cannot rollback to savepoint: " << name);
424  }
425 
426  std::string sql("ROLLBACK TO SAVEPOINT " + name);
427  executeSQL(sql);
428 }
429 
430 void
431 PgSqlConnection::executeSQL(const std::string& sql) {
432  // Use a TaggedStatement so we can call checkStatementError and ensure
433  // we detect connectivity issues properly.
434  PgSqlTaggedStatement statement({0, {OID_NONE}, "run-statement", sql.c_str()});
435  checkUnusable();
436  PgSqlResult r(PQexec(conn_, statement.text));
437  checkStatementError(r, statement);
438 }
439 
442  const PsqlBindArray& in_bindings) {
443  checkUnusable();
444 
445  if (statement.nbparams != in_bindings.size()) {
446  isc_throw (InvalidOperation, "executePreparedStatement:"
447  << " expected: " << statement.nbparams
448  << " parameters, given: " << in_bindings.size()
449  << ", statement: " << statement.name
450  << ", SQL: " << statement.text);
451  }
452 
453  const char* const* values = 0;
454  const int* lengths = 0;
455  const int* formats = 0;
456  if (statement.nbparams > 0) {
457  values = static_cast<const char* const*>(&in_bindings.values_[0]);
458  lengths = static_cast<const int *>(&in_bindings.lengths_[0]);
459  formats = static_cast<const int *>(&in_bindings.formats_[0]);
460  }
461 
462  PgSqlResultPtr result_set;
463  result_set.reset(new PgSqlResult(PQexecPrepared(conn_, statement.name, statement.nbparams,
464  values, lengths, formats, 0)));
465 
466  checkStatementError(*result_set, statement);
467  return (result_set);
468 }
469 
470 void
472  const PsqlBindArray& in_bindings,
473  ConsumeResultRowFun process_result_row) {
474  // Execute the prepared statement.
475  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
476 
477  // Iterate over the returned rows and invoke the row consumption
478  // function on each one.
479  int rows = result_set->getRows();
480  for (int row = 0; row < rows; ++row) {
481  try {
482  process_result_row(*result_set, row);
483  } catch (const std::exception& ex) {
484  // Rethrow the exception with a bit more data.
485  isc_throw(BadValue, ex.what() << ". Statement is <" <<
486  statement.text << ">");
487  }
488  }
489 }
490 
491 void
493  const PsqlBindArray& in_bindings) {
494  // Execute the prepared statement.
495  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
496 }
497 
498 uint64_t
500  const PsqlBindArray& in_bindings) {
501  // Execute the prepared statement.
502  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
503 
504  return (boost::lexical_cast<int>(PQcmdTuples(*result_set)));
505 }
506 
507 template<typename T>
508 void
509 PgSqlConnection::setIntParameterValue(const std::string& name, int64_t min, int64_t max, T& value) {
510  string svalue;
511  try {
512  svalue = getParameter(name);
513  } catch (...) {
514  // Do nothing if the parameter is not present.
515  }
516  if (svalue.empty()) {
517  return;
518  }
519  try {
520  // Try to convert the value.
521  auto parsed_value = boost::lexical_cast<T>(svalue);
522  // Check if the value is within the specified range.
523  if ((parsed_value < min) || (parsed_value > max)) {
524  isc_throw(BadValue, "bad " << svalue << " value");
525  }
526  // Everything is fine. Return the parsed value.
527  value = parsed_value;
528 
529  } catch (...) {
530  // We may end up here when lexical_cast fails or when the
531  // parsed value is not within the desired range. In both
532  // cases let's throw the same general error.
533  isc_throw(BadValue, name << " parameter (" <<
534  svalue << ") must be an integer between "
535  << min << " and " << max);
536  }
537 }
538 
539 
540 } // end of isc::db namespace
541 } // 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.
Invalid port number.
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.
std::string getConnParameters()
Creates connection string from specified parameters.
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