2 32.1. Database Connection Control Functions #
4 32.1.1. Connection Strings
5 32.1.2. Parameter Key Words
7 The following functions deal with making a connection to a PostgreSQL
8 backend server. An application program can have several backend
9 connections open at one time. (One reason to do that is to access more
10 than one database.) Each connection is represented by a PGconn object,
11 which is obtained from the function PQconnectdb, PQconnectdbParams, or
12 PQsetdbLogin. Note that these functions will always return a non-null
13 object pointer, unless perhaps there is too little memory even to
14 allocate the PGconn object. The PQstatus function should be called to
15 check the return value for a successful connection before queries are
16 sent via the connection object.
20 If untrusted users have access to a database that has not adopted a
21 secure schema usage pattern, begin each session by removing
22 publicly-writable schemas from search_path. One can set parameter key
23 word options to value -csearch_path=. Alternately, one can issue
24 PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)")
25 after connecting. This consideration is not specific to libpq; it
26 applies to every interface for executing arbitrary SQL commands.
30 On Unix, forking a process with open libpq connections can lead to
31 unpredictable results because the parent and child processes share the
32 same sockets and operating system resources. For this reason, such
33 usage is not recommended, though doing an exec from the child process
34 to load a new executable is safe.
37 Makes a new connection to the database server.
39 PGconn *PQconnectdbParams(const char * const *keywords,
40 const char * const *values,
43 This function opens a new database connection using the
44 parameters taken from two NULL-terminated arrays. The first,
45 keywords, is defined as an array of strings, each one being a
46 key word. The second, values, gives the value for each key word.
47 Unlike PQsetdbLogin below, the parameter set can be extended
48 without changing the function signature, so use of this function
49 (or its nonblocking analogs PQconnectStartParams and
50 PQconnectPoll) is preferred for new application programming.
52 The currently recognized parameter key words are listed in
55 The passed arrays can be empty to use all default parameters, or
56 can contain one or more parameter settings. They must be matched
57 in length. Processing will stop at the first NULL entry in the
58 keywords array. Also, if the values entry associated with a
59 non-NULL keywords entry is NULL or an empty string, that entry
60 is ignored and processing continues with the next pair of array
63 When expand_dbname is non-zero, the value for the first dbname
64 key word is checked to see if it is a connection string. If so,
65 it is “expanded” into the individual connection parameters
66 extracted from the string. The value is considered to be a
67 connection string, rather than just a database name, if it
68 contains an equal sign (=) or it begins with a URI scheme
69 designator. (More details on connection string formats appear in
70 Section 32.1.1.) Only the first occurrence of dbname is treated
71 in this way; any subsequent dbname parameter is processed as a
74 In general the parameter arrays are processed from start to end.
75 If any key word is repeated, the last value (that is not NULL or
76 empty) is used. This rule applies in particular when a key word
77 found in a connection string conflicts with one appearing in the
78 keywords array. Thus, the programmer may determine whether array
79 entries can override or be overridden by values taken from a
80 connection string. Array entries appearing before an expanded
81 dbname entry can be overridden by fields of the connection
82 string, and in turn those fields are overridden by array entries
83 appearing after dbname (but, again, only if those entries supply
86 After processing all the array entries and any expanded
87 connection string, any connection parameters that remain unset
88 are filled with default values. If an unset parameter's
89 corresponding environment variable (see Section 32.15) is set,
90 its value is used. If the environment variable is not set
91 either, then the parameter's built-in default value is used.
94 Makes a new connection to the database server.
96 PGconn *PQconnectdb(const char *conninfo);
98 This function opens a new database connection using the
99 parameters taken from the string conninfo.
101 The passed string can be empty to use all default parameters, or
102 it can contain one or more parameter settings separated by
103 whitespace, or it can contain a URI. See Section 32.1.1 for
107 Makes a new connection to the database server.
109 PGconn *PQsetdbLogin(const char *pghost,
111 const char *pgoptions,
117 This is the predecessor of PQconnectdb with a fixed set of
118 parameters. It has the same functionality except that the
119 missing parameters will always take on default values. Write
120 NULL or an empty string for any one of the fixed parameters that
123 If the dbName contains an = sign or has a valid connection URI
124 prefix, it is taken as a conninfo string in exactly the same way
125 as if it had been passed to PQconnectdb, and the remaining
126 parameters are then applied as specified for PQconnectdbParams.
128 pgtty is no longer used and any value passed will be ignored.
131 Makes a new connection to the database server.
133 PGconn *PQsetdb(char *pghost,
139 This is a macro that calls PQsetdbLogin with null pointers for
140 the login and pwd parameters. It is provided for backward
141 compatibility with very old programs.
146 Make a connection to the database server in a nonblocking
149 PGconn *PQconnectStartParams(const char * const *keywords,
150 const char * const *values,
153 PGconn *PQconnectStart(const char *conninfo);
155 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
157 These three functions are used to open a connection to a
158 database server such that your application's thread of execution
159 is not blocked on remote I/O whilst doing so. The point of this
160 approach is that the waits for I/O to complete can occur in the
161 application's main loop, rather than down inside
162 PQconnectdbParams or PQconnectdb, and so the application can
163 manage this operation in parallel with other activities.
165 With PQconnectStartParams, the database connection is made using
166 the parameters taken from the keywords and values arrays, and
167 controlled by expand_dbname, as described above for
170 With PQconnectStart, the database connection is made using the
171 parameters taken from the string conninfo as described above for
174 Neither PQconnectStartParams nor PQconnectStart nor
175 PQconnectPoll will block, so long as a number of restrictions
178 + The hostaddr parameter must be used appropriately to prevent
179 DNS queries from being made. See the documentation of this
180 parameter in Section 32.1.2 for details.
181 + If you call PQtrace, ensure that the stream object into which
182 you trace will not block.
183 + You must ensure that the socket is in the appropriate state
184 before calling PQconnectPoll, as described below.
186 To begin a nonblocking connection request, call PQconnectStart
187 or PQconnectStartParams. If the result is null, then libpq has
188 been unable to allocate a new PGconn structure. Otherwise, a
189 valid PGconn pointer is returned (though not yet representing a
190 valid connection to the database). Next call PQstatus(conn). If
191 the result is CONNECTION_BAD, the connection attempt has already
192 failed, typically because of invalid connection parameters.
194 If PQconnectStart or PQconnectStartParams succeeds, the next
195 stage is to poll libpq so that it can proceed with the
196 connection sequence. Use PQsocket(conn) to obtain the descriptor
197 of the socket underlying the database connection. (Caution: do
198 not assume that the socket remains the same across PQconnectPoll
199 calls.) Loop thus: If PQconnectPoll(conn) last returned
200 PGRES_POLLING_READING, wait until the socket is ready to read
201 (as indicated by select(), poll(), or similar system function).
202 Note that PQsocketPoll can help reduce boilerplate by
203 abstracting the setup of select(2) or poll(2) if it is available
204 on your system. Then call PQconnectPoll(conn) again. Conversely,
205 if PQconnectPoll(conn) last returned PGRES_POLLING_WRITING, wait
206 until the socket is ready to write, then call
207 PQconnectPoll(conn) again. On the first iteration, i.e., if you
208 have yet to call PQconnectPoll, behave as if it last returned
209 PGRES_POLLING_WRITING. Continue this loop until
210 PQconnectPoll(conn) returns PGRES_POLLING_FAILED, indicating the
211 connection procedure has failed, or PGRES_POLLING_OK, indicating
212 the connection has been successfully made.
214 At any time during connection, the status of the connection can
215 be checked by calling PQstatus. If this call returns
216 CONNECTION_BAD, then the connection procedure has failed; if the
217 call returns CONNECTION_OK, then the connection is ready. Both
218 of these states are equally detectable from the return value of
219 PQconnectPoll, described above. Other states might also occur
220 during (and only during) an asynchronous connection procedure.
221 These indicate the current stage of the connection procedure and
222 might be useful to provide feedback to the user for example.
226 Waiting for connection to be made.
229 Connection OK; waiting to send.
231 CONNECTION_AWAITING_RESPONSE #
232 Waiting for a response from the server.
235 Received authentication; waiting for backend start-up to
238 CONNECTION_SSL_STARTUP #
239 Negotiating SSL encryption.
241 CONNECTION_GSS_STARTUP #
242 Negotiating GSS encryption.
244 CONNECTION_CHECK_WRITABLE #
245 Checking if connection is able to handle write
248 CONNECTION_CHECK_STANDBY #
249 Checking if connection is to a server in standby mode.
252 Consuming any remaining response messages on connection.
254 Note that, although these constants will remain (in order to
255 maintain compatibility), an application should never rely upon
256 these occurring in a particular order, or at all, or on the
257 status always being one of these documented values. An
258 application might do something like this:
260 switch(PQstatus(conn))
262 case CONNECTION_STARTED:
263 feedback = "Connecting...";
266 case CONNECTION_MADE:
267 feedback = "Connected to server...";
273 feedback = "Connecting...";
276 The connect_timeout connection parameter is ignored when using
277 PQconnectPoll; it is the application's responsibility to decide
278 whether an excessive amount of time has elapsed. Otherwise,
279 PQconnectStart followed by a PQconnectPoll loop is equivalent to
282 Note that when PQconnectStart or PQconnectStartParams returns a
283 non-null pointer, you must call PQfinish when you are finished
284 with it, in order to dispose of the structure and any associated
285 memory blocks. This must be done even if the connection attempt
286 fails or is abandoned.
289 Poll a connection's underlying socket descriptor retrieved with
290 PQsocket. The primary use of this function is iterating through
291 the connection sequence described in the documentation of
292 PQconnectStartParams.
294 typedef int64_t pg_usec_time_t;
296 int PQsocketPoll(int sock, int forRead, int forWrite,
297 pg_usec_time_t end_time);
299 This function performs polling of a file descriptor, optionally
300 with a timeout. If forRead is nonzero, the function will
301 terminate when the socket is ready for reading. If forWrite is
302 nonzero, the function will terminate when the socket is ready
305 The timeout is specified by end_time, which is the time to stop
306 waiting expressed as a number of microseconds since the Unix
307 epoch (that is, time_t times 1 million). Timeout is infinite if
308 end_time is -1. Timeout is immediate (no blocking) if end_time
309 is 0 (or indeed, any time before now). Timeout values can be
310 calculated conveniently by adding the desired number of
311 microseconds to the result of PQgetCurrentTimeUSec. Note that
312 the underlying system calls may have less than microsecond
313 precision, so that the actual delay may be imprecise.
315 The function returns a value greater than 0 if the specified
316 condition is met, 0 if a timeout occurred, or -1 if an error
317 occurred. The error can be retrieved by checking the errno(3)
318 value. In the event both forRead and forWrite are zero, the
319 function immediately returns a timeout indication.
321 PQsocketPoll is implemented using either poll(2) or select(2),
322 depending on platform. See POLLIN and POLLOUT from poll(2), or
323 readfds and writefds from select(2), for more information.
326 Returns the default connection options.
328 PQconninfoOption *PQconndefaults(void);
332 char *keyword; /* The keyword of the option */
333 char *envvar; /* Fallback environment variable name */
334 char *compiled; /* Fallback compiled in default value */
335 char *val; /* Option's current value, or NULL */
336 char *label; /* Label for field in connect dialog */
337 char *dispchar; /* Indicates how to display this field
338 in a connect dialog. Values are:
339 "" Display entered value as is
340 "*" Password field - hide value
341 "D" Debug option - don't show by default */
342 int dispsize; /* Field size in characters for dialog */
345 Returns a connection options array. This can be used to
346 determine all possible PQconnectdb options and their current
347 default values. The return value points to an array of
348 PQconninfoOption structures, which ends with an entry having a
349 null keyword pointer. The null pointer is returned if memory
350 could not be allocated. Note that the current default values
351 (val fields) will depend on environment variables and other
352 context. A missing or invalid service file will be silently
353 ignored. Callers must treat the connection options data as
356 After processing the options array, free it by passing it to
357 PQconninfoFree. If this is not done, a small amount of memory is
358 leaked for each call to PQconndefaults.
361 Returns the connection options used by a live connection.
363 PQconninfoOption *PQconninfo(PGconn *conn);
365 Returns a connection options array. This can be used to
366 determine all possible PQconnectdb options and the values that
367 were used to connect to the server. The return value points to
368 an array of PQconninfoOption structures, which ends with an
369 entry having a null keyword pointer. All notes above for
370 PQconndefaults also apply to the result of PQconninfo.
373 Returns parsed connection options from the provided connection
376 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
378 Parses a connection string and returns the resulting options as
379 an array; or returns NULL if there is a problem with the
380 connection string. This function can be used to extract the
381 PQconnectdb options in the provided connection string. The
382 return value points to an array of PQconninfoOption structures,
383 which ends with an entry having a null keyword pointer.
385 All legal options will be present in the result array, but the
386 PQconninfoOption for any option not present in the connection
387 string will have val set to NULL; default values are not
390 If errmsg is not NULL, then *errmsg is set to NULL on success,
391 else to a malloc'd error string explaining the problem. (It is
392 also possible for *errmsg to be set to NULL and the function to
393 return NULL; this indicates an out-of-memory condition.)
395 After processing the options array, free it by passing it to
396 PQconninfoFree. If this is not done, some memory is leaked for
397 each call to PQconninfoParse. Conversely, if an error occurs and
398 errmsg is not NULL, be sure to free the error string using
402 Closes the connection to the server. Also frees memory used by
405 void PQfinish(PGconn *conn);
407 Note that even if the server connection attempt fails (as
408 indicated by PQstatus), the application should call PQfinish to
409 free the memory used by the PGconn object. The PGconn pointer
410 must not be used again after PQfinish has been called.
413 Resets the communication channel to the server.
415 void PQreset(PGconn *conn);
417 This function will close the connection to the server and
418 attempt to establish a new connection, using all the same
419 parameters previously used. This might be useful for error
420 recovery if a working connection is lost.
424 Reset the communication channel to the server, in a nonblocking
427 int PQresetStart(PGconn *conn);
429 PostgresPollingStatusType PQresetPoll(PGconn *conn);
431 These functions will close the connection to the server and
432 attempt to establish a new connection, using all the same
433 parameters previously used. This can be useful for error
434 recovery if a working connection is lost. They differ from
435 PQreset (above) in that they act in a nonblocking manner. These
436 functions suffer from the same restrictions as
437 PQconnectStartParams, PQconnectStart and PQconnectPoll.
439 To initiate a connection reset, call PQresetStart. If it returns
440 0, the reset has failed. If it returns 1, poll the reset using
441 PQresetPoll in exactly the same way as you would create the
442 connection using PQconnectPoll.
445 PQpingParams reports the status of the server. It accepts
446 connection parameters identical to those of PQconnectdbParams,
447 described above. It is not necessary to supply correct user
448 name, password, or database name values to obtain the server
449 status; however, if incorrect values are provided, the server
450 will log a failed connection attempt.
452 PGPing PQpingParams(const char * const *keywords,
453 const char * const *values,
456 The function returns one of the following values:
459 The server is running and appears to be accepting
463 The server is running but is in a state that disallows
464 connections (startup, shutdown, or crash recovery).
467 The server could not be contacted. This might indicate
468 that the server is not running, or that there is something
469 wrong with the given connection parameters (for example,
470 wrong port number), or that there is a network
471 connectivity problem (for example, a firewall blocking the
475 No attempt was made to contact the server, because the
476 supplied parameters were obviously incorrect or there was
477 some client-side problem (for example, out of memory).
480 PQping reports the status of the server. It accepts connection
481 parameters identical to those of PQconnectdb, described above.
482 It is not necessary to supply correct user name, password, or
483 database name values to obtain the server status; however, if
484 incorrect values are provided, the server will log a failed
487 PGPing PQping(const char *conninfo);
489 The return values are the same as for PQpingParams.
491 PQsetSSLKeyPassHook_OpenSSL #
492 PQsetSSLKeyPassHook_OpenSSL lets an application override libpq's
493 default handling of encrypted client certificate key files using
494 sslpassword or interactive prompting.
496 void PQsetSSLKeyPassHook_OpenSSL(PQsslKeyPassHook_OpenSSL_type hook);
498 The application passes a pointer to a callback function with
501 int callback_fn(char *buf, int size, PGconn *conn);
503 which libpq will then call instead of its default
504 PQdefaultSSLKeyPassHook_OpenSSL handler. The callback should
505 determine the password for the key and copy it to result-buffer
506 buf of size size. The string in buf must be null-terminated. The
507 callback must return the length of the password stored in buf
508 excluding the null terminator. On failure, the callback should
509 set buf[0] = '\0' and return 0. See
510 PQdefaultSSLKeyPassHook_OpenSSL in libpq's source code for an
513 If the user specified an explicit key location, its path will be
514 in conn->sslkey when the callback is invoked. This will be empty
515 if the default key path is being used. For keys that are engine
516 specifiers, it is up to engine implementations whether they use
517 the OpenSSL password callback or define their own handling.
519 The app callback may choose to delegate unhandled cases to
520 PQdefaultSSLKeyPassHook_OpenSSL, or call it first and try
521 something else if it returns 0, or completely override it.
523 The callback must not escape normal flow control with
524 exceptions, longjmp(...), etc. It must return normally.
526 PQgetSSLKeyPassHook_OpenSSL #
527 PQgetSSLKeyPassHook_OpenSSL returns the current client
528 certificate key password hook, or NULL if none has been set.
530 PQsslKeyPassHook_OpenSSL_type PQgetSSLKeyPassHook_OpenSSL(void);
532 32.1.1. Connection Strings #
534 Several libpq functions parse a user-specified string to obtain
535 connection parameters. There are two accepted formats for these
536 strings: plain keyword/value strings and URIs. URIs generally follow
537 RFC 3986, except that multi-host connection strings are allowed as
538 further described below.
540 32.1.1.1. Keyword/Value Connection Strings #
542 In the keyword/value format, each parameter setting is in the form
543 keyword = value, with space(s) between settings. Spaces around a
544 setting's equal sign are optional. To write an empty value, or a value
545 containing spaces, surround it with single quotes, for example keyword
546 = 'a value'. Single quotes and backslashes within a value must be
547 escaped with a backslash, i.e., \' and \\.
550 host=localhost port=5432 dbname=mydb connect_timeout=10
552 The recognized parameter key words are listed in Section 32.1.2.
554 32.1.1.2. Connection URIs #
556 The general form for a connection URI is:
557 postgresql://[userspec@][hostspec][/dbname][?paramspec]
571 The URI scheme designator can be either postgresql:// or postgres://.
572 Each of the remaining URI parts is optional. The following examples
573 illustrate valid URI syntax:
575 postgresql://localhost
576 postgresql://localhost:5433
577 postgresql://localhost/mydb
578 postgresql://user@localhost
579 postgresql://user:secret@localhost
580 postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
581 postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_nam
584 Values that would normally appear in the hierarchical part of the URI
585 can alternatively be given as named parameters. For example:
586 postgresql:///mydb?host=localhost&port=5433
588 All named parameters must match key words listed in Section 32.1.2,
589 except that for compatibility with JDBC connection URIs, instances of
590 ssl=true are translated into sslmode=require.
592 The connection URI needs to be encoded with percent-encoding if it
593 includes symbols with special meaning in any of its parts. Here is an
594 example where the equal sign (=) is replaced with %3D and the space
596 postgresql://user@localhost:5433/mydb?options=-c%20synchronous_commit%3Doff
598 The host part may be either a host name or an IP address. To specify an
599 IPv6 address, enclose it in square brackets:
600 postgresql://[2001:db8::1234]/database
602 The host part is interpreted as described for the parameter host. In
603 particular, a Unix-domain socket connection is chosen if the host part
604 is either empty or looks like an absolute path name, otherwise a TCP/IP
605 connection is initiated. Note, however, that the slash is a reserved
606 character in the hierarchical part of the URI. So, to specify a
607 non-standard Unix-domain socket directory, either omit the host part of
608 the URI and specify the host as a named parameter, or percent-encode
609 the path in the host part of the URI:
610 postgresql:///dbname?host=/var/lib/postgresql
611 postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
613 It is possible to specify multiple host components, each with an
614 optional port component, in a single URI. A URI of the form
615 postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a
616 connection string of the form host=host1,host2,host3
617 port=port1,port2,port3. As further described below, each host will be
618 tried in turn until a connection is successfully established.
620 32.1.1.3. Specifying Multiple Hosts #
622 It is possible to specify multiple hosts to connect to, so that they
623 are tried in the given order. In the Keyword/Value format, the host,
624 hostaddr, and port options accept comma-separated lists of values. The
625 same number of elements must be given in each option that is specified,
626 such that e.g., the first hostaddr corresponds to the first host name,
627 the second hostaddr corresponds to the second host name, and so forth.
628 As an exception, if only one port is specified, it applies to all the
631 In the connection URI format, you can list multiple host:port pairs
632 separated by commas in the host component of the URI.
634 In either format, a single host name can translate to multiple network
635 addresses. A common example of this is a host that has both an IPv4 and
638 When multiple hosts are specified, or when a single host name is
639 translated to multiple addresses, all the hosts and addresses will be
640 tried in order, until one succeeds. If none of the hosts can be
641 reached, the connection fails. If a connection is established
642 successfully, but authentication fails, the remaining hosts in the list
645 If a password file is used, you can have different passwords for
646 different hosts. All the other connection options are the same for
647 every host in the list; it is not possible to e.g., specify different
648 usernames for different hosts.
650 32.1.2. Parameter Key Words #
652 The currently recognized parameter key words are:
655 Name of host to connect to. If a host name looks like an
656 absolute path name, it specifies Unix-domain communication
657 rather than TCP/IP communication; the value is the name of the
658 directory in which the socket file is stored. (On Unix, an
659 absolute path name begins with a slash. On Windows, paths
660 starting with drive letters are also recognized.) If the host
661 name starts with @, it is taken as a Unix-domain socket in the
662 abstract namespace (currently supported on Linux and Windows).
663 The default behavior when host is not specified, or is empty, is
664 to connect to a Unix-domain socket in /tmp (or whatever socket
665 directory was specified when PostgreSQL was built). On Windows,
666 the default is to connect to localhost.
668 A comma-separated list of host names is also accepted, in which
669 case each host name in the list is tried in order; an empty item
670 in the list selects the default behavior as explained above. See
671 Section 32.1.1.3 for details.
674 Numeric IP address of host to connect to. This should be in the
675 standard IPv4 address format, e.g., 172.28.40.9. If your machine
676 supports IPv6, you can also use those addresses. TCP/IP
677 communication is always used when a nonempty string is specified
678 for this parameter. If this parameter is not specified, the
679 value of host will be looked up to find the corresponding IP
680 address — or, if host specifies an IP address, that value will
683 Using hostaddr allows the application to avoid a host name
684 look-up, which might be important in applications with time
685 constraints. However, a host name is required for GSSAPI or SSPI
686 authentication methods, as well as for verify-full SSL
687 certificate verification. The following rules are used:
689 + If host is specified without hostaddr, a host name lookup
690 occurs. (When using PQconnectPoll, the lookup occurs when
691 PQconnectPoll first considers this host name, and it may cause
692 PQconnectPoll to block for a significant amount of time.)
693 + If hostaddr is specified without host, the value for hostaddr
694 gives the server network address. The connection attempt will
695 fail if the authentication method requires a host name.
696 + If both host and hostaddr are specified, the value for
697 hostaddr gives the server network address. The value for host
698 is ignored unless the authentication method requires it, in
699 which case it will be used as the host name.
701 Note that authentication is likely to fail if host is not the
702 name of the server at network address hostaddr. Also, when both
703 host and hostaddr are specified, host is used to identify the
704 connection in a password file (see Section 32.16).
706 A comma-separated list of hostaddr values is also accepted, in
707 which case each host in the list is tried in order. An empty
708 item in the list causes the corresponding host name to be used,
709 or the default host name if that is empty as well. See
710 Section 32.1.1.3 for details.
712 Without either a host name or host address, libpq will connect
713 using a local Unix-domain socket; or on Windows, it will attempt
714 to connect to localhost.
717 Port number to connect to at the server host, or socket file
718 name extension for Unix-domain connections. If multiple hosts
719 were given in the host or hostaddr parameters, this parameter
720 may specify a comma-separated list of ports of the same length
721 as the host list, or it may specify a single port number to be
722 used for all hosts. An empty string, or an empty item in a
723 comma-separated list, specifies the default port number
724 established when PostgreSQL was built.
727 The database name. Defaults to be the same as the user name. In
728 certain contexts, the value is checked for extended formats; see
729 Section 32.1.1 for more details on those.
732 PostgreSQL user name to connect as. Defaults to be the same as
733 the operating system name of the user running the application.
736 Password to be used if the server demands password
740 Specifies the name of the file used to store passwords (see
741 Section 32.16). Defaults to ~/.pgpass, or
742 %APPDATA%\postgresql\pgpass.conf on Microsoft Windows. (No error
743 is reported if this file does not exist.)
746 Specifies the authentication method that the client requires
747 from the server. If the server does not use the required method
748 to authenticate the client, or if the authentication handshake
749 is not fully completed by the server, the connection will fail.
750 A comma-separated list of methods may also be provided, of which
751 the server must use exactly one in order for the connection to
752 succeed. By default, any authentication method is accepted, and
753 the server is free to skip authentication altogether.
755 Methods may be negated with the addition of a ! prefix, in which
756 case the server must not attempt the listed method; any other
757 method is accepted, and the server is free not to authenticate
758 the client at all. If a comma-separated list is provided, the
759 server may not attempt any of the listed negated methods.
760 Negated and non-negated forms may not be combined in the same
763 As a final special case, the none method requires the server not
764 to use an authentication challenge. (It may also be negated, to
765 require some form of authentication.)
767 The following methods may be specified:
770 The server must request plaintext password authentication.
773 The server must request MD5 hashed password
778 Support for MD5-encrypted passwords is deprecated and will
779 be removed in a future release of PostgreSQL. Refer to
780 Section 20.5 for details about migrating to another
784 The server must either request a Kerberos handshake via
785 GSSAPI or establish a GSS-encrypted channel (see also
789 The server must request Windows SSPI authentication.
792 The server must successfully complete a SCRAM-SHA-256
793 authentication exchange with the client.
796 The server must request an OAuth bearer token from the
800 The server must not prompt the client for an
801 authentication exchange. (This does not prohibit client
802 certificate authentication via TLS, nor GSS authentication
803 via its encrypted transport.)
806 This option controls the client's use of channel binding. A
807 setting of require means that the connection must employ channel
808 binding, prefer means that the client will choose channel
809 binding if available, and disable prevents the use of channel
810 binding. The default is prefer if PostgreSQL is compiled with
811 SSL support; otherwise the default is disable.
813 Channel binding is a method for the server to authenticate
814 itself to the client. It is only supported over SSL connections
815 with PostgreSQL 11 or later servers using the SCRAM
816 authentication method.
819 Maximum time to wait while connecting, in seconds (write as a
820 decimal integer, e.g., 10). Zero, negative, or not specified
821 means wait indefinitely. This timeout applies separately to each
822 host name or IP address. For example, if you specify two hosts
823 and connect_timeout is 5, each host will time out if no
824 connection is made within 5 seconds, so the total time spent
825 waiting for a connection might be up to 10 seconds.
828 This sets the client_encoding configuration parameter for this
829 connection. In addition to the values accepted by the
830 corresponding server option, you can use auto to determine the
831 right encoding from the current locale in the client (LC_CTYPE
832 environment variable on Unix systems).
835 Specifies command-line options to send to the server at
836 connection start. For example, setting this to -c geqo=off or
837 --geqo=off sets the session's value of the geqo parameter to
838 off. Spaces within this string are considered to separate
839 command-line arguments, unless escaped with a backslash (\);
840 write \\ to represent a literal backslash. For a detailed
841 discussion of the available options, consult Chapter 19.
844 Specifies a value for the application_name configuration
847 fallback_application_name #
848 Specifies a fallback value for the application_name
849 configuration parameter. This value will be used if no value has
850 been given for application_name via a connection parameter or
851 the PGAPPNAME environment variable. Specifying a fallback name
852 is useful in generic utility programs that wish to set a default
853 application name but allow it to be overridden by the user.
856 Controls whether client-side TCP keepalives are used. The
857 default value is 1, meaning on, but you can change this to 0,
858 meaning off, if keepalives are not wanted. This parameter is
859 ignored for connections made via a Unix-domain socket.
862 Controls the number of seconds of inactivity after which TCP
863 should send a keepalive message to the server. A value of zero
864 uses the system default. This parameter is ignored for
865 connections made via a Unix-domain socket, or if keepalives are
866 disabled. It is only supported on systems where TCP_KEEPIDLE or
867 an equivalent socket option is available, and on Windows; on
868 other systems, it has no effect.
870 keepalives_interval #
871 Controls the number of seconds after which a TCP keepalive
872 message that is not acknowledged by the server should be
873 retransmitted. A value of zero uses the system default. This
874 parameter is ignored for connections made via a Unix-domain
875 socket, or if keepalives are disabled. It is only supported on
876 systems where TCP_KEEPINTVL or an equivalent socket option is
877 available, and on Windows; on other systems, it has no effect.
880 Controls the number of TCP keepalives that can be lost before
881 the client's connection to the server is considered dead. A
882 value of zero uses the system default. This parameter is ignored
883 for connections made via a Unix-domain socket, or if keepalives
884 are disabled. It is only supported on systems where TCP_KEEPCNT
885 or an equivalent socket option is available; on other systems,
889 Controls the number of milliseconds that transmitted data may
890 remain unacknowledged before a connection is forcibly closed. A
891 value of zero uses the system default. This parameter is ignored
892 for connections made via a Unix-domain socket. It is only
893 supported on systems where TCP_USER_TIMEOUT is available; on
894 other systems, it has no effect.
897 This option determines whether the connection should use the
898 replication protocol instead of the normal protocol. This is
899 what PostgreSQL replication connections as well as tools such as
900 pg_basebackup use internally, but it can also be used by
901 third-party applications. For a description of the replication
902 protocol, consult Section 54.4.
904 The following values, which are case-insensitive, are supported:
907 The connection goes into physical replication mode.
910 The connection goes into logical replication mode,
911 connecting to the database specified in the dbname
915 The connection is a regular one, which is the default
918 In physical or logical replication mode, only the simple query
919 protocol can be used.
922 This option determines whether or with what priority a secure
923 GSS TCP/IP connection will be negotiated with the server. There
927 only try a non-GSSAPI-encrypted connection
930 if there are GSSAPI credentials present (i.e., in a
931 credentials cache), first try a GSSAPI-encrypted
932 connection; if that fails or there are no credentials, try
933 a non-GSSAPI-encrypted connection. This is the default
934 when PostgreSQL has been compiled with GSSAPI support.
937 only try a GSSAPI-encrypted connection
939 gssencmode is ignored for Unix domain socket communication. If
940 PostgreSQL is compiled without GSSAPI support, using the require
941 option will cause an error, while prefer will be accepted but
942 libpq will not actually attempt a GSSAPI-encrypted connection.
945 This option determines whether or with what priority a secure
946 SSL TCP/IP connection will be negotiated with the server. There
950 only try a non-SSL connection
953 first try a non-SSL connection; if that fails, try an SSL
957 first try an SSL connection; if that fails, try a non-SSL
961 only try an SSL connection. If a root CA file is present,
962 verify the certificate in the same way as if verify-ca was
966 only try an SSL connection, and verify that the server
967 certificate is issued by a trusted certificate authority
971 only try an SSL connection, verify that the server
972 certificate is issued by a trusted CA and that the
973 requested server host name matches that in the certificate
975 See Section 32.19 for a detailed description of how these
978 sslmode is ignored for Unix domain socket communication. If
979 PostgreSQL is compiled without SSL support, using options
980 require, verify-ca, or verify-full will cause an error, while
981 options allow and prefer will be accepted but libpq will not
982 actually attempt an SSL connection.
984 Note that if GSSAPI encryption is possible, that will be used in
985 preference to SSL encryption, regardless of the value of
986 sslmode. To force use of SSL encryption in an environment that
987 has working GSSAPI infrastructure (such as a Kerberos server),
988 also set gssencmode to disable.
991 This option is deprecated in favor of the sslmode setting.
993 If set to 1, an SSL connection to the server is required (this
994 is equivalent to sslmode require). libpq will then refuse to
995 connect if the server does not accept an SSL connection. If set
996 to 0 (default), libpq will negotiate the connection type with
997 the server (equivalent to sslmode prefer). This option is only
998 available if PostgreSQL is compiled with SSL support.
1001 This option controls how SSL encryption is negotiated with the
1002 server, if SSL is used. In the default postgres mode, the client
1003 first asks the server if SSL is supported. In direct mode, the
1004 client starts the standard SSL handshake directly after
1005 establishing the TCP/IP connection. Traditional PostgreSQL
1006 protocol negotiation is the most flexible with different server
1007 configurations. If the server is known to support direct SSL
1008 connections then the latter requires one fewer round trip
1009 reducing connection latency and also allows the use of protocol
1010 agnostic SSL network tools. The direct SSL option was introduced
1011 in PostgreSQL version 17.
1014 perform PostgreSQL protocol negotiation. This is the
1015 default if the option is not provided.
1018 start SSL handshake directly after establishing the TCP/IP
1019 connection. This is only allowed with sslmode=require or
1020 higher, because the weaker settings could lead to
1021 unintended fallback to plaintext authentication when the
1022 server does not support direct SSL handshake.
1025 If set to 1, data sent over SSL connections will be compressed.
1026 If set to 0, compression will be disabled. The default is 0.
1027 This parameter is ignored if a connection without SSL is made.
1029 SSL compression is nowadays considered insecure and its use is
1030 no longer recommended. OpenSSL 1.1.0 disabled compression by
1031 default, and many operating system distributions disabled it in
1032 prior versions as well, so setting this parameter to on will not
1033 have any effect if the server does not accept compression.
1034 PostgreSQL 14 disabled compression completely in the backend.
1036 If security is not a primary concern, compression can improve
1037 throughput if the network is the bottleneck. Disabling
1038 compression can improve response time and throughput if CPU
1039 performance is the limiting factor.
1042 This parameter specifies the file name of the client SSL
1043 certificate, replacing the default ~/.postgresql/postgresql.crt.
1044 This parameter is ignored if an SSL connection is not made.
1047 This parameter specifies the location for the secret key used
1048 for the client certificate. It can either specify a file name
1049 that will be used instead of the default
1050 ~/.postgresql/postgresql.key, or it can specify a key obtained
1051 from an external “engine” (engines are OpenSSL loadable
1052 modules). An external engine specification should consist of a
1053 colon-separated engine name and an engine-specific key
1054 identifier. This parameter is ignored if an SSL connection is
1058 This parameter specifies the location where libpq will log keys
1059 used in this SSL context. This is useful for debugging
1060 PostgreSQL protocol interactions or client connections using
1061 network inspection tools like Wireshark. This parameter is
1062 ignored if an SSL connection is not made, or if LibreSSL is used
1063 (LibreSSL does not support key logging). Keys are logged using
1068 Key logging will expose potentially sensitive information in the
1069 keylog file. Keylog files should be handled with the same care
1073 This parameter specifies the password for the secret key
1074 specified in sslkey, allowing client certificate private keys to
1075 be stored in encrypted form on disk even when interactive
1076 passphrase input is not practical.
1078 Specifying this parameter with any non-empty value suppresses
1079 the Enter PEM pass phrase: prompt that OpenSSL will emit by
1080 default when an encrypted client certificate key is provided to
1083 If the key is not encrypted this parameter is ignored. The
1084 parameter has no effect on keys specified by OpenSSL engines
1085 unless the engine uses the OpenSSL password callback mechanism
1088 There is no environment variable equivalent to this option, and
1089 no facility for looking it up in .pgpass. It can be used in a
1090 service file connection definition. Users with more
1091 sophisticated uses should consider using OpenSSL engines and
1092 tools like PKCS#11 or USB crypto offload devices.
1095 This option determines whether a client certificate may be sent
1096 to the server, and whether the server is required to request
1097 one. There are three modes:
1100 A client certificate is never sent, even if one is
1101 available (default location or provided via sslcert).
1104 A certificate may be sent, if the server requests one and
1105 the client has one to send.
1108 The server must request a certificate. The connection will
1109 fail if the client does not send a certificate and the
1110 server successfully authenticates the client anyway.
1114 sslcertmode=require doesn't add any additional security, since
1115 there is no guarantee that the server is validating the
1116 certificate correctly; PostgreSQL servers generally request TLS
1117 certificates from clients whether they validate them or not. The
1118 option may be useful when troubleshooting more complicated TLS
1122 This parameter specifies the name of a file containing SSL
1123 certificate authority (CA) certificate(s). If the file exists,
1124 the server's certificate will be verified to be signed by one of
1125 these authorities. The default is ~/.postgresql/root.crt.
1127 The special value system may be specified instead, in which case
1128 the trusted CA roots from the SSL implementation will be loaded.
1129 The exact locations of these root certificates differ by SSL
1130 implementation and platform. For OpenSSL in particular, the
1131 locations may be further modified by the SSL_CERT_DIR and
1132 SSL_CERT_FILE environment variables.
1136 When using sslrootcert=system, the default sslmode is changed to
1137 verify-full, and any weaker setting will result in an error. In
1138 most cases it is trivial for anyone to obtain a certificate
1139 trusted by the system for a hostname they control, rendering
1140 verify-ca and all weaker modes useless.
1142 The magic system value will take precedence over a local
1143 certificate file with the same name. If for some reason you find
1144 yourself in this situation, use an alternative path like
1145 sslrootcert=./system instead.
1148 This parameter specifies the file name of the SSL server
1149 certificate revocation list (CRL). Certificates listed in this
1150 file, if it exists, will be rejected while attempting to
1151 authenticate the server's certificate. If neither sslcrl nor
1152 sslcrldir is set, this setting is taken as
1153 ~/.postgresql/root.crl.
1156 This parameter specifies the directory name of the SSL server
1157 certificate revocation list (CRL). Certificates listed in the
1158 files in this directory, if it exists, will be rejected while
1159 attempting to authenticate the server's certificate.
1161 The directory needs to be prepared with the OpenSSL command
1162 openssl rehash or c_rehash. See its documentation for details.
1164 Both sslcrl and sslcrldir can be specified together.
1167 If set to 1 (default), libpq sets the TLS extension “Server Name
1168 Indication” (SNI) on SSL-enabled connections. By setting this
1169 parameter to 0, this is turned off.
1171 The Server Name Indication can be used by SSL-aware proxies to
1172 route connections without having to decrypt the SSL stream.
1173 (Note that unless the proxy is aware of the PostgreSQL protocol
1174 handshake this would require setting sslnegotiation to direct.)
1175 However, SNI makes the destination host name appear in cleartext
1176 in the network traffic, so it might be undesirable in some
1180 This parameter specifies the operating-system user name of the
1181 server, for example requirepeer=postgres. When making a
1182 Unix-domain socket connection, if this parameter is set, the
1183 client checks at the beginning of the connection that the server
1184 process is running under the specified user name; if it is not,
1185 the connection is aborted with an error. This parameter can be
1186 used to provide server authentication similar to that available
1187 with SSL certificates on TCP/IP connections. (Note that if the
1188 Unix-domain socket is in /tmp or another publicly writable
1189 location, any user could start a server listening there. Use
1190 this parameter to ensure that you are connected to a server run
1191 by a trusted user.) This option is only supported on platforms
1192 for which the peer authentication method is implemented; see
1195 ssl_min_protocol_version #
1196 This parameter specifies the minimum SSL/TLS protocol version to
1197 allow for the connection. Valid values are TLSv1, TLSv1.1,
1198 TLSv1.2 and TLSv1.3. The supported protocols depend on the
1199 version of OpenSSL used, older versions not supporting the most
1200 modern protocol versions. If not specified, the default is
1201 TLSv1.2, which satisfies industry best practices as of this
1204 ssl_max_protocol_version #
1205 This parameter specifies the maximum SSL/TLS protocol version to
1206 allow for the connection. Valid values are TLSv1, TLSv1.1,
1207 TLSv1.2 and TLSv1.3. The supported protocols depend on the
1208 version of OpenSSL used, older versions not supporting the most
1209 modern protocol versions. If not set, this parameter is ignored
1210 and the connection will use the maximum bound defined by the
1211 backend, if set. Setting the maximum protocol version is mainly
1212 useful for testing or if some component has issues working with
1215 min_protocol_version #
1216 Specifies the minimum protocol version to allow for the
1217 connection. The default is to allow any version of the
1218 PostgreSQL protocol supported by libpq, which currently means
1219 3.0. If the server does not support at least this protocol
1220 version the connection will be closed.
1222 The current supported values are 3.0, 3.2, and latest. The
1223 latest value is equivalent to the latest protocol version
1224 supported by the libpq version being used, which is currently
1227 max_protocol_version #
1228 Specifies the protocol version to request from the server. The
1229 default is to use version 3.0 of the PostgreSQL protocol, unless
1230 the connection string specifies a feature that relies on a
1231 higher protocol version, in which case the latest version
1232 supported by libpq is used. If the server does not support the
1233 protocol version requested by the client, the connection is
1234 automatically downgraded to a lower minor protocol version that
1235 the server supports. After the connection attempt has completed
1236 you can use PQprotocolVersion to find out which exact protocol
1237 version was negotiated.
1239 The current supported values are 3.0, 3.2, and latest. The
1240 latest value is equivalent to the latest protocol version
1241 supported by the libpq version being used, which is currently
1245 Kerberos service name to use when authenticating with GSSAPI.
1246 This must match the service name specified in the server
1247 configuration for Kerberos authentication to succeed. (See also
1248 Section 20.6.) The default value is normally postgres, but that
1249 can be changed when building PostgreSQL via the
1250 --with-krb-srvnam option of configure. In most environments,
1251 this parameter never needs to be changed. Some Kerberos
1252 implementations might require a different service name, such as
1253 Microsoft Active Directory which requires the service name to be
1254 in upper case (POSTGRES).
1257 GSS library to use for GSSAPI authentication. Currently this is
1258 disregarded except on Windows builds that include both GSSAPI
1259 and SSPI support. In that case, set this to gssapi to cause
1260 libpq to use the GSSAPI library for authentication instead of
1264 Forward (delegate) GSS credentials to the server. The default is
1265 0 which means credentials will not be forwarded to the server.
1266 Set this to 1 to have credentials forwarded when possible.
1269 The base64-encoded SCRAM client key. This can be used by
1270 foreign-data wrappers or similar middleware to enable
1271 pass-through SCRAM authentication. See Section F.38.1.10 for one
1272 such implementation. It is not meant to be specified directly by
1273 users or client applications.
1276 The base64-encoded SCRAM server key. This can be used by
1277 foreign-data wrappers or similar middleware to enable
1278 pass-through SCRAM authentication. See Section F.38.1.10 for one
1279 such implementation. It is not meant to be specified directly by
1280 users or client applications.
1283 Service name to use for additional parameters. It specifies a
1284 service name in pg_service.conf that holds additional connection
1285 parameters. This allows applications to specify only a service
1286 name so connection parameters can be centrally maintained. See
1289 target_session_attrs #
1290 This option determines whether the session must have certain
1291 properties to be acceptable. It's typically used in combination
1292 with multiple host names to select the first acceptable
1293 alternative among several hosts. There are six modes:
1296 any successful connection is acceptable
1299 session must accept read-write transactions by default
1300 (that is, the server must not be in hot standby mode and
1301 the default_transaction_read_only parameter must be off)
1304 session must not accept read-write transactions by default
1308 server must not be in hot standby mode
1311 server must be in hot standby mode
1314 first try to find a standby server, but if none of the
1315 listed hosts is a standby server, try again in any mode
1317 load_balance_hosts #
1318 Controls the order in which the client tries to connect to the
1319 available hosts and addresses. Once a connection attempt is
1320 successful no other hosts and addresses will be tried. This
1321 parameter is typically used in combination with multiple host
1322 names or a DNS record that returns multiple IPs. This parameter
1323 can be used in combination with target_session_attrs to, for
1324 example, load balance over standby servers only. Once
1325 successfully connected, subsequent queries on the returned
1326 connection will all be sent to the same server. There are
1327 currently two modes:
1330 No load balancing across hosts is performed. Hosts are
1331 tried in the order in which they are provided and
1332 addresses are tried in the order they are received from
1333 DNS or a hosts file.
1336 Hosts and addresses are tried in random order. This value
1337 is mostly useful when opening multiple connections at the
1338 same time, possibly from different machines. This way
1339 connections can be load balanced across multiple
1342 While random load balancing, due to its random nature,
1343 will almost never result in a completely uniform
1344 distribution, it statistically gets quite close. One
1345 important aspect here is that this algorithm uses two
1346 levels of random choices: First the hosts will be resolved
1347 in random order. Then secondly, before resolving the next
1348 host, all resolved addresses for the current host will be
1349 tried in random order. This behaviour can skew the amount
1350 of connections each node gets greatly in certain cases,
1351 for instance when some hosts resolve to more addresses
1352 than others. But such a skew can also be used on purpose,
1353 e.g. to increase the number of connections a larger server
1354 gets by providing its hostname multiple times in the host
1357 When using this value it's recommended to also configure a
1358 reasonable value for connect_timeout. Because then, if one
1359 of the nodes that are used for load balancing is not
1360 responding, a new node will be tried.
1363 The HTTPS URL of a trusted issuer to contact if the server
1364 requests an OAuth token for the connection. This parameter is
1365 required for all OAuth connections; it should exactly match the
1366 issuer setting in the server's HBA configuration.
1368 As part of the standard authentication handshake, libpq will ask
1369 the server for a discovery document: a URL providing a set of
1370 OAuth configuration parameters. The server must provide a URL
1371 that is directly constructed from the components of the
1372 oauth_issuer, and this value must exactly match the issuer
1373 identifier that is declared in the discovery document itself, or
1374 the connection will fail. This is required to prevent a class of
1375 "mix-up attacks" on OAuth clients.
1377 You may also explicitly set oauth_issuer to the /.well-known/
1378 URI used for OAuth discovery. In this case, if the server asks
1379 for a different URL, the connection will fail, but a custom
1380 OAuth flow may be able to speed up the standard handshake by
1381 using previously cached tokens. (In this case, it is recommended
1382 that oauth_scope be set as well, since the client will not have
1383 a chance to ask the server for a correct scope setting, and the
1384 default scopes for a token may not be sufficient to connect.)
1385 libpq currently supports the following well-known endpoints:
1387 + /.well-known/openid-configuration
1388 + /.well-known/oauth-authorization-server
1392 Issuers are highly privileged during the OAuth connection
1393 handshake. As a rule of thumb, if you would not trust the
1394 operator of a URL to handle access to your servers, or to
1395 impersonate you directly, that URL should not be trusted as an
1399 An OAuth 2.0 client identifier, as issued by the authorization
1400 server. If the PostgreSQL server requests an OAuth token for the
1401 connection (and if no custom OAuth hook is installed to provide
1402 one), then this parameter must be set; otherwise, the connection
1405 oauth_client_secret #
1406 The client password, if any, to use when contacting the OAuth
1407 authorization server. Whether this parameter is required or not
1408 is determined by the OAuth provider; "public" clients generally
1409 do not use a secret, whereas "confidential" clients generally
1413 The scope of the access request sent to the authorization
1414 server, specified as a (possibly empty) space-separated list of
1415 OAuth scope identifiers. This parameter is optional and intended
1418 Usually the client will obtain appropriate scope settings from
1419 the PostgreSQL server. If this parameter is used, the server's
1420 requested scope list will be ignored. This can prevent a
1421 less-trusted server from requesting inappropriate access scopes
1422 from the end user. However, if the client's scope setting does
1423 not contain the server's required scopes, the server is likely
1424 to reject the issued token, and the connection will fail.
1426 The meaning of an empty scope list is provider-dependent. An
1427 OAuth authorization server may choose to issue a token with
1428 "default scope", whatever that happens to be, or it may reject
1429 the token request entirely.
begriffs.com / projects / ai-pg / blob