Kea  2.5.3
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.openDatabaseInternal(false);
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;
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  return (getConnParametersInternal(false));
186 }
187 
188 std::string
189 PgSqlConnection::getConnParametersInternal(bool logging) {
190  string dbconnparameters;
191  string shost = "localhost";
192  try {
193  shost = getParameter("host");
194  } catch(...) {
195  // No host. Fine, we'll use "localhost"
196  }
197 
198  dbconnparameters += "host = '" + shost + "'" ;
199 
200  unsigned int port = 0;
201  try {
202  setIntParameterValue("port", 0, numeric_limits<uint16_t>::max(), port);
203 
204  } catch (const std::exception& ex) {
205  isc_throw(DbInvalidPort, ex.what());
206  }
207 
208  // Add port to connection parameters when not default.
209  if (port > 0) {
210  std::ostringstream oss;
211  oss << port;
212  dbconnparameters += " port = " + oss.str();
213  }
214 
215  string suser;
216  try {
217  suser = getParameter("user");
218  dbconnparameters += " user = '" + suser + "'";
219  } catch(...) {
220  // No user. Fine, we'll use NULL
221  }
222 
223  string spassword;
224  try {
225  spassword = getParameter("password");
226  dbconnparameters += " password = '" + spassword + "'";
227  } catch(...) {
228  // No password. Fine, we'll use NULL
229  }
230 
231  string sname;
232  try {
233  sname = getParameter("name");
234  dbconnparameters += " dbname = '" + sname + "'";
235  } catch(...) {
236  // No database name. Throw a "NoDatabaseName" exception
237  isc_throw(NoDatabaseName, "must specify a name for the database");
238  }
239 
240  unsigned int connect_timeout = PGSQL_DEFAULT_CONNECTION_TIMEOUT;
241  unsigned int tcp_user_timeout = 0;
242  try {
243  // The timeout is only valid if greater than zero, as depending on the
244  // database, a zero timeout might signify something like "wait
245  // indefinitely".
246  setIntParameterValue("connect-timeout", 1, numeric_limits<int>::max(), connect_timeout);
247  // This timeout value can be 0, meaning that the database client will
248  // follow a default behavior. Earlier Postgres versions didn't have
249  // this parameter, so we allow 0 to skip setting them for these
250  // earlier versions.
251  setIntParameterValue("tcp-user-timeout", 0, numeric_limits<int>::max(), tcp_user_timeout);
252 
253  } catch (const std::exception& ex) {
254  isc_throw(DbInvalidTimeout, ex.what());
255  }
256 
257  // Append connection timeout.
258  std::ostringstream oss;
259  oss << " connect_timeout = " << connect_timeout;
260 
261  if (tcp_user_timeout > 0) {
262 // tcp_user_timeout parameter is a PostgreSQL 12+ feature.
263 #ifdef HAVE_PGSQL_TCP_USER_TIMEOUT
264  oss << " tcp_user_timeout = " << tcp_user_timeout * 1000;
265  static_cast<void>(logging);
266 #else
267  if (logging) {
268  DB_LOG_WARN(PGSQL_TCP_USER_TIMEOUT_UNSUPPORTED).arg();
269  }
270 #endif
271  }
272  dbconnparameters += oss.str();
273 
274  return (dbconnparameters);
275 }
276 
277 void
279  openDatabaseInternal(true);
280 }
281 
282 void
283 PgSqlConnection::openDatabaseInternal(bool logging) {
284  std::string dbconnparameters = getConnParametersInternal(logging);
285  // Connect to Postgres, saving the low level connection pointer
286  // in the holder object
287  PGconn* new_conn = PQconnectdb(dbconnparameters.c_str());
288  if (!new_conn) {
289  isc_throw(DbOpenError, "could not allocate connection object");
290  }
291 
292  if (PQstatus(new_conn) != CONNECTION_OK) {
293  // If we have a connection object, we have to call finish
294  // to release it, but grab the error message first.
295  std::string error_message = PQerrorMessage(new_conn);
296  PQfinish(new_conn);
297  isc_throw(DbOpenError, error_message);
298  }
299 
300  // We have a valid connection, so let's save it to our holder
301  conn_.setConnection(new_conn);
302 }
303 
304 bool
305 PgSqlConnection::compareError(const PgSqlResult& r, const char* error_state) {
306  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
307  // PostgreSQL guarantees it will always be 5 characters long
308  return ((sqlstate != NULL) &&
309  (memcmp(sqlstate, error_state, PGSQL_STATECODE_LEN) == 0));
310 }
311 
312 void
314  PgSqlTaggedStatement& statement) {
315  int s = PQresultStatus(r);
316  if (s != PGRES_COMMAND_OK && s != PGRES_TUPLES_OK) {
317  // We're testing the first two chars of SQLSTATE, as this is the
318  // error class. Note, there is a severity field, but it can be
319  // misleadingly returned as fatal. However, a loss of connectivity
320  // can lead to a NULL sqlstate with a status of PGRES_FATAL_ERROR.
321  const char* sqlstate = PQresultErrorField(r, PG_DIAG_SQLSTATE);
322  if ((sqlstate == NULL) ||
323  ((memcmp(sqlstate, "08", 2) == 0) || // Connection Exception
324  (memcmp(sqlstate, "53", 2) == 0) || // Insufficient resources
325  (memcmp(sqlstate, "54", 2) == 0) || // Program Limit exceeded
326  (memcmp(sqlstate, "57", 2) == 0) || // Operator intervention
327  (memcmp(sqlstate, "58", 2) == 0))) { // System error
329  .arg(statement.name)
330  .arg(PQerrorMessage(conn_))
331  .arg(sqlstate ? sqlstate : "<sqlstate null>");
332 
333  // Mark this connection as no longer usable.
334  markUnusable();
335 
336  // Start the connection recovery.
338 
339  // We still need to throw so caller can error out of the current
340  // processing.
342  "fatal database error or connectivity lost");
343  }
344 
345  // Failure: check for the special case of duplicate entry.
347  isc_throw(DuplicateEntry, "statement: " << statement.name
348  << ", reason: " << PQerrorMessage(conn_));
349  }
350 
351  // Failure: check for the special case of null key violation.
353  isc_throw(NullKeyError, "statement: " << statement.name
354  << ", reason: " << PQerrorMessage(conn_));
355  }
356 
357  // Apparently it wasn't fatal, so we throw with a helpful message.
358  const char* error_message = PQerrorMessage(conn_);
359  isc_throw(DbOperationError, "Statement exec failed for: "
360  << statement.name << ", status: " << s
361  << "sqlstate:[ " << (sqlstate ? sqlstate : "<null>")
362  << " ], reason: " << error_message);
363  }
364 }
365 
366 void
368  // If it is nested transaction, do nothing.
369  if (++transaction_ref_count_ > 1) {
370  return;
371  }
372 
374  checkUnusable();
375  PgSqlResult r(PQexec(conn_, "START TRANSACTION"));
376  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
377  const char* error_message = PQerrorMessage(conn_);
378  isc_throw(DbOperationError, "unable to start transaction"
379  << error_message);
380  }
381 }
382 
383 bool
385  return (transaction_ref_count_ > 0);
386 }
387 
388 void
390  if (transaction_ref_count_ <= 0) {
391  isc_throw(Unexpected, "commit called for not started transaction - coding error");
392  }
393 
394  // When committing nested transaction, do nothing.
395  if (--transaction_ref_count_ > 0) {
396  return;
397  }
398 
400  checkUnusable();
401  PgSqlResult r(PQexec(conn_, "COMMIT"));
402  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
403  const char* error_message = PQerrorMessage(conn_);
404  isc_throw(DbOperationError, "commit failed: " << error_message);
405  }
406 }
407 
408 void
410  if (transaction_ref_count_ <= 0) {
411  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
412  }
413 
414  // When rolling back nested transaction, do nothing.
415  if (--transaction_ref_count_ > 0) {
416  return;
417  }
418 
420  checkUnusable();
421  PgSqlResult r(PQexec(conn_, "ROLLBACK"));
422  if (PQresultStatus(r) != PGRES_COMMAND_OK) {
423  const char* error_message = PQerrorMessage(conn_);
424  isc_throw(DbOperationError, "rollback failed: " << error_message);
425  }
426 }
427 
428 void
429 PgSqlConnection::createSavepoint(const std::string& name) {
430  if (transaction_ref_count_ <= 0) {
431  isc_throw(InvalidOperation, "no transaction, cannot create savepoint: " << name);
432  }
433 
435  std::string sql("SAVEPOINT " + name);
436  executeSQL(sql);
437 }
438 
439 void
440 PgSqlConnection::rollbackToSavepoint(const std::string& name) {
441  if (transaction_ref_count_ <= 0) {
442  isc_throw(InvalidOperation, "no transaction, cannot rollback to savepoint: " << name);
443  }
444 
445  std::string sql("ROLLBACK TO SAVEPOINT " + name);
446  executeSQL(sql);
447 }
448 
449 void
450 PgSqlConnection::executeSQL(const std::string& sql) {
451  // Use a TaggedStatement so we can call checkStatementError and ensure
452  // we detect connectivity issues properly.
453  PgSqlTaggedStatement statement({0, {OID_NONE}, "run-statement", sql.c_str()});
454  checkUnusable();
455  PgSqlResult r(PQexec(conn_, statement.text));
456  checkStatementError(r, statement);
457 }
458 
461  const PsqlBindArray& in_bindings) {
462  checkUnusable();
463 
464  if (statement.nbparams != in_bindings.size()) {
465  isc_throw (InvalidOperation, "executePreparedStatement:"
466  << " expected: " << statement.nbparams
467  << " parameters, given: " << in_bindings.size()
468  << ", statement: " << statement.name
469  << ", SQL: " << statement.text);
470  }
471 
472  const char* const* values = 0;
473  const int* lengths = 0;
474  const int* formats = 0;
475  if (statement.nbparams > 0) {
476  values = static_cast<const char* const*>(&in_bindings.values_[0]);
477  lengths = static_cast<const int *>(&in_bindings.lengths_[0]);
478  formats = static_cast<const int *>(&in_bindings.formats_[0]);
479  }
480 
481  PgSqlResultPtr result_set;
482  result_set.reset(new PgSqlResult(PQexecPrepared(conn_, statement.name, statement.nbparams,
483  values, lengths, formats, 0)));
484 
485  checkStatementError(*result_set, statement);
486  return (result_set);
487 }
488 
489 void
491  const PsqlBindArray& in_bindings,
492  ConsumeResultRowFun process_result_row) {
493  // Execute the prepared statement.
494  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
495 
496  // Iterate over the returned rows and invoke the row consumption
497  // function on each one.
498  int rows = result_set->getRows();
499  for (int row = 0; row < rows; ++row) {
500  try {
501  process_result_row(*result_set, row);
502  } catch (const std::exception& ex) {
503  // Rethrow the exception with a bit more data.
504  isc_throw(BadValue, ex.what() << ". Statement is <" <<
505  statement.text << ">");
506  }
507  }
508 }
509 
510 void
512  const PsqlBindArray& in_bindings) {
513  // Execute the prepared statement.
514  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
515 }
516 
517 uint64_t
519  const PsqlBindArray& in_bindings) {
520  // Execute the prepared statement.
521  PgSqlResultPtr result_set = executePreparedStatement(statement, in_bindings);
522 
523  return (boost::lexical_cast<int>(PQcmdTuples(*result_set)));
524 }
525 
526 template<typename T>
527 void
528 PgSqlConnection::setIntParameterValue(const std::string& name, int64_t min, int64_t max, T& value) {
529  string svalue;
530  try {
531  svalue = getParameter(name);
532  } catch (...) {
533  // Do nothing if the parameter is not present.
534  }
535  if (svalue.empty()) {
536  return;
537  }
538  try {
539  // Try to convert the value.
540  auto parsed_value = boost::lexical_cast<T>(svalue);
541  // Check if the value is within the specified range.
542  if ((parsed_value < min) || (parsed_value > max)) {
543  isc_throw(BadValue, "bad " << svalue << " value");
544  }
545  // Everything is fine. Return the parsed value.
546  value = parsed_value;
547 
548  } catch (...) {
549  // We may end up here when lexical_cast fails or when the
550  // parsed value is not within the desired range. In both
551  // cases let's throw the same general error.
552  isc_throw(BadValue, name << " parameter (" <<
553  svalue << ") must be an integer between "
554  << min << " and " << max);
555  }
556 }
557 
558 
559 } // end of isc::db namespace
560 } // end of isc namespace
int version()
returns Kea hooks version.
A generic exception that is thrown if a parameter given to a method is considered invalid in that con...
virtual const char * what() const
Returns a C-style character string of the cause of the exception.
A generic exception that is thrown if a function is called in a prohibited way.
A generic exception that is thrown when an unexpected error condition occurs.
std::string getParameter(const std::string &name) const
Returns value of a connection parameter.
void markUnusable()
Sets the unusable flag to true.
void checkUnusable()
Throws an exception if the connection is not usable.
std::map< std::string, std::string > ParameterMap
Database configuration parameter map.
Exception thrown when a specific connection has been rendered unusable either through loss of connect...
Exception thrown on failure to open database.
Exception thrown on failure to execute a database function.
Database duplicate entry error.
Definition: db_exceptions.h:30
Key is NULL but was specified NOT NULL.
Definition: db_exceptions.h:37
Common PgSql Connector Pool.
void startTransaction()
Starts new transaction.
void rollback()
Rollbacks current transaction.
void createSavepoint(const std::string &name)
Creates a savepoint within the current transaction.
uint64_t updateDeleteQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings)
Executes UPDATE or DELETE prepared statement and returns the number of affected rows.
int transaction_ref_count_
Reference counter for transactions.
void selectQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings, ConsumeResultRowFun process_result_row)
Executes SELECT query using prepared statement.
bool compareError(const PgSqlResult &r, const char *error_state)
Checks a result set's SQL state against an error state.
std::string getConnParameters()
Creates connection string from specified parameters.
static const char NULL_KEY[]
Define the PgSql error state for a null foreign key error.
std::function< void(PgSqlResult &, int)> ConsumeResultRowFun
Function invoked to process fetched row.
void prepareStatement(const PgSqlTaggedStatement &statement)
Prepare Single Statement.
static const char DUPLICATE_KEY[]
Define the PgSql error state for a duplicate key error.
PgSqlResultPtr executePreparedStatement(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings=PsqlBindArray())
Executes a prepared SQL statement.
bool isTransactionStarted() const
Checks if there is a transaction in progress.
static std::pair< uint32_t, uint32_t > getVersion(const ParameterMap &parameters)
Get the schema version.
PgSqlHolder conn_
PgSql connection handle.
void rollbackToSavepoint(const std::string &name)
Rollbacks to the given savepoint.
void startRecoverDbConnection()
The recover connection.
void insertQuery(PgSqlTaggedStatement &statement, const PsqlBindArray &in_bindings)
Executes INSERT prepared statement.
void commit()
Commits current transaction.
void executeSQL(const std::string &sql)
Executes the an SQL statement.
virtual ~PgSqlConnection()
Destructor.
void checkStatementError(const PgSqlResult &r, PgSqlTaggedStatement &statement)
Checks result of the r object.
void prepareStatements(const PgSqlTaggedStatement *start_statement, const PgSqlTaggedStatement *end_statement)
Prepare statements.
void openDatabase()
Open database with logging.
static void getColumnValue(const PgSqlResult &r, const int row, const size_t col, std::string &value)
Fetches text column value as a string.
void setConnection(PGconn *connection)
Sets the connection to the value given.
RAII wrapper for PostgreSQL Result sets.
void colCheck(int col) const
Determines if a column index is valid.
void rowCheck(int row) const
Determines if a row index is valid.
void rowColCheck(int row, int col) const
Determines if both a row and column index are valid.
std::string getColumnLabel(const int col) const
Fetches the name of the column in a result set.
PgSqlTransaction(PgSqlConnection &conn)
Constructor.
void commit()
Commits transaction.
We want to reuse the database backend connection and exchange code for other uses,...
#define isc_throw(type, stream)
A shortcut macro to insert known values into exception arguments.
const int DB_DBG_TRACE_DETAIL
Database logging levels.
Definition: db_log.cc:21
const int PGSQL_DEFAULT_CONNECTION_TIMEOUT
@ PGSQL_CREATE_SAVEPOINT
Definition: db_log.h:59
@ PGSQL_ROLLBACK
Definition: db_log.h:58
@ PGSQL_TCP_USER_TIMEOUT_UNSUPPORTED
Definition: db_log.h:61
@ PGSQL_COMMIT
Definition: db_log.h:57
@ PGSQL_START_TRANSACTION
Definition: db_log.h:56
@ PGSQL_FATAL_ERROR
Definition: db_log.h:55
@ PGSQL_DEALLOC_ERROR
Definition: db_log.h:54
boost::shared_ptr< PgSqlResult > PgSqlResultPtr
const size_t OID_NONE
Constants for PostgreSQL data types These are defined by PostgreSQL in <catalog/pg_type....
Defines the logger used by the top-level component of kea-lfc.
#define PGSQL_STATECODE_LEN
DB_LOG & arg(T first, Args... args)
Pass parameters to replace logger placeholders.
Definition: db_log.h:142
Define a PostgreSQL statement.
int nbparams
Number of parameters for a given query.
const char * text
Text representation of the actual query.
const char * name
Short name of the query.
const Oid types[PGSQL_MAX_PARAMETERS_IN_QUERY]
OID types.
std::vector< const char * > values_
Vector of pointers to the data values.
std::vector< int > formats_
Vector of "format" for each value.
size_t size() const
Fetches the number of entries in the array.
std::vector< int > lengths_
Vector of data lengths for each value.