1 <!-- $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.405 2008/01/31 23:03:16 momjian Exp $ -->
4 <title>Operating System Environment</title>
7 This chapter discusses how to set up and run the database server
8 and its interactions with the operating system.
11 <sect1 id="postgres-user">
12 <title>The <productname>PostgreSQL</productname> User Account</title>
15 <primary>postgres user</primary>
19 As with any other server daemon that is accessible to the outside world,
20 it is advisable to run <productname>PostgreSQL</productname> under a
21 separate user account. This user account should only own the data
22 that is managed by the server, and should not be shared with other
23 daemons. (For example, using the user <literal>nobody</literal> is a bad
24 idea.) It is not advisable to install executables owned by this
25 user because compromised systems could then modify their own
30 To add a Unix user account to your system, look for a command
31 <command>useradd</command> or <command>adduser</command>. The user
32 name <systemitem>postgres</systemitem> is often used, and is assumed
33 throughout this book, but you can use another name if you like.
37 <sect1 id="creating-cluster">
38 <title>Creating a Database Cluster</title>
41 <primary>database cluster</primary>
45 <primary>data area</primary>
46 <see>database cluster</see>
50 Before you can do anything, you must initialize a database storage
51 area on disk. We call this a <firstterm>database cluster</firstterm>.
52 (<acronym>SQL</acronym> uses the term catalog cluster.) A
53 database cluster is a collection of databases that is managed by a
54 single instance of a running database server. After initialization, a
55 database cluster will contain a database named <literal>postgres</literal>,
56 which is meant as a default database for use by utilities, users and third
57 party applications. The database server itself does not require the
58 <literal>postgres</literal> database to exist, but many external utility
59 programs assume it exists. Another database created within each cluster
60 during initialization is called
61 <literal>template1</literal>. As the name suggests, this will be used
62 as a template for subsequently created databases; it should not be
63 used for actual work. (See <xref linkend="managing-databases"> for
64 information about creating new databases within a cluster.)
68 In file system terms, a database cluster will be a single directory
69 under which all data will be stored. We call this the <firstterm>data
70 directory</firstterm> or <firstterm>data area</firstterm>. It is
71 completely up to you where you choose to store your data. There is no
72 default, although locations such as
73 <filename>/usr/local/pgsql/data</filename> or
74 <filename>/var/lib/pgsql/data</filename> are popular. To initialize a
75 database cluster, use the command <xref
76 linkend="app-initdb">,<indexterm><primary>initdb</></> which is
77 installed with <productname>PostgreSQL</productname>. The desired
78 file system location of your database cluster is indicated by the
79 <option>-D</option> option, for example
81 <prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput>
83 Note that you must execute this command while logged into the
84 <productname>PostgreSQL</productname> user account, which is
85 described in the previous section.
90 As an alternative to the <option>-D</option> option, you can set
91 the environment variable <envar>PGDATA</envar>.
92 <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
97 <command>initdb</command> will attempt to create the directory you
98 specify if it does not already exist. It is likely that it will not
99 have the permission to do so (if you followed our advice and created
100 an unprivileged account). In that case you should create the
101 directory yourself (as root) and change the owner to be the
102 <productname>PostgreSQL</productname> user. Here is how this might
105 root# <userinput>mkdir /usr/local/pgsql/data</userinput>
106 root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
107 root# <userinput>su postgres</userinput>
108 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
113 <command>initdb</command> will refuse to run if the data directory
114 looks like it has already been initialized.</para>
117 Because the data directory contains all the data stored in the
118 database, it is essential that it be secured from unauthorized
119 access. <command>initdb</command> therefore revokes access
120 permissions from everyone but the
121 <productname>PostgreSQL</productname> user.
125 However, while the directory contents are secure, the default
126 client authentication setup allows any local user to connect to the
127 database and even become the database superuser. If you do not
128 trust other local users, we recommend you use one of
129 <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
130 or <option>--pwfile</option> options to assign a password to the
131 database superuser.<indexterm><primary>password</><secondary>of the
132 superuser</></indexterm> Also, specify <option>-A md5</> or
133 <option>-A password</> so that the default <literal>trust</> authentication
134 mode is not used; or modify the generated <filename>pg_hba.conf</filename>
135 file after running <command>initdb</command>,
136 <emphasis>before</> you start the server for the first time. (Other
137 reasonable approaches include using <literal>ident</literal> authentication
138 or file system permissions to restrict connections. See <xref
139 linkend="client-authentication"> for more information.)
143 <command>initdb</command> also initializes the default
144 locale<indexterm><primary>locale</></> for the database cluster.
145 Normally, it will just take the locale settings in the environment
146 and apply them to the initialized database. It is possible to
147 specify a different locale for the database; more information about
148 that can be found in <xref linkend="locale">. The sort order used
149 within a particular database cluster is set by
150 <command>initdb</command> and cannot be changed later, short of
151 dumping all data, rerunning <command>initdb</command>, and reloading
152 the data. There is also a performance impact for using locales
153 other than <literal>C</> or <literal>POSIX</>. Therefore, it is
154 important to make this choice correctly the first time.
158 <command>initdb</command> also sets the default character set encoding
159 for the database cluster. Normally this should be chosen to match the
160 locale setting. For details see <xref linkend="multibyte">.
163 <sect2 id="creating-cluster-nfs">
164 <title>Network File Systems</title>
166 <indexterm zone="creating-cluster-nfs">
167 <primary>Network File Systems</primary>
169 <indexterm><primary><acronym>NFS</></><see>Network File Systems</></>
170 <indexterm><primary>Network Attached Storage (<acronym>NAS</>)</><see>Network File Systems</></>
173 Many installations create database clusters on network file systems.
174 Sometimes this is done directly via <acronym>NFS</>, or by using a
175 Network Attached Storage (<acronym>NAS</>) device that uses
176 <acronym>NFS</> internally. <productname>PostgreSQL</> does nothing
177 special for <acronym>NFS</> file systems, meaning it assumes
178 <acronym>NFS</> behaves exactly like locally-connected drives
179 (<acronym>DAS</>, Direct Attached Storage). If client and server
180 <acronym>NFS</> implementations have non-standard semantics, this can
181 cause reliability problems (see <ulink
182 url="http://www.time-travellers.org/shane/papers/NFS_considered_harmful.html"></ulink>).
183 Specifically, delayed (asynchronous) writes to the <acronym>NFS</>
184 server can cause reliability problems; if possible, mount
185 <acronym>NFS</> file systems synchronously (without caching) to avoid
186 this. (Storage Area Networks (<acronym>SAN</>) use a low-level
187 communication protocol rather than <acronym>NFS</>.)
194 <sect1 id="server-start">
195 <title>Starting the Database Server</title>
198 Before anyone can access the database, you must start the database
199 server. The database server program is called
200 <command>postgres</command>.<indexterm><primary>postgres</></>
201 The <command>postgres</command> program must know where to
202 find the data it is supposed to use. This is done with the
203 <option>-D</option> option. Thus, the simplest way to start the
206 $ <userinput>postgres -D /usr/local/pgsql/data</userinput>
208 which will leave the server running in the foreground. This must be
209 done while logged into the <productname>PostgreSQL</productname> user
210 account. Without <option>-D</option>, the server will try to use
211 the data directory named by the environment variable <envar>PGDATA</envar>.
212 If that variable is not provided either, it will fail.
216 Normally it is better to start <command>postgres</command> in the
217 background. For this, use the usual shell syntax:
219 $ <userinput>postgres -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
221 It is important to store the server's <systemitem>stdout</> and
222 <systemitem>stderr</> output somewhere, as shown above. It will help
223 for auditing purposes and to diagnose problems. (See <xref
224 linkend="logfile-maintenance"> for a more thorough discussion of log
229 The <command>postgres</command> program also takes a number of other
230 command-line options. For more information, see the
231 <xref linkend="app-postgres"> reference page
232 and <xref linkend="runtime-config"> below.
236 This shell syntax can get tedious quickly. Therefore the wrapper
238 <xref linkend="app-pg-ctl"><indexterm><primary>pg_ctl</primary></indexterm>
239 is provided to simplify some tasks. For example:
241 pg_ctl start -l logfile
243 will start the server in the background and put the output into the
244 named log file. The <option>-D</option> option has the same meaning
245 here as for <command>postgres</command>. <command>pg_ctl</command>
246 is also capable of stopping the server.
250 Normally, you will want to start the database server when the
251 computer boots.<indexterm><primary>booting</><secondary>starting
252 the server during</></> Autostart scripts are operating-system-specific.
253 There are a few distributed with
254 <productname>PostgreSQL</productname> in the
255 <filename>contrib/start-scripts</> directory. Installing one will require
260 Different systems have different conventions for starting up daemons
261 at boot time. Many systems have a file
262 <filename>/etc/rc.local</filename> or
263 <filename>/etc/rc.d/rc.local</filename>. Others use
264 <filename>rc.d</> directories. Whatever you do, the server must be
265 run by the <productname>PostgreSQL</productname> user account
266 <emphasis>and not by root</emphasis> or any other user. Therefore you
267 probably should form your commands using <literal>su -c '...'
268 postgres</literal>. For example:
270 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
275 Here are a few more operating-system-specific suggestions. (In each
276 case be sure to use the proper installation directory and user
277 name where we show generic values.)
282 For <productname>FreeBSD</productname>, look at the file
283 <filename>contrib/start-scripts/freebsd</filename> in the
284 <productname>PostgreSQL</productname> source distribution.
285 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
291 On <productname>OpenBSD</productname>, add the following lines
292 to the file <filename>/etc/rc.local</filename>:
293 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
295 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
296 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
297 echo -n ' postgresql'
305 On <productname>Linux</productname> systems either add
306 <indexterm><primary>Linux</><secondary>start script</secondary></>
308 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
310 to <filename>/etc/rc.d/rc.local</filename> or look at the file
311 <filename>contrib/start-scripts/linux</filename> in the
312 <productname>PostgreSQL</productname> source distribution.
318 On <productname>NetBSD</productname>, either use the
319 <productname>FreeBSD</productname> or
320 <productname>Linux</productname> start scripts, depending on
321 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
327 On <productname>Solaris</productname>, create a file called
328 <filename>/etc/init.d/postgresql</filename> that contains
330 <indexterm><primary>Solaris</><secondary>start script</secondary></>
332 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
334 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
335 <filename>S99postgresql</>.
343 While the server is running, its
344 <acronym>PID</acronym> is stored in the file
345 <filename>postmaster.pid</filename> in the data directory. This is
346 used to prevent multiple server instances from
347 running in the same data directory and can also be used for
348 shutting down the server.
351 <sect2 id="server-start-failures">
352 <title>Server Start-up Failures</title>
355 There are several common reasons the server might fail to
356 start. Check the server's log file, or start it by hand (without
357 redirecting standard output or standard error) and see what error
358 messages appear. Below we explain some of the most common error
359 messages in more detail.
364 LOG: could not bind IPv4 socket: Address already in use
365 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
366 FATAL: could not create TCP/IP listen socket
368 This usually means just what it suggests: you tried to start
369 another server on the same port where one is already running.
370 However, if the kernel error message is not <computeroutput>Address
371 already in use</computeroutput> or some variant of that, there might
372 be a different problem. For example, trying to start a server
373 on a reserved port number might draw something like:
375 $ <userinput>postgres -p 666</userinput>
376 LOG: could not bind IPv4 socket: Permission denied
377 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
378 FATAL: could not create TCP/IP listen socket
385 FATAL: could not create shared memory segment: Invalid argument
386 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
388 probably means your kernel's limit on the size of shared memory is
389 smaller than the work area <productname>PostgreSQL</productname>
390 is trying to create (4011376640 bytes in this example). Or it could
391 mean that you do not have System-V-style shared memory support
392 configured into your kernel at all. As a temporary workaround, you
393 can try starting the server with a smaller-than-normal number of
394 buffers (<xref linkend="guc-shared-buffers">). You will eventually want
395 to reconfigure your kernel to increase the allowed shared memory
396 size. You might also see this message when trying to start multiple
397 servers on the same machine, if their total space requested
398 exceeds the kernel limit.
404 FATAL: could not create semaphores: No space left on device
405 DETAIL: Failed system call was semget(5440126, 17, 03600).
407 does <emphasis>not</emphasis> mean you've run out of disk
408 space. It means your kernel's limit on the number of <systemitem
409 class="osname">System V</> semaphores is smaller than the number
410 <productname>PostgreSQL</productname> wants to create. As above,
411 you might be able to work around the problem by starting the
412 server with a reduced number of allowed connections
413 (<xref linkend="guc-max-connections">), but you'll eventually want to
414 increase the kernel limit.
418 If you get an <quote>illegal system call</> error, it is likely that
419 shared memory or semaphores are not supported in your kernel at
420 all. In that case your only option is to reconfigure the kernel to
421 enable these features.
425 Details about configuring <systemitem class="osname">System V</>
426 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
430 <sect2 id="client-connection-problems">
431 <title>Client Connection Problems</title>
434 Although the error conditions possible on the client side are quite
435 varied and application-dependent, a few of them might be directly
436 related to how the server was started up. Conditions other than
437 those shown below should be documented with the respective client
443 psql: could not connect to server: Connection refused
444 Is the server running on host "server.joe.com" and accepting
445 TCP/IP connections on port 5432?
447 This is the generic <quote>I couldn't find a server to talk
448 to</quote> failure. It looks like the above when TCP/IP
449 communication is attempted. A common mistake is to forget to
450 configure the server to allow TCP/IP connections.
454 Alternatively, you'll get this when attempting Unix-domain socket
455 communication to a local server:
457 psql: could not connect to server: No such file or directory
458 Is the server running locally and accepting
459 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
464 The last line is useful in verifying that the client is trying to
465 connect to the right place. If there is in fact no server
466 running there, the kernel error message will typically be either
467 <computeroutput>Connection refused</computeroutput> or
468 <computeroutput>No such file or directory</computeroutput>, as
469 illustrated. (It is important to realize that
470 <computeroutput>Connection refused</computeroutput> in this context
471 does <emphasis>not</emphasis> mean that the server got your
472 connection request and rejected it. That case will produce a
473 different message, as shown in <xref
474 linkend="client-authentication-problems">.) Other error messages
475 such as <computeroutput>Connection timed out</computeroutput> might
476 indicate more fundamental problems, like lack of network
482 <sect1 id="kernel-resources">
483 <title>Managing Kernel Resources</title>
486 A large <productname>PostgreSQL</> installation can quickly exhaust
487 various operating system resource limits. (On some systems, the
488 factory defaults are so low that you don't even need a really
489 <quote>large</> installation.) If you have encountered this kind of
490 problem, keep reading.
494 <title>Shared Memory and Semaphores</title>
496 <indexterm zone="sysvipc">
497 <primary>shared memory</primary>
500 <indexterm zone="sysvipc">
501 <primary>semaphores</primary>
505 Shared memory and semaphores are collectively referred to as
506 <quote><systemitem class="osname">System V</>
507 <acronym>IPC</></quote> (together with message queues, which are not
508 relevant for <productname>PostgreSQL</>). Almost all modern
509 operating systems provide these features, but not all of them have
510 them turned on or sufficiently sized by default, especially systems
511 with BSD heritage. (On <systemitem class="osname">Windows</>,
512 <productname>PostgreSQL</> provides its own replacement
513 implementation of these facilities, and so most of this section
518 The complete lack of these facilities is usually manifested by an
519 <errorname>Illegal system call</> error upon server start. In
520 that case there's nothing left to do but to reconfigure your
521 kernel. <productname>PostgreSQL</> won't work without them.
525 When <productname>PostgreSQL</> exceeds one of the various hard
526 <acronym>IPC</> limits, the server will refuse to start and
527 should leave an instructive error message describing the problem
528 encountered and what to do about it. (See also <xref
529 linkend="server-start-failures">.) The relevant kernel
530 parameters are named consistently across different systems; <xref
531 linkend="sysvipc-parameters"> gives an overview. The methods to set
532 them, however, vary. Suggestions for some platforms are given below.
533 Be warned that it is often necessary to reboot your machine, and
534 possibly even recompile the kernel, to change these settings.
538 <table id="sysvipc-parameters">
539 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
545 <entry>Description</>
546 <entry>Reasonable values</>
552 <entry><varname>SHMMAX</></>
553 <entry>Maximum size of shared memory segment (bytes)</>
554 <entry>at least several megabytes (see text)</entry>
558 <entry><varname>SHMMIN</></>
559 <entry>Minimum size of shared memory segment (bytes)</>
564 <entry><varname>SHMALL</></>
565 <entry>Total amount of shared memory available (bytes or pages)</>
566 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
570 <entry><varname>SHMSEG</></>
571 <entry>Maximum number of shared memory segments per process</>
572 <entry>only 1 segment is needed, but the default is much higher</>
576 <entry><varname>SHMMNI</></>
577 <entry>Maximum number of shared memory segments system-wide</>
578 <entry>like <varname>SHMSEG</> plus room for other applications</>
582 <entry><varname>SEMMNI</></>
583 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
584 <entry>at least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</literal></>
588 <entry><varname>SEMMNS</></>
589 <entry>Maximum number of semaphores system-wide</>
590 <entry><literal>ceil((max_connections + autovacuum_max_workers) / 16) * 17</literal> plus room for other applications</>
594 <entry><varname>SEMMSL</></>
595 <entry>Maximum number of semaphores per set</>
596 <entry>at least 17</>
600 <entry><varname>SEMMAP</></>
601 <entry>Number of entries in semaphore map</>
606 <entry><varname>SEMVMX</></>
607 <entry>Maximum value of semaphore</>
608 <entry>at least 1000 (The default is often 32767, don't change unless forced to)</>
617 <indexterm><primary>SHMMAX</primary></indexterm> The most important
618 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
619 bytes, of a shared memory segment. If you get an error message from
620 <function>shmget</> like <errorname>Invalid argument</>, it is
621 likely that this limit has been exceeded. The size of the required
622 shared memory segment varies depending on several
623 <productname>PostgreSQL</> configuration parameters, as shown in
624 <xref linkend="shared-memory-parameters">. (Any error message you might
625 get will include the exact size of the failed allocation request.)
626 You can, as a temporary solution, lower some of those settings to
627 avoid the failure. While it is possible to get
628 <productname>PostgreSQL</> to run with <varname>SHMMAX</> as small as
629 2 MB, you need considerably more for acceptable performance. Desirable
630 settings are in the tens to hundreds of megabytes.
634 Some systems also have a limit on the total amount of shared memory in
635 the system (<varname>SHMALL</>). Make sure this is large enough
636 for <productname>PostgreSQL</> plus any other applications that
637 are using shared memory segments. (Caution: <varname>SHMALL</>
638 is measured in pages rather than bytes on many systems.)
642 Less likely to cause problems is the minimum size for shared
643 memory segments (<varname>SHMMIN</>), which should be at most
644 approximately 500 kB for <productname>PostgreSQL</> (it is
645 usually just 1). The maximum number of segments system-wide
646 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) are unlikely
647 to cause a problem unless your system has them set to zero.
651 <productname>PostgreSQL</> uses one semaphore per allowed connection
652 (<xref linkend="guc-max-connections">) and allowed autovacuum worker
653 process (<xref linkend="guc-autovacuum-max-workers">), in sets of 16.
655 also contain a 17th semaphore which contains a <quote>magic
656 number</quote>, to detect collision with semaphore sets used by
657 other applications. The maximum number of semaphores in the system
658 is set by <varname>SEMMNS</>, which consequently must be at least
659 as high as <varname>max_connections</> plus
660 <varname>autovacuum_max_workers</>, plus one extra for each 16
661 allowed connections plus workers (see the formula in <xref
662 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
663 determines the limit on the number of semaphore sets that can
664 exist on the system at one time. Hence this parameter must be at
665 least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</>.
667 of allowed connections is a temporary workaround for failures,
668 which are usually confusingly worded <errorname>No space
669 left on device</>, from the function <function>semget</>.
673 In some cases it might also be necessary to increase
674 <varname>SEMMAP</> to be at least on the order of
675 <varname>SEMMNS</>. This parameter defines the size of the semaphore
676 resource map, in which each contiguous block of available semaphores
677 needs an entry. When a semaphore set is freed it is either added to
678 an existing entry that is adjacent to the freed block or it is
679 registered under a new map entry. If the map is full, the freed
680 semaphores get lost (until reboot). Fragmentation of the semaphore
681 space could over time lead to fewer available semaphores than there
686 The <varname>SEMMSL</> parameter, which determines how many
687 semaphores can be in a set, must be at least 17 for
688 <productname>PostgreSQL</>.
692 Various other settings related to <quote>semaphore undo</>, such as
693 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
694 for <productname>PostgreSQL</>.
700 <term><systemitem class="osname">AIX</></term>
701 <indexterm><primary>AIX</><secondary>IPC configuration</></>
704 At least as of version 5.1, it should not be necessary to do
705 any special configuration for such parameters as
706 <varname>SHMMAX</varname>, as it appears this is configured to
707 allow all memory to be used as shared memory. That is the
708 sort of configuration commonly used for other databases such
709 as <application>DB/2</application>.</para>
711 <para> It might, however, be necessary to modify the global
712 <command>ulimit</command> information in
713 <filename>/etc/security/limits</filename>, as the default hard
714 limits for file sizes (<varname>fsize</varname>) and numbers of
715 files (<varname>nofiles</varname>) might be too low.
721 <term><systemitem class="osname">BSD/OS</></term>
722 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
725 <title>Shared Memory</>
727 By default, only 4 MB of shared memory is supported. Keep in
728 mind that shared memory is not pageable; it is locked in RAM.
729 To increase the amount of shared memory supported by your
730 system, add something like the following to your kernel configuration
733 options "SHMALL=8192"
734 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
736 <varname>SHMALL</> is measured in 4 kB pages, so a value of
737 1024 represents 4 MB of shared memory. Therefore the above increases
738 the maximum shared memory area to 32 MB.
739 For those running 4.3 or later, you will probably also need to increase
740 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
741 Once all changes have been made, recompile the kernel, and reboot.
746 For those running 4.0 and earlier releases, use <command>bpatch</>
747 to find the <varname>sysptsize</> value in the current
748 kernel. This is computed dynamically at boot time.
750 $ <userinput>bpatch -r sysptsize</>
751 <computeroutput>0x9 = 9</>
753 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
754 kernel configuration file. Increase the value you found using
755 <command>bpatch</>. Add 1 for every additional 4 MB of
756 shared memory you desire.
758 options "SYSPTSIZE=16"
760 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
766 You will probably want to increase the number of semaphores
767 as well; the default system total of 60 will only allow about
768 50 <productname>PostgreSQL</productname> connections. Set the
769 values you want in your kernel configuration file, e.g.:
781 <term><systemitem class="osname">FreeBSD</></term>
782 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
785 The default settings are only suitable for small installations
786 (for example, default <varname>SHMMAX</varname> is 32
787 MB). Changes can be made via the <command>sysctl</command> or
788 <command>loader</command> interfaces. The following
789 parameters can be set using <command>sysctl</command>:
791 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmall=32768</userinput>
792 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmmax=134217728</userinput>
793 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.semmap=256</userinput>
795 To have these settings persist over reboots, modify
796 <filename>/etc/sysctl.conf</filename>.
800 The remaining semaphore settings are read-only as far as
801 <command>sysctl</command> is concerned, but can be changed
802 before boot using the <command>loader</command> prompt:
804 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmni=256</userinput>
805 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmns=512</userinput>
806 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmnu=256</userinput>
808 Similarly these can be saved between reboots in
809 <filename>/boot/loader.conf</filename>.
813 You might also want to configure your kernel to lock shared
814 memory into RAM and prevent it from being paged out to swap.
815 This can be accomplished using the <command>sysctl</command>
816 setting <literal>kern.ipc.shm_use_phys</literal>.
820 If running in FreeBSD jails by enabling <application>sysctl</>'s
821 <literal>security.jail.sysvipc_allowed</>, <application>postmaster</>s
822 running in different jails should be run by different operating system
823 users. This improves security because it prevents non-root users
824 from interfering with shared memory or semaphores in a different jail,
825 and it allows the PostgreSQL IPC cleanup code to function properly.
826 (In FreeBSD 6.0 and later the IPC cleanup code doesn't properly detect
827 processes in other jails, preventing the running of postmasters on the
828 same port in different jails.)
832 <systemitem class="osname">FreeBSD</> versions before 4.0 work like
833 <systemitem class="osname">NetBSD</> and <systemitem class="osname">
834 OpenBSD</> (see below).
840 <term><systemitem class="osname">NetBSD</></term>
841 <term><systemitem class="osname">OpenBSD</></term>
842 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
843 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
846 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
847 to be enabled when the kernel is compiled. (They are by
848 default.) The maximum size of shared memory is determined by
849 the option <varname>SHMMAXPGS</> (in pages). The following
850 shows an example of how to set the various parameters
851 (<systemitem class="osname">OpenBSD</> uses <literal>option</> instead):
854 options SHMMAXPGS=4096
866 You might also want to configure your kernel to lock shared
867 memory into RAM and prevent it from being paged out to swap.
868 This can be accomplished using the <command>sysctl</command>
869 setting <literal>kern.ipc.shm_use_phys</literal>.
876 <term><systemitem class="osname">HP-UX</></term>
877 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
880 The default settings tend to suffice for normal installations.
881 On <productname>HP-UX</> 10, the factory default for
882 <varname>SEMMNS</> is 128, which might be too low for larger
886 <acronym>IPC</> parameters can be set in the <application>System
887 Administration Manager</> (<acronym>SAM</>) under
888 <menuchoice><guimenu>Kernel
889 Configuration</><guimenuitem>Configurable Parameters</></>. Hit
890 <guibutton>Create A New Kernel</> when you're done.
897 <term><systemitem class="osname">Linux</></term>
898 <indexterm><primary>Linux</><secondary>IPC configuration</></>
901 The default maximum segment size is 32 MB, which is only adequate
902 for small <productname>PostgreSQL</productname> installations.
903 However, the remaining
904 defaults are quite generously sized, and usually do not require
905 changes. The maximum shared memory segment size can be changed via the
906 <command>sysctl</command> interface. For example, to allow 128 MB,
907 and explicitly set the maximum total shared memory size to 2097152
910 <prompt>$</prompt> <userinput>sysctl -w kernel.shmmax=134217728</userinput>
911 <prompt>$</prompt> <userinput>sysctl -w kernel.shmall=2097152</userinput>
913 In addition these settings can be saved between reboots in
914 <filename>/etc/sysctl.conf</filename>.
918 Older distributions might not have the <command>sysctl</command> program,
919 but equivalent changes can be made by manipulating the
920 <filename>/proc</filename> file system:
922 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
923 <prompt>$</prompt> <userinput>echo 2097152 >/proc/sys/kernel/shmall</userinput>
931 <term><systemitem class="osname">MacOS X</></term>
932 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
935 In OS X 10.2 and earlier, edit the file
936 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
937 and change the values in the following commands:
939 sysctl -w kern.sysv.shmmax
940 sysctl -w kern.sysv.shmmin
941 sysctl -w kern.sysv.shmmni
942 sysctl -w kern.sysv.shmseg
943 sysctl -w kern.sysv.shmall
948 In OS X 10.3 and later, these commands have been moved to
949 <filename>/etc/rc</> and must be edited there. Note that
950 <filename>/etc/rc</> is usually overwritten by OS X updates (such as
951 10.3.6 to 10.3.7) so you should expect to have to redo your editing
956 In OS X 10.3.9 and later, instead of editing <filename>/etc/rc</>
957 you can create a file named <filename>/etc/sysctl.conf</>,
958 containing variable assignments such as:
960 kern.sysv.shmmax=4194304
964 kern.sysv.shmall=1024
966 This method is better than editing <filename>/etc/rc</> because
967 your changes will be preserved across system updates. Note that
968 <emphasis>all five</> shared-memory parameters must be set in
969 <filename>/etc/sysctl.conf</>, else the values will be ignored.
973 Beware that recent releases of OS X ignore attempts to set
974 <varname>SHMMAX</> to a value that isn't an exact multiple of 4096.
978 <varname>SHMALL</> is measured in 4 kB pages on this platform.
982 In all OS X versions, you'll need to reboot to make changes in the
983 shared memory parameters take effect.
990 <term><systemitem class="osname">SCO OpenServer</></term>
991 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
994 In the default configuration, only 512 kB of shared memory per
995 segment is allowed. To increase the setting, first change to the
996 directory <filename>/etc/conf/cf.d</>. To display the current value of
997 <varname>SHMMAX</>, run:
999 ./configure -y SHMMAX
1001 To set a new value for <varname>SHMMAX</>, run:
1003 ./configure SHMMAX=<replaceable>value</>
1005 where <replaceable>value</> is the new value you want to use
1006 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
1017 <term><systemitem class="osname">Solaris</></term>
1018 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
1021 At least in version 2.6, the default maximum size of a shared
1022 memory segment is too low for <productname>PostgreSQL</>. The
1023 relevant settings can be changed in <filename>/etc/system</>,
1026 set shmsys:shminfo_shmmax=0x2000000
1027 set shmsys:shminfo_shmmin=1
1028 set shmsys:shminfo_shmmni=256
1029 set shmsys:shminfo_shmseg=256
1031 set semsys:seminfo_semmap=256
1032 set semsys:seminfo_semmni=512
1033 set semsys:seminfo_semmns=512
1034 set semsys:seminfo_semmsl=32
1036 You need to reboot for the changes to take effect.
1041 url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
1042 for information on shared memory under
1043 <productname>Solaris</>.
1050 <term><systemitem class="osname">UnixWare</></term>
1051 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
1054 On <productname>UnixWare</> 7, the maximum size for shared
1055 memory segments is only 512 kB in the default configuration.
1056 To display the current value of <varname>SHMMAX</>, run:
1058 /etc/conf/bin/idtune -g SHMMAX
1060 which displays the current, default, minimum, and maximum
1061 values. To set a new value for <varname>SHMMAX</>,
1064 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
1066 where <replaceable>value</> is the new value you want to use
1067 (in bytes). After setting <varname>SHMMAX</>, rebuild the
1070 /etc/conf/bin/idbuild -B
1080 <table id="shared-memory-parameters">
1081 <title>Configuration parameters affecting
1082 <productname>PostgreSQL</productname>'s shared memory usage</>
1088 <entry>Approximate multiplier (bytes per increment) as of 8.3</>
1094 <entry><xref linkend="guc-max-connections"></>
1095 <entry>1800 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1099 <entry><xref linkend="guc-autovacuum-max-workers"></>
1100 <entry>1800 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1104 <entry><xref linkend="guc-max-prepared-transactions"></>
1105 <entry>770 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1109 <entry><xref linkend="guc-shared-buffers"></>
1110 <entry>8400 (assuming 8 kB <symbol>BLCKSZ</>)</entry>
1114 <entry><xref linkend="guc-wal-buffers"></>
1115 <entry>8200 (assuming 8 kB <symbol>XLOG_BLCKSZ</>)</entry>
1119 <entry><xref linkend="guc-max-fsm-relations"></>
1124 <entry><xref linkend="guc-max-fsm-pages"></>
1129 <entry>Fixed space requirements</>
1130 <entry>770 kB</entry>
1139 <title>Resource Limits</title>
1142 Unix-like operating systems enforce various kinds of resource limits
1143 that might interfere with the operation of your
1144 <productname>PostgreSQL</productname> server. Of particular
1145 importance are limits on the number of processes per user, the
1146 number of open files per process, and the amount of memory available
1147 to each process. Each of these have a <quote>hard</quote> and a
1148 <quote>soft</quote> limit. The soft limit is what actually counts
1149 but it can be changed by the user up to the hard limit. The hard
1150 limit can only be changed by the root user. The system call
1151 <function>setrlimit</function> is responsible for setting these
1152 parameters. The shell's built-in command <command>ulimit</command>
1153 (Bourne shells) or <command>limit</command> (<application>csh</>) is
1154 used to control the resource limits from the command line. On
1155 BSD-derived systems the file <filename>/etc/login.conf</filename>
1156 controls the various resource limits set during login. See the
1157 operating system documentation for details. The relevant
1158 parameters are <varname>maxproc</varname>,
1159 <varname>openfiles</varname>, and <varname>datasize</varname>. For
1164 :datasize-cur=256M:\
1166 :openfiles-cur=256:\
1169 (<literal>-cur</literal> is the soft limit. Append
1170 <literal>-max</literal> to set the hard limit.)
1174 Kernels can also have system-wide limits on some resources.
1178 On <productname>Linux</productname>
1179 <filename>/proc/sys/fs/file-max</filename> determines the
1180 maximum number of open files that the kernel will support. It can
1181 be changed by writing a different number into the file or by
1182 adding an assignment in <filename>/etc/sysctl.conf</filename>.
1183 The maximum limit of files per process is fixed at the time the
1184 kernel is compiled; see
1185 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
1193 The <productname>PostgreSQL</productname> server uses one process
1194 per connection so you should provide for at least as many processes
1195 as allowed connections, in addition to what you need for the rest
1196 of your system. This is usually not a problem but if you run
1197 several servers on one machine things might get tight.
1201 The factory default limit on open files is often set to
1202 <quote>socially friendly</quote> values that allow many users to
1203 coexist on a machine without using an inappropriate fraction of
1204 the system resources. If you run many servers on a machine this
1205 is perhaps what you want, but on dedicated servers you might want to
1210 On the other side of the coin, some systems allow individual
1211 processes to open large numbers of files; if more than a few
1212 processes do so then the system-wide limit can easily be exceeded.
1213 If you find this happening, and you do not want to alter the
1214 system-wide limit, you can set <productname>PostgreSQL</>'s <xref
1215 linkend="guc-max-files-per-process"> configuration parameter to
1216 limit the consumption of open files.
1221 <title>Linux Memory Overcommit</title>
1224 In Linux 2.4 and later, the default virtual memory behavior is not
1225 optimal for <productname>PostgreSQL</productname>. Because of the
1226 way that the kernel implements memory overcommit, the kernel might
1227 terminate the <productname>PostgreSQL</productname> server (the
1228 master server process) if the memory demands of
1229 another process cause the system to run out of virtual memory.
1233 If this happens, you will see a kernel message that looks like
1234 this (consult your system documentation and configuration on where
1235 to look for such a message):
1237 Out of Memory: Killed process 12345 (postgres).
1239 This indicates that the <filename>postgres</filename> process
1240 has been terminated due to memory pressure.
1241 Although existing database connections will continue to function
1242 normally, no new connections will be accepted. To recover,
1243 <productname>PostgreSQL</productname> will need to be restarted.
1247 One way to avoid this problem is to run
1248 <productname>PostgreSQL</productname> on a machine where you can
1249 be sure that other processes will not run the machine out of
1250 memory. If memory is tight, increasing the swap space of the
1251 operating system can help avoiding the problem, because the
1252 out-of-memory (OOM) killer is invoked whenever physical memory and
1253 swap space are exhausted.
1257 On Linux 2.6 and later, an additional measure is to modify the
1258 kernel's behavior so that it will not <quote>overcommit</> memory.
1259 Although this setting will not prevent the <ulink
1260 url="http://lwn.net/Articles/104179/">OOM killer</> from being invoked
1261 altogether, it will lower the chances significantly and will therefore
1262 lead to more robust system behavior. This is done by selecting strict
1263 overcommit mode via <command>sysctl</command>:
1265 sysctl -w vm.overcommit_memory=2
1267 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
1268 You might also wish to modify the related setting
1269 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
1270 file <filename>Documentation/vm/overcommit-accounting</>.
1274 Some vendors' Linux 2.4 kernels are reported to have early versions
1275 of the 2.6 overcommit <command>sysctl</command> parameter. However, setting
1276 <literal>vm.overcommit_memory</> to 2
1277 on a kernel that does not have the relevant code will make
1278 things worse not better. It is recommended that you inspect
1279 the actual kernel source code (see the function
1280 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
1281 to verify what is supported in your copy before you try this in a 2.4
1282 installation. The presence of the <filename>overcommit-accounting</>
1283 documentation file should <emphasis>not</> be taken as evidence that the
1284 feature is there. If in any doubt, consult a kernel expert or your
1291 <sect1 id="server-shutdown">
1292 <title>Shutting Down the Server</title>
1294 <indexterm zone="server-shutdown">
1295 <primary>shutdown</>
1299 There are several ways to shut down the database server. You control
1300 the type of shutdown by sending different signals to the master
1301 <command>postgres</command> process.
1305 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
1308 After receiving <systemitem>SIGTERM</systemitem>, the server
1309 disallows new connections, but lets existing sessions end their
1310 work normally. It shuts down only after all of the sessions
1311 terminate normally. This is the <firstterm>Smart
1312 Shutdown</firstterm>.
1318 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
1321 The server disallows new connections and sends all existing
1322 server processes <systemitem>SIGTERM</systemitem>, which will cause them
1323 to abort their current transactions and exit promptly. It then
1324 waits for the server processes to exit and finally shuts down. This is the
1325 <firstterm>Fast Shutdown</firstterm>.
1331 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
1334 This is the <firstterm>Immediate Shutdown</firstterm>, which
1335 will cause the master <command>postgres</command> process to send a
1336 <systemitem>SIGQUIT</systemitem> to all child processes and exit
1337 immediately, without properly shutting itself down. The child processes
1338 likewise exit immediately upon receiving
1339 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
1340 replaying the WAL log) upon next start-up. This is recommended
1341 only in emergencies.
1349 The <xref linkend="app-pg-ctl"> program provides a convenient
1350 interface for sending these signals to shut down the server.
1351 Alternatively, you can send the signal directly using <command>kill</>
1352 on non-Windows systems.
1353 The <acronym>PID</> of the <command>postgres</command> process can be
1354 found using the <command>ps</command> program, or from the file
1355 <filename>postmaster.pid</filename> in the data directory. For
1356 example, to do a fast shutdown:
1358 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
1364 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
1365 the server. Doing so will prevent the server from releasing
1366 shared memory and semaphores, which might then have to be done
1367 manually before a new server can be started. Furthermore,
1368 <systemitem>SIGKILL</systemitem> kills the <command>postgres</command>
1369 process without letting it relay the signal to its subprocesses,
1370 so it will be necessary to kill the individual subprocesses by hand as
1376 <sect1 id="preventing-server-spoofing">
1377 <title>Preventing Server Spoofing</title>
1379 <indexterm zone="preventing-server-spoofing">
1380 <primary>server spoofing</primary>
1384 While the server is running, it is not possible for a malicious user
1385 to interfere with client/server communications. However, when the
1386 server is down it is possible for a local user to spoof the normal
1387 server by starting their own server. The spoof server could read
1388 passwords and queries sent by clients, but could not return any data
1389 because the <varname>PGDATA</> directory would still be secure because
1390 of directory permissions. Spoofing is possible because any user can
1391 start a database server; a client cannot identify an invalid server
1392 unless it is specially configured.
1396 The simplest way to prevent invalid servers for <literal>local</>
1397 connections is to use a Unix domain socket directory (<xref
1398 linkend="guc-unix-socket-directory">) that has write permission only
1399 for a trusted local user. This prevents a malicious user from creating
1400 their own socket file in that directory. If you are concerned that
1401 some applications might still reference <filename>/tmp</> for the
1402 socket file and hence be vulnerable to spoofing, during operating system
1403 startup create symbolic link <filename>/tmp/.s.PGSQL.5432</> that points
1404 to the relocated socket file. You also might need to modify your
1405 <filename>/tmp</> cleanup script to preserve the symbolic link.
1409 For TCP connections the server
1410 must accept only <literal>hostssl</> connections (<xref
1411 linkend="auth-pg-hba-conf">) and have SSL
1412 <filename>server.key</filename> (key) and
1413 <filename>server.crt</filename> (certificate) files (<xref
1414 linkend="ssl-tcp">). The TCP client must connect using
1415 <literal>sslmode='require'</> (<xref linkend="libpq-connect">) and have
1416 a <filename>~/.postgresql/root.crt</> SSL certificate (<xref
1417 linkend="libpq-ssl">).
1421 <sect1 id="encryption-options">
1422 <title>Encryption Options</title>
1424 <indexterm zone="encryption-options">
1425 <primary>encryption</primary>
1429 <productname>PostgreSQL</productname> offers encryption at several
1430 levels, and provides flexibility in protecting data from disclosure
1431 due to database server theft, unscrupulous administrators, and
1432 insecure networks. Encryption might also be required to secure
1433 sensitive data such as medical records or financial transactions.
1439 <term>Password Storage Encryption</term>
1443 By default, database user passwords are stored as MD5 hashes, so
1444 the administrator cannot determine the actual password assigned
1445 to the user. If MD5 encryption is used for client authentication,
1446 the unencrypted password is never even temporarily present on the
1447 server because the client MD5 encrypts it before being sent
1454 <term>Encryption For Specific Columns</term>
1458 The <filename>contrib</> function library
1459 <function>pgcrypto</function> allows certain fields to be stored
1460 encrypted. This is useful if only some of the data is sensitive.
1461 The client supplies the decryption key and the data is decrypted
1462 on the server and then sent to the client.
1466 The decrypted data and the decryption key are present on the
1467 server for a brief time while it is being decrypted and
1468 communicated between the client and server. This presents a brief
1469 moment where the data and keys can be intercepted by someone with
1470 complete access to the database server, such as the system
1477 <term>Data Partition Encryption</term>
1481 On Linux, encryption can be layered on top of a file system mount
1482 using a <quote>loopback device</quote>. This allows an entire
1483 file system partition be encrypted on disk, and decrypted by the
1484 operating system. On FreeBSD, the equivalent facility is called
1485 GEOM Based Disk Encryption, or <acronym>gbde</acronym>.
1489 This mechanism prevents unencrypted data from being read from the
1490 drives if the drives or the entire computer is stolen. This does
1491 not protect against attacks while the file system is mounted,
1492 because when mounted, the operating system provides an unencrypted
1493 view of the data. However, to mount the file system, you need some
1494 way for the encryption key to be passed to the operating system,
1495 and sometimes the key is stored somewhere on the host that mounts
1502 <term>Encrypting Passwords Across A Network</term>
1506 The <literal>MD5</> authentication method double-encrypts the
1507 password on the client before sending it to the server. It first
1508 MD5 encrypts it based on the user name, and then encrypts it
1509 based on a random salt sent by the server when the database
1510 connection was made. It is this double-encrypted value that is
1511 sent over the network to the server. Double-encryption not only
1512 prevents the password from being discovered, it also prevents
1513 another connection from using the same encrypted password to
1514 connect to the database server at a later time.
1520 <term>Encrypting Data Across A Network</term>
1524 SSL connections encrypt all data sent across the network: the
1525 password, the queries, and the data returned. The
1526 <filename>pg_hba.conf</> file allows administrators to specify
1527 which hosts can use non-encrypted connections (<literal>host</>)
1528 and which require SSL-encrypted connections
1529 (<literal>hostssl</>). Also, clients can specify that they
1530 connect to servers only via SSL. <application>Stunnel</> or
1531 <application>SSH</> can also be used to encrypt transmissions.
1537 <term>SSL Host Authentication</term>
1541 It is possible for both the client and server to provide SSL keys
1542 or certificates to each other. It takes some extra configuration
1543 on each side, but this provides stronger verification of identity
1544 than the mere use of passwords. It prevents a computer from
1545 pretending to be the server just long enough to read the password
1546 send by the client. It also helps prevent "man in the middle"
1547 attacks where a computer between the client and server pretends to
1548 be the server and reads and passes all data between the client and
1555 <term>Client-Side Encryption</term>
1559 If the system administrator cannot be trusted, it is necessary
1560 for the client to encrypt the data; this way, unencrypted data
1561 never appears on the database server. Data is encrypted on the
1562 client before being sent to the server, and database results have
1563 to be decrypted on the client before being used.
1572 <sect1 id="ssl-tcp">
1573 <title>Secure TCP/IP Connections with SSL</title>
1575 <indexterm zone="ssl-tcp">
1576 <primary>SSL</primary>
1580 <productname>PostgreSQL</> has native support for using
1581 <acronym>SSL</> connections to encrypt client/server communications
1582 for increased security. This requires that
1583 <productname>OpenSSL</productname> is installed on both client and
1584 server systems and that support in <productname>PostgreSQL</> is
1585 enabled at build time (see <xref linkend="installation">).
1589 With <acronym>SSL</> support compiled in, the
1590 <productname>PostgreSQL</> server can be started with
1591 <acronym>SSL</> enabled by setting the parameter
1592 <xref linkend="guc-ssl"> to <literal>on</> in
1593 <filename>postgresql.conf</>. The server will listen for both standard
1594 and <acronym>SSL</> connections on the same TCP port, and will negotiate
1595 with any connecting client on whether to use <acronym>SSL</>. By
1596 default, this is at the client's option; see <xref
1597 linkend="auth-pg-hba-conf"> about how to set up the server to require
1598 use of <acronym>SSL</> for some or all connections.
1602 <productname>PostgreSQL</productname> reads the system-wide
1603 <productname>OpenSSL</productname> configuration file. By default, this
1604 file is named <filename>openssl.cnf</filename> and is located in the
1605 directory reported by <literal>openssl version -d</>.
1606 This default can be overridden by setting environment variable
1607 <envar>OPENSSL_CONF</envar> to the name of the desired configuration file.
1611 <productname>OpenSSL</productname> supports a wide range of ciphers
1612 and authentication algorithms, of varying strength. While a list of
1613 ciphers can be specified in the <productname>OpenSSL</productname>
1614 configuration file, you can specify ciphers specifically for use by
1615 the database server by modifying <xref linkend="guc-ssl-ciphers"> in
1616 <filename>postgresql.conf</>.
1621 It is possible to have authentication without encryption overhead by
1622 using <literal>NULL-SHA</> or <literal>NULL-MD5</> ciphers. However,
1623 a man-in-the-middle could read and pass communications between client
1624 and server. Also, encryption overhead is minimal compared to the
1625 overhead of authentication. For these reasons NULL ciphers are not
1631 To start in <acronym>SSL</> mode, the files <filename>server.crt</>
1632 and <filename>server.key</> must exist in the server's data directory.
1633 These files should contain the server certificate and private key,
1634 respectively. If the private key is protected with a passphrase, the
1635 server will prompt for the passphrase and will not start until it has
1640 To require the client to supply a trusted certificate, place
1641 certificates of the certificate authorities (<acronym>CA</acronym>)
1642 you trust in the file <filename>root.crt</filename> in the data
1643 directory. A certificate will then be requested from the client during
1644 SSL connection startup. (See <xref linkend="libpq-ssl"> for a
1645 description of how to set up client certificates.) The server will
1646 verify that the client's certificate is signed by one of the trusted
1647 certificate authorities. Certificate Revocation List (CRL) entries
1648 are also checked if the file <filename>root.crl</filename> exists.
1650 url="http://h71000.www7.hp.com/DOC/83final/BA554_90007/ch04s02.html"></>
1651 for diagrams showing SSL certificate usage.)
1655 If the <filename>root.crt</filename> file is not present, client
1656 certificates will not be requested or checked. In this mode, SSL
1657 provides encrypted communication but not authentication.
1661 The files <filename>server.key</>, <filename>server.crt</>,
1662 <filename>root.crt</filename>, and <filename>root.crl</filename>
1663 are only examined during server start; so you must restart
1664 the server for changes in them to take effect.
1667 <table id="ssl-file-usage">
1668 <title>SSL Server File Usage</title>
1673 <entry>Contents</entry>
1674 <entry>Effect</entry>
1681 <entry><filename>server.crt</></entry>
1682 <entry>server certificate</entry>
1683 <entry>requested by client</entry>
1687 <entry><filename>server.key</></entry>
1688 <entry>server private key</entry>
1689 <entry>proves server certificate sent by owner; does not indicate
1690 certificate owner is trustworthy</entry>
1694 <entry><filename>root.crt</></entry>
1695 <entry>trusted certificate authorities</entry>
1696 <entry>requests client certificate; checks certificate is
1697 signed by a trusted certificate authority</entry>
1701 <entry><filename>root.crl</></entry>
1702 <entry>certificates revoked by certificate authorities</entry>
1703 <entry>client certificate must not be on this list</entry>
1710 <sect2 id="ssl-certificate-creation">
1711 <title>Creating a Self-Signed Certificate</title>
1714 To create a quick self-signed certificate for the server, use the
1715 following <productname>OpenSSL</productname> command:
1717 openssl req -new -text -out server.req
1719 Fill out the information that <application>openssl</> asks for. Make sure
1720 you enter the local host name as <quote>Common Name</>; the challenge
1721 password can be left blank. The program will generate a key that is
1722 passphrase protected; it will not accept a passphrase that is less
1723 than four characters long. To remove the passphrase (as you must if
1724 you want automatic start-up of the server), run the commands:
1726 openssl rsa -in privkey.pem -out server.key
1729 Enter the old passphrase to unlock the existing key. Now do:
1731 openssl req -x509 -in server.req -text -key server.key -out server.crt
1732 chmod og-rwx server.key
1734 to turn the certificate into a self-signed certificate and to copy
1735 the key and certificate to where the server will look for them.
1736 For more details on how to create your server private key and
1737 certificate, refer to the <productname>OpenSSL</> documentation.
1741 A self-signed certificate can be used for testing, but a certificate
1742 signed by a certificate authority (<acronym>CA</>) (either one of the
1743 global <acronym>CAs</> or a local one) should be used in production
1744 so the client can verify the server's identity.
1751 <sect1 id="ssh-tunnels">
1752 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
1754 <indexterm zone="ssh-tunnels">
1755 <primary>ssh</primary>
1759 One can use <application>SSH</application> to encrypt the network
1760 connection between clients and a
1761 <productname>PostgreSQL</productname> server. Done properly, this
1762 provides an adequately secure network connection, even for non-SSL-capable
1767 First make sure that an <application>SSH</application> server is
1768 running properly on the same machine as the
1769 <productname>PostgreSQL</productname> server and that you can log in using
1770 <command>ssh</command> as some user. Then you can establish a secure
1771 tunnel with a command like this from the client machine:
1773 ssh -L 3333:foo.com:5432 joe@foo.com
1775 The first number in the <option>-L</option> argument, 3333, is the
1776 port number of your end of the tunnel; it can be chosen freely. The
1777 second number, 5432, is the remote end of the tunnel: the port
1778 number your server is using. The name or IP address between
1779 the port numbers is the host with the database server you are going
1780 to connect to. In order to connect to the database server using
1781 this tunnel, you connect to port 3333 on the local machine:
1783 psql -h localhost -p 3333 postgres
1785 To the database server it will then look as though you are really
1786 user <literal>joe@foo.com</literal> and it will use whatever
1787 authentication procedure was configured for connections from this
1788 user and host. Note that the server will not think the connection is
1789 SSL-encrypted, since in fact it is not encrypted between the
1790 <application>SSH</application> server and the
1791 <productname>PostgreSQL</productname> server. This should not pose any
1792 extra security risk as long as they are on the same machine.
1796 tunnel setup to succeed you must be allowed to connect via
1797 <command>ssh</command> as <literal>joe@foo.com</literal>, just
1798 as if you had attempted to use <command>ssh</command> to set up a
1804 Several other applications exist that can provide secure tunnels using
1805 a procedure similar in concept to the one just described.