1 <!-- $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.419 2008/11/04 04:18:50 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 default sort order used
149 within the particular database cluster is set by
150 <command>initdb</command>, and while you can create new databases using
151 different sort order, the order used in the template databases that initdb
152 creates cannot be changed without dropping and recreating them.
153 There is also a performance impact for using locales
154 other than <literal>C</> or <literal>POSIX</>. Therefore, it is
155 important to make this choice correctly the first time.
159 <command>initdb</command> also sets the default character set encoding
160 for the database cluster. Normally this should be chosen to match the
161 locale setting. For details see <xref linkend="multibyte">.
164 <sect2 id="creating-cluster-nfs">
165 <title>Network File Systems</title>
167 <indexterm zone="creating-cluster-nfs">
168 <primary>Network File Systems</primary>
170 <indexterm><primary><acronym>NFS</></><see>Network File Systems</></>
171 <indexterm><primary>Network Attached Storage (<acronym>NAS</>)</><see>Network File Systems</></>
174 Many installations create database clusters on network file systems.
175 Sometimes this is done directly via <acronym>NFS</>, or by using a
176 Network Attached Storage (<acronym>NAS</>) device that uses
177 <acronym>NFS</> internally. <productname>PostgreSQL</> does nothing
178 special for <acronym>NFS</> file systems, meaning it assumes
179 <acronym>NFS</> behaves exactly like locally-connected drives
180 (<acronym>DAS</>, Direct Attached Storage). If client and server
181 <acronym>NFS</> implementations have non-standard semantics, this can
182 cause reliability problems (see <ulink
183 url="http://www.time-travellers.org/shane/papers/NFS_considered_harmful.html"></ulink>).
184 Specifically, delayed (asynchronous) writes to the <acronym>NFS</>
185 server can cause reliability problems; if possible, mount
186 <acronym>NFS</> file systems synchronously (without caching) to avoid
187 this. Also, soft-mounting <acronym>NFS</> is not recommended.
188 (Storage Area Networks (<acronym>SAN</>) use a low-level
189 communication protocol rather than <acronym>NFS</>.)
196 <sect1 id="server-start">
197 <title>Starting the Database Server</title>
200 Before anyone can access the database, you must start the database
201 server. The database server program is called
202 <command>postgres</command>.<indexterm><primary>postgres</></>
203 The <command>postgres</command> program must know where to
204 find the data it is supposed to use. This is done with the
205 <option>-D</option> option. Thus, the simplest way to start the
208 $ <userinput>postgres -D /usr/local/pgsql/data</userinput>
210 which will leave the server running in the foreground. This must be
211 done while logged into the <productname>PostgreSQL</productname> user
212 account. Without <option>-D</option>, the server will try to use
213 the data directory named by the environment variable <envar>PGDATA</envar>.
214 If that variable is not provided either, it will fail.
218 Normally it is better to start <command>postgres</command> in the
219 background. For this, use the usual shell syntax:
221 $ <userinput>postgres -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
223 It is important to store the server's <systemitem>stdout</> and
224 <systemitem>stderr</> output somewhere, as shown above. It will help
225 for auditing purposes and to diagnose problems. (See <xref
226 linkend="logfile-maintenance"> for a more thorough discussion of log
231 The <command>postgres</command> program also takes a number of other
232 command-line options. For more information, see the
233 <xref linkend="app-postgres"> reference page
234 and <xref linkend="runtime-config"> below.
238 This shell syntax can get tedious quickly. Therefore the wrapper
240 <xref linkend="app-pg-ctl"><indexterm><primary>pg_ctl</primary></indexterm>
241 is provided to simplify some tasks. For example:
243 pg_ctl start -l logfile
245 will start the server in the background and put the output into the
246 named log file. The <option>-D</option> option has the same meaning
247 here as for <command>postgres</command>. <command>pg_ctl</command>
248 is also capable of stopping the server.
252 Normally, you will want to start the database server when the
253 computer boots.<indexterm><primary>booting</><secondary>starting
254 the server during</></> Autostart scripts are operating-system-specific.
255 There are a few distributed with
256 <productname>PostgreSQL</productname> in the
257 <filename>contrib/start-scripts</> directory. Installing one will require
262 Different systems have different conventions for starting up daemons
263 at boot time. Many systems have a file
264 <filename>/etc/rc.local</filename> or
265 <filename>/etc/rc.d/rc.local</filename>. Others use
266 <filename>rc.d</> directories. Whatever you do, the server must be
267 run by the <productname>PostgreSQL</productname> user account
268 <emphasis>and not by root</emphasis> or any other user. Therefore you
269 probably should form your commands using <literal>su -c '...'
270 postgres</literal>. For example:
272 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
277 Here are a few more operating-system-specific suggestions. (In each
278 case be sure to use the proper installation directory and user
279 name where we show generic values.)
284 For <productname>FreeBSD</productname>, look at the file
285 <filename>contrib/start-scripts/freebsd</filename> in the
286 <productname>PostgreSQL</productname> source distribution.
287 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
293 On <productname>OpenBSD</productname>, add the following lines
294 to the file <filename>/etc/rc.local</filename>:
295 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
297 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
298 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
299 echo -n ' postgresql'
307 On <productname>Linux</productname> systems either add
308 <indexterm><primary>Linux</><secondary>start script</secondary></>
310 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
312 to <filename>/etc/rc.d/rc.local</filename> or look at the file
313 <filename>contrib/start-scripts/linux</filename> in the
314 <productname>PostgreSQL</productname> source distribution.
320 On <productname>NetBSD</productname>, either use the
321 <productname>FreeBSD</productname> or
322 <productname>Linux</productname> start scripts, depending on
323 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
329 On <productname>Solaris</productname>, create a file called
330 <filename>/etc/init.d/postgresql</filename> that contains
332 <indexterm><primary>Solaris</><secondary>start script</secondary></>
334 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
336 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
337 <filename>S99postgresql</>.
345 While the server is running, its
346 <acronym>PID</acronym> is stored in the file
347 <filename>postmaster.pid</filename> in the data directory. This is
348 used to prevent multiple server instances from
349 running in the same data directory and can also be used for
350 shutting down the server.
353 <sect2 id="server-start-failures">
354 <title>Server Start-up Failures</title>
357 There are several common reasons the server might fail to
358 start. Check the server's log file, or start it by hand (without
359 redirecting standard output or standard error) and see what error
360 messages appear. Below we explain some of the most common error
361 messages in more detail.
366 LOG: could not bind IPv4 socket: Address already in use
367 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
368 FATAL: could not create TCP/IP listen socket
370 This usually means just what it suggests: you tried to start
371 another server on the same port where one is already running.
372 However, if the kernel error message is not <computeroutput>Address
373 already in use</computeroutput> or some variant of that, there might
374 be a different problem. For example, trying to start a server
375 on a reserved port number might draw something like:
377 $ <userinput>postgres -p 666</userinput>
378 LOG: could not bind IPv4 socket: Permission denied
379 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
380 FATAL: could not create TCP/IP listen socket
387 FATAL: could not create shared memory segment: Invalid argument
388 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
390 probably means your kernel's limit on the size of shared memory is
391 smaller than the work area <productname>PostgreSQL</productname>
392 is trying to create (4011376640 bytes in this example). Or it could
393 mean that you do not have System-V-style shared memory support
394 configured into your kernel at all. As a temporary workaround, you
395 can try starting the server with a smaller-than-normal number of
396 buffers (<xref linkend="guc-shared-buffers">). You will eventually want
397 to reconfigure your kernel to increase the allowed shared memory
398 size. You might also see this message when trying to start multiple
399 servers on the same machine, if their total space requested
400 exceeds the kernel limit.
406 FATAL: could not create semaphores: No space left on device
407 DETAIL: Failed system call was semget(5440126, 17, 03600).
409 does <emphasis>not</emphasis> mean you've run out of disk
410 space. It means your kernel's limit on the number of <systemitem
411 class="osname">System V</> semaphores is smaller than the number
412 <productname>PostgreSQL</productname> wants to create. As above,
413 you might be able to work around the problem by starting the
414 server with a reduced number of allowed connections
415 (<xref linkend="guc-max-connections">), but you'll eventually want to
416 increase the kernel limit.
420 If you get an <quote>illegal system call</> error, it is likely that
421 shared memory or semaphores are not supported in your kernel at
422 all. In that case your only option is to reconfigure the kernel to
423 enable these features.
427 Details about configuring <systemitem class="osname">System V</>
428 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
432 <sect2 id="client-connection-problems">
433 <title>Client Connection Problems</title>
436 Although the error conditions possible on the client side are quite
437 varied and application-dependent, a few of them might be directly
438 related to how the server was started up. Conditions other than
439 those shown below should be documented with the respective client
445 psql: could not connect to server: Connection refused
446 Is the server running on host "server.joe.com" and accepting
447 TCP/IP connections on port 5432?
449 This is the generic <quote>I couldn't find a server to talk
450 to</quote> failure. It looks like the above when TCP/IP
451 communication is attempted. A common mistake is to forget to
452 configure the server to allow TCP/IP connections.
456 Alternatively, you'll get this when attempting Unix-domain socket
457 communication to a local server:
459 psql: could not connect to server: No such file or directory
460 Is the server running locally and accepting
461 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
466 The last line is useful in verifying that the client is trying to
467 connect to the right place. If there is in fact no server
468 running there, the kernel error message will typically be either
469 <computeroutput>Connection refused</computeroutput> or
470 <computeroutput>No such file or directory</computeroutput>, as
471 illustrated. (It is important to realize that
472 <computeroutput>Connection refused</computeroutput> in this context
473 does <emphasis>not</emphasis> mean that the server got your
474 connection request and rejected it. That case will produce a
475 different message, as shown in <xref
476 linkend="client-authentication-problems">.) Other error messages
477 such as <computeroutput>Connection timed out</computeroutput> might
478 indicate more fundamental problems, like lack of network
484 <sect1 id="kernel-resources">
485 <title>Managing Kernel Resources</title>
488 A large <productname>PostgreSQL</> installation can quickly exhaust
489 various operating system resource limits. (On some systems, the
490 factory defaults are so low that you don't even need a really
491 <quote>large</> installation.) If you have encountered this kind of
492 problem, keep reading.
496 <title>Shared Memory and Semaphores</title>
498 <indexterm zone="sysvipc">
499 <primary>shared memory</primary>
502 <indexterm zone="sysvipc">
503 <primary>semaphores</primary>
507 Shared memory and semaphores are collectively referred to as
508 <quote><systemitem class="osname">System V</>
509 <acronym>IPC</></quote> (together with message queues, which are not
510 relevant for <productname>PostgreSQL</>). Almost all modern
511 operating systems provide these features, but not all of them have
512 them turned on or sufficiently sized by default, especially systems
513 with BSD heritage. (On <systemitem class="osname">Windows</>,
514 <productname>PostgreSQL</> provides its own replacement
515 implementation of these facilities, and so most of this section
520 The complete lack of these facilities is usually manifested by an
521 <errorname>Illegal system call</> error upon server start. In
522 that case there's nothing left to do but to reconfigure your
523 kernel. <productname>PostgreSQL</> won't work without them.
527 When <productname>PostgreSQL</> exceeds one of the various hard
528 <acronym>IPC</> limits, the server will refuse to start and
529 should leave an instructive error message describing the problem
530 encountered and what to do about it. (See also <xref
531 linkend="server-start-failures">.) The relevant kernel
532 parameters are named consistently across different systems; <xref
533 linkend="sysvipc-parameters"> gives an overview. The methods to set
534 them, however, vary. Suggestions for some platforms are given below.
535 Be warned that it is often necessary to reboot your machine, and
536 possibly even recompile the kernel, to change these settings.
540 <table id="sysvipc-parameters">
541 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
547 <entry>Description</>
548 <entry>Reasonable values</>
554 <entry><varname>SHMMAX</></>
555 <entry>Maximum size of shared memory segment (bytes)</>
556 <entry>at least several megabytes (see text)</entry>
560 <entry><varname>SHMMIN</></>
561 <entry>Minimum size of shared memory segment (bytes)</>
566 <entry><varname>SHMALL</></>
567 <entry>Total amount of shared memory available (bytes or pages)</>
568 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
572 <entry><varname>SHMSEG</></>
573 <entry>Maximum number of shared memory segments per process</>
574 <entry>only 1 segment is needed, but the default is much higher</>
578 <entry><varname>SHMMNI</></>
579 <entry>Maximum number of shared memory segments system-wide</>
580 <entry>like <varname>SHMSEG</> plus room for other applications</>
584 <entry><varname>SEMMNI</></>
585 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
586 <entry>at least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</literal></>
590 <entry><varname>SEMMNS</></>
591 <entry>Maximum number of semaphores system-wide</>
592 <entry><literal>ceil((max_connections + autovacuum_max_workers) / 16) * 17</literal> plus room for other applications</>
596 <entry><varname>SEMMSL</></>
597 <entry>Maximum number of semaphores per set</>
598 <entry>at least 17</>
602 <entry><varname>SEMMAP</></>
603 <entry>Number of entries in semaphore map</>
608 <entry><varname>SEMVMX</></>
609 <entry>Maximum value of semaphore</>
610 <entry>at least 1000 (The default is often 32767, don't change unless forced to)</>
619 <indexterm><primary>SHMMAX</primary></indexterm> The most important
620 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
621 bytes, of a shared memory segment. If you get an error message from
622 <function>shmget</> like <errorname>Invalid argument</>, it is
623 likely that this limit has been exceeded. The size of the required
624 shared memory segment varies depending on several
625 <productname>PostgreSQL</> configuration parameters, as shown in
626 <xref linkend="shared-memory-parameters">. (Any error message you might
627 get will include the exact size of the failed allocation request.)
628 You can, as a temporary solution, lower some of those settings to
629 avoid the failure. While it is possible to get
630 <productname>PostgreSQL</> to run with <varname>SHMMAX</> as small as
631 2 MB, you need considerably more for acceptable performance. Desirable
632 settings are in the tens to hundreds of megabytes.
636 Some systems also have a limit on the total amount of shared memory in
637 the system (<varname>SHMALL</>). Make sure this is large enough
638 for <productname>PostgreSQL</> plus any other applications that
639 are using shared memory segments. (Caution: <varname>SHMALL</>
640 is measured in pages rather than bytes on many systems.)
644 Less likely to cause problems is the minimum size for shared
645 memory segments (<varname>SHMMIN</>), which should be at most
646 approximately 500 kB for <productname>PostgreSQL</> (it is
647 usually just 1). The maximum number of segments system-wide
648 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) are unlikely
649 to cause a problem unless your system has them set to zero.
653 <productname>PostgreSQL</> uses one semaphore per allowed connection
654 (<xref linkend="guc-max-connections">) and allowed autovacuum worker
655 process (<xref linkend="guc-autovacuum-max-workers">), in sets of 16.
657 also contain a 17th semaphore which contains a <quote>magic
658 number</quote>, to detect collision with semaphore sets used by
659 other applications. The maximum number of semaphores in the system
660 is set by <varname>SEMMNS</>, which consequently must be at least
661 as high as <varname>max_connections</> plus
662 <varname>autovacuum_max_workers</>, plus one extra for each 16
663 allowed connections plus workers (see the formula in <xref
664 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
665 determines the limit on the number of semaphore sets that can
666 exist on the system at one time. Hence this parameter must be at
667 least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</>.
669 of allowed connections is a temporary workaround for failures,
670 which are usually confusingly worded <errorname>No space
671 left on device</>, from the function <function>semget</>.
675 In some cases it might also be necessary to increase
676 <varname>SEMMAP</> to be at least on the order of
677 <varname>SEMMNS</>. This parameter defines the size of the semaphore
678 resource map, in which each contiguous block of available semaphores
679 needs an entry. When a semaphore set is freed it is either added to
680 an existing entry that is adjacent to the freed block or it is
681 registered under a new map entry. If the map is full, the freed
682 semaphores get lost (until reboot). Fragmentation of the semaphore
683 space could over time lead to fewer available semaphores than there
688 The <varname>SEMMSL</> parameter, which determines how many
689 semaphores can be in a set, must be at least 17 for
690 <productname>PostgreSQL</>.
694 Various other settings related to <quote>semaphore undo</>, such as
695 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
696 for <productname>PostgreSQL</>.
702 <term><systemitem class="osname">AIX</></term>
703 <indexterm><primary>AIX</><secondary>IPC configuration</></>
706 At least as of version 5.1, it should not be necessary to do
707 any special configuration for such parameters as
708 <varname>SHMMAX</varname>, as it appears this is configured to
709 allow all memory to be used as shared memory. That is the
710 sort of configuration commonly used for other databases such
711 as <application>DB/2</application>.</para>
713 <para> It might, however, be necessary to modify the global
714 <command>ulimit</command> information in
715 <filename>/etc/security/limits</filename>, as the default hard
716 limits for file sizes (<varname>fsize</varname>) and numbers of
717 files (<varname>nofiles</varname>) might be too low.
723 <term><systemitem class="osname">BSD/OS</></term>
724 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
727 <title>Shared Memory</>
729 By default, only 4 MB of shared memory is supported. Keep in
730 mind that shared memory is not pageable; it is locked in RAM.
731 To increase the amount of shared memory supported by your
732 system, add something like the following to your kernel configuration
735 options "SHMALL=8192"
736 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
738 <varname>SHMALL</> is measured in 4 kB pages, so a value of
739 1024 represents 4 MB of shared memory. Therefore the above increases
740 the maximum shared memory area to 32 MB.
741 For those running 4.3 or later, you will probably also need to increase
742 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
743 Once all changes have been made, recompile the kernel, and reboot.
748 For those running 4.0 and earlier releases, use <command>bpatch</>
749 to find the <varname>sysptsize</> value in the current
750 kernel. This is computed dynamically at boot time.
752 $ <userinput>bpatch -r sysptsize</>
753 <computeroutput>0x9 = 9</>
755 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
756 kernel configuration file. Increase the value you found using
757 <command>bpatch</>. Add 1 for every additional 4 MB of
758 shared memory you desire.
760 options "SYSPTSIZE=16"
762 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
768 You will probably want to increase the number of semaphores
769 as well; the default system total of 60 will only allow about
770 50 <productname>PostgreSQL</productname> connections. Set the
771 values you want in your kernel configuration file, e.g.:
783 <term><systemitem class="osname">FreeBSD</></term>
784 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
787 The default settings are only suitable for small installations
788 (for example, default <varname>SHMMAX</varname> is 32
789 MB). Changes can be made via the <command>sysctl</command> or
790 <command>loader</command> interfaces. The following
791 parameters can be set using <command>sysctl</command>:
793 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmall=32768</userinput>
794 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmmax=134217728</userinput>
795 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.semmap=256</userinput>
797 To have these settings persist over reboots, modify
798 <filename>/etc/sysctl.conf</filename>.
802 The remaining semaphore settings are read-only as far as
803 <command>sysctl</command> is concerned, but can be changed
804 before boot using the <command>loader</command> prompt:
806 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmni=256</userinput>
807 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmns=512</userinput>
808 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmnu=256</userinput>
810 Similarly these can be saved between reboots in
811 <filename>/boot/loader.conf</filename>.
815 You might also want to configure your kernel to lock shared
816 memory into RAM and prevent it from being paged out to swap.
817 This can be accomplished using the <command>sysctl</command>
818 setting <literal>kern.ipc.shm_use_phys</literal>.
822 If running in FreeBSD jails by enabling <application>sysctl</>'s
823 <literal>security.jail.sysvipc_allowed</>, <application>postmaster</>s
824 running in different jails should be run by different operating system
825 users. This improves security because it prevents non-root users
826 from interfering with shared memory or semaphores in a different jail,
827 and it allows the PostgreSQL IPC cleanup code to function properly.
828 (In FreeBSD 6.0 and later the IPC cleanup code doesn't properly detect
829 processes in other jails, preventing the running of postmasters on the
830 same port in different jails.)
834 <systemitem class="osname">FreeBSD</> versions before 4.0 work like
835 <systemitem class="osname">NetBSD</> and <systemitem class="osname">
836 OpenBSD</> (see below).
842 <term><systemitem class="osname">NetBSD</></term>
843 <term><systemitem class="osname">OpenBSD</></term>
844 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
845 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
848 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
849 to be enabled when the kernel is compiled. (They are by
850 default.) The maximum size of shared memory is determined by
851 the option <varname>SHMMAXPGS</> (in pages). The following
852 shows an example of how to set the various parameters
853 (<systemitem class="osname">OpenBSD</> uses <literal>option</> instead):
856 options SHMMAXPGS=4096
868 You might also want to configure your kernel to lock shared
869 memory into RAM and prevent it from being paged out to swap.
870 This can be accomplished using the <command>sysctl</command>
871 setting <literal>kern.ipc.shm_use_phys</literal>.
878 <term><systemitem class="osname">HP-UX</></term>
879 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
882 The default settings tend to suffice for normal installations.
883 On <productname>HP-UX</> 10, the factory default for
884 <varname>SEMMNS</> is 128, which might be too low for larger
888 <acronym>IPC</> parameters can be set in the <application>System
889 Administration Manager</> (<acronym>SAM</>) under
890 <menuchoice><guimenu>Kernel
891 Configuration</><guimenuitem>Configurable Parameters</></>. Hit
892 <guibutton>Create A New Kernel</> when you're done.
899 <term><systemitem class="osname">Linux</></term>
900 <indexterm><primary>Linux</><secondary>IPC configuration</></>
903 The default maximum segment size is 32 MB, which is only adequate
904 for small <productname>PostgreSQL</productname> installations.
905 However, the remaining
906 defaults are quite generously sized, and usually do not require
907 changes. The maximum shared memory segment size can be changed via the
908 <command>sysctl</command> interface. For example, to allow 128 MB,
909 and explicitly set the maximum total shared memory size to 2097152
912 <prompt>$</prompt> <userinput>sysctl -w kernel.shmmax=134217728</userinput>
913 <prompt>$</prompt> <userinput>sysctl -w kernel.shmall=2097152</userinput>
915 In addition these settings can be saved between reboots in
916 <filename>/etc/sysctl.conf</filename>.
920 Older distributions might not have the <command>sysctl</command> program,
921 but equivalent changes can be made by manipulating the
922 <filename>/proc</filename> file system:
924 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
925 <prompt>$</prompt> <userinput>echo 2097152 >/proc/sys/kernel/shmall</userinput>
933 <term><systemitem class="osname">MacOS X</></term>
934 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
937 In OS X 10.2 and earlier, edit the file
938 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
939 and change the values in the following commands:
941 sysctl -w kern.sysv.shmmax
942 sysctl -w kern.sysv.shmmin
943 sysctl -w kern.sysv.shmmni
944 sysctl -w kern.sysv.shmseg
945 sysctl -w kern.sysv.shmall
950 In OS X 10.3 and later, these commands have been moved to
951 <filename>/etc/rc</> and must be edited there. Note that
952 <filename>/etc/rc</> is usually overwritten by OS X updates (such as
953 10.3.6 to 10.3.7) so you should expect to have to redo your editing
958 In OS X 10.3.9 and later, instead of editing <filename>/etc/rc</>
959 you can create a file named <filename>/etc/sysctl.conf</>,
960 containing variable assignments such as:
962 kern.sysv.shmmax=4194304
966 kern.sysv.shmall=1024
968 This method is better than editing <filename>/etc/rc</> because
969 your changes will be preserved across system updates. Note that
970 <emphasis>all five</> shared-memory parameters must be set in
971 <filename>/etc/sysctl.conf</>, else the values will be ignored.
975 Beware that recent releases of OS X ignore attempts to set
976 <varname>SHMMAX</> to a value that isn't an exact multiple of 4096.
980 <varname>SHMALL</> is measured in 4 kB pages on this platform.
984 In all OS X versions, you'll need to reboot to make changes in the
985 shared memory parameters take effect.
992 <term><systemitem class="osname">SCO OpenServer</></term>
993 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
996 In the default configuration, only 512 kB of shared memory per
997 segment is allowed. To increase the setting, first change to the
998 directory <filename>/etc/conf/cf.d</>. To display the current value of
999 <varname>SHMMAX</>, run:
1001 ./configure -y SHMMAX
1003 To set a new value for <varname>SHMMAX</>, run:
1005 ./configure SHMMAX=<replaceable>value</>
1007 where <replaceable>value</> is the new value you want to use
1008 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
1019 <term><systemitem class="osname">Solaris</></term>
1020 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
1023 At least in version 2.6, the default maximum size of a shared
1024 memory segment is too low for <productname>PostgreSQL</>. The
1025 relevant settings can be changed in <filename>/etc/system</>,
1028 set shmsys:shminfo_shmmax=0x2000000
1029 set shmsys:shminfo_shmmin=1
1030 set shmsys:shminfo_shmmni=256
1031 set shmsys:shminfo_shmseg=256
1033 set semsys:seminfo_semmap=256
1034 set semsys:seminfo_semmni=512
1035 set semsys:seminfo_semmns=512
1036 set semsys:seminfo_semmsl=32
1038 You need to reboot for the changes to take effect.
1043 url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
1044 for information on shared memory under
1045 <productname>Solaris</>.
1052 <term><systemitem class="osname">UnixWare</></term>
1053 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
1056 On <productname>UnixWare</> 7, the maximum size for shared
1057 memory segments is only 512 kB in the default configuration.
1058 To display the current value of <varname>SHMMAX</>, run:
1060 /etc/conf/bin/idtune -g SHMMAX
1062 which displays the current, default, minimum, and maximum
1063 values. To set a new value for <varname>SHMMAX</>,
1066 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
1068 where <replaceable>value</> is the new value you want to use
1069 (in bytes). After setting <varname>SHMMAX</>, rebuild the
1072 /etc/conf/bin/idbuild -B
1082 <table id="shared-memory-parameters">
1083 <title>Configuration parameters affecting
1084 <productname>PostgreSQL</productname>'s shared memory usage</>
1090 <entry>Approximate multiplier (bytes per increment) as of 8.3</>
1096 <entry><xref linkend="guc-max-connections"></>
1097 <entry>1800 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1101 <entry><xref linkend="guc-autovacuum-max-workers"></>
1102 <entry>1800 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1106 <entry><xref linkend="guc-max-prepared-transactions"></>
1107 <entry>770 + 270 * <xref linkend="guc-max-locks-per-transaction"></entry>
1111 <entry><xref linkend="guc-shared-buffers"></>
1112 <entry>8400 (assuming 8 kB <symbol>BLCKSZ</>)</entry>
1116 <entry><xref linkend="guc-wal-buffers"></>
1117 <entry>8200 (assuming 8 kB <symbol>XLOG_BLCKSZ</>)</entry>
1121 <entry>Fixed space requirements</>
1122 <entry>770 kB</entry>
1131 <title>Resource Limits</title>
1134 Unix-like operating systems enforce various kinds of resource limits
1135 that might interfere with the operation of your
1136 <productname>PostgreSQL</productname> server. Of particular
1137 importance are limits on the number of processes per user, the
1138 number of open files per process, and the amount of memory available
1139 to each process. Each of these have a <quote>hard</quote> and a
1140 <quote>soft</quote> limit. The soft limit is what actually counts
1141 but it can be changed by the user up to the hard limit. The hard
1142 limit can only be changed by the root user. The system call
1143 <function>setrlimit</function> is responsible for setting these
1144 parameters. The shell's built-in command <command>ulimit</command>
1145 (Bourne shells) or <command>limit</command> (<application>csh</>) is
1146 used to control the resource limits from the command line. On
1147 BSD-derived systems the file <filename>/etc/login.conf</filename>
1148 controls the various resource limits set during login. See the
1149 operating system documentation for details. The relevant
1150 parameters are <varname>maxproc</varname>,
1151 <varname>openfiles</varname>, and <varname>datasize</varname>. For
1156 :datasize-cur=256M:\
1158 :openfiles-cur=256:\
1161 (<literal>-cur</literal> is the soft limit. Append
1162 <literal>-max</literal> to set the hard limit.)
1166 Kernels can also have system-wide limits on some resources.
1170 On <productname>Linux</productname>
1171 <filename>/proc/sys/fs/file-max</filename> determines the
1172 maximum number of open files that the kernel will support. It can
1173 be changed by writing a different number into the file or by
1174 adding an assignment in <filename>/etc/sysctl.conf</filename>.
1175 The maximum limit of files per process is fixed at the time the
1176 kernel is compiled; see
1177 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
1185 The <productname>PostgreSQL</productname> server uses one process
1186 per connection so you should provide for at least as many processes
1187 as allowed connections, in addition to what you need for the rest
1188 of your system. This is usually not a problem but if you run
1189 several servers on one machine things might get tight.
1193 The factory default limit on open files is often set to
1194 <quote>socially friendly</quote> values that allow many users to
1195 coexist on a machine without using an inappropriate fraction of
1196 the system resources. If you run many servers on a machine this
1197 is perhaps what you want, but on dedicated servers you might want to
1202 On the other side of the coin, some systems allow individual
1203 processes to open large numbers of files; if more than a few
1204 processes do so then the system-wide limit can easily be exceeded.
1205 If you find this happening, and you do not want to alter the
1206 system-wide limit, you can set <productname>PostgreSQL</>'s <xref
1207 linkend="guc-max-files-per-process"> configuration parameter to
1208 limit the consumption of open files.
1213 <title>Linux Memory Overcommit</title>
1216 In Linux 2.4 and later, the default virtual memory behavior is not
1217 optimal for <productname>PostgreSQL</productname>. Because of the
1218 way that the kernel implements memory overcommit, the kernel might
1219 terminate the <productname>PostgreSQL</productname> server (the
1220 master server process) if the memory demands of
1221 another process cause the system to run out of virtual memory.
1225 If this happens, you will see a kernel message that looks like
1226 this (consult your system documentation and configuration on where
1227 to look for such a message):
1229 Out of Memory: Killed process 12345 (postgres).
1231 This indicates that the <filename>postgres</filename> process
1232 has been terminated due to memory pressure.
1233 Although existing database connections will continue to function
1234 normally, no new connections will be accepted. To recover,
1235 <productname>PostgreSQL</productname> will need to be restarted.
1239 One way to avoid this problem is to run
1240 <productname>PostgreSQL</productname> on a machine where you can
1241 be sure that other processes will not run the machine out of
1242 memory. If memory is tight, increasing the swap space of the
1243 operating system can help avoiding the problem, because the
1244 out-of-memory (OOM) killer is invoked whenever physical memory and
1245 swap space are exhausted.
1249 On Linux 2.6 and later, an additional measure is to modify the
1250 kernel's behavior so that it will not <quote>overcommit</> memory.
1251 Although this setting will not prevent the <ulink
1252 url="http://lwn.net/Articles/104179/">OOM killer</> from being invoked
1253 altogether, it will lower the chances significantly and will therefore
1254 lead to more robust system behavior. This is done by selecting strict
1255 overcommit mode via <command>sysctl</command>:
1257 sysctl -w vm.overcommit_memory=2
1259 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
1260 You might also wish to modify the related setting
1261 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
1262 file <filename>Documentation/vm/overcommit-accounting</>.
1266 Some vendors' Linux 2.4 kernels are reported to have early versions
1267 of the 2.6 overcommit <command>sysctl</command> parameter. However, setting
1268 <literal>vm.overcommit_memory</> to 2
1269 on a kernel that does not have the relevant code will make
1270 things worse not better. It is recommended that you inspect
1271 the actual kernel source code (see the function
1272 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
1273 to verify what is supported in your copy before you try this in a 2.4
1274 installation. The presence of the <filename>overcommit-accounting</>
1275 documentation file should <emphasis>not</> be taken as evidence that the
1276 feature is there. If in any doubt, consult a kernel expert or your
1283 <sect1 id="server-shutdown">
1284 <title>Shutting Down the Server</title>
1286 <indexterm zone="server-shutdown">
1287 <primary>shutdown</>
1291 There are several ways to shut down the database server. You control
1292 the type of shutdown by sending different signals to the master
1293 <command>postgres</command> process.
1297 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
1300 This is the <firstterm>Smart Shutdown</firstterm> mode.
1301 After receiving <systemitem>SIGTERM</systemitem>, the server
1302 disallows new connections, but lets existing sessions end their
1303 work normally. It shuts down only after all of the sessions terminate.
1304 If the server is in online backup mode, it additionally waits
1305 until online backup mode is no longer active. While backup mode is
1306 active, new connections will still be allowed, but only to superusers
1307 (this exception allows a superuser to connect to terminate
1308 online backup mode).
1314 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
1317 This is the <firstterm>Fast Shutdown</firstterm> mode.
1318 The server disallows new connections and sends all existing
1319 server processes <systemitem>SIGTERM</systemitem>, which will cause them
1320 to abort their current transactions and exit promptly. It then
1321 waits for the server processes to exit and finally shuts down.
1322 If the server is in online backup mode, backup mode will be
1323 terminated, rendering the backup useless.
1329 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
1332 This is the <firstterm>Immediate Shutdown</firstterm> mode.
1333 The master <command>postgres</command> process will send a
1334 <systemitem>SIGQUIT</systemitem> to all child processes and exit
1335 immediately, without properly shutting itself down. The child processes
1336 likewise exit immediately upon receiving
1337 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
1338 replaying the WAL log) upon next start-up. This is recommended
1339 only in emergencies.
1347 The <xref linkend="app-pg-ctl"> program provides a convenient
1348 interface for sending these signals to shut down the server.
1349 Alternatively, you can send the signal directly using <command>kill</>
1350 on non-Windows systems.
1351 The <acronym>PID</> of the <command>postgres</command> process can be
1352 found using the <command>ps</command> program, or from the file
1353 <filename>postmaster.pid</filename> in the data directory. For
1354 example, to do a fast shutdown:
1356 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
1362 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
1363 the server. Doing so will prevent the server from releasing
1364 shared memory and semaphores, which might then have to be done
1365 manually before a new server can be started. Furthermore,
1366 <systemitem>SIGKILL</systemitem> kills the <command>postgres</command>
1367 process without letting it relay the signal to its subprocesses,
1368 so it will be necessary to kill the individual subprocesses by hand as
1374 To terminate an individual session while allowing other sessions to
1375 continue, use <function>pg_terminate_backend()</> (see <xref
1376 linkend="functions-admin-signal-table">) or send a
1377 <systemitem>SIGTERM</> signal to the child process associated with
1382 <sect1 id="preventing-server-spoofing">
1383 <title>Preventing Server Spoofing</title>
1385 <indexterm zone="preventing-server-spoofing">
1386 <primary>server spoofing</primary>
1390 While the server is running, it is not possible for a malicious user
1391 to interfere with client/server communications. However, when the
1392 server is down it is possible for a local user to spoof the normal
1393 server by starting their own server. The spoof server could read
1394 passwords and queries sent by clients, but could not return any data
1395 because the <varname>PGDATA</> directory would still be secure because
1396 of directory permissions. Spoofing is possible because any user can
1397 start a database server; a client cannot identify an invalid server
1398 unless it is specially configured.
1402 The simplest way to prevent invalid servers for <literal>local</>
1403 connections is to use a Unix domain socket directory (<xref
1404 linkend="guc-unix-socket-directory">) that has write permission only
1405 for a trusted local user. This prevents a malicious user from creating
1406 their own socket file in that directory. If you are concerned that
1407 some applications might still reference <filename>/tmp</> for the
1408 socket file and hence be vulnerable to spoofing, during operating system
1409 startup create symbolic link <filename>/tmp/.s.PGSQL.5432</> that points
1410 to the relocated socket file. You also might need to modify your
1411 <filename>/tmp</> cleanup script to preserve the symbolic link.
1415 For TCP connections the server
1416 must accept only <literal>hostssl</> connections (<xref
1417 linkend="auth-pg-hba-conf">) and have SSL
1418 <filename>server.key</filename> (key) and
1419 <filename>server.crt</filename> (certificate) files (<xref
1420 linkend="ssl-tcp">). The TCP client must connect using
1421 <literal>sslmode='require'</> (<xref linkend="libpq-connect">) and have
1422 a <filename>~/.postgresql/root.crt</> SSL certificate (<xref
1423 linkend="libpq-ssl">).
1427 <sect1 id="encryption-options">
1428 <title>Encryption Options</title>
1430 <indexterm zone="encryption-options">
1431 <primary>encryption</primary>
1435 <productname>PostgreSQL</productname> offers encryption at several
1436 levels, and provides flexibility in protecting data from disclosure
1437 due to database server theft, unscrupulous administrators, and
1438 insecure networks. Encryption might also be required to secure
1439 sensitive data such as medical records or financial transactions.
1445 <term>Password Storage Encryption</term>
1449 By default, database user passwords are stored as MD5 hashes, so
1450 the administrator cannot determine the actual password assigned
1451 to the user. If MD5 encryption is used for client authentication,
1452 the unencrypted password is never even temporarily present on the
1453 server because the client MD5 encrypts it before being sent
1460 <term>Encryption For Specific Columns</term>
1464 The <filename>contrib</> function library
1465 <function>pgcrypto</function> allows certain fields to be stored
1466 encrypted. This is useful if only some of the data is sensitive.
1467 The client supplies the decryption key and the data is decrypted
1468 on the server and then sent to the client.
1472 The decrypted data and the decryption key are present on the
1473 server for a brief time while it is being decrypted and
1474 communicated between the client and server. This presents a brief
1475 moment where the data and keys can be intercepted by someone with
1476 complete access to the database server, such as the system
1483 <term>Data Partition Encryption</term>
1487 On Linux, encryption can be layered on top of a file system mount
1488 using a <quote>loopback device</quote>. This allows an entire
1489 file system partition be encrypted on disk, and decrypted by the
1490 operating system. On FreeBSD, the equivalent facility is called
1491 GEOM Based Disk Encryption, or <acronym>gbde</acronym>.
1495 This mechanism prevents unencrypted data from being read from the
1496 drives if the drives or the entire computer is stolen. This does
1497 not protect against attacks while the file system is mounted,
1498 because when mounted, the operating system provides an unencrypted
1499 view of the data. However, to mount the file system, you need some
1500 way for the encryption key to be passed to the operating system,
1501 and sometimes the key is stored somewhere on the host that mounts
1508 <term>Encrypting Passwords Across A Network</term>
1512 The <literal>MD5</> authentication method double-encrypts the
1513 password on the client before sending it to the server. It first
1514 MD5 encrypts it based on the user name, and then encrypts it
1515 based on a random salt sent by the server when the database
1516 connection was made. It is this double-encrypted value that is
1517 sent over the network to the server. Double-encryption not only
1518 prevents the password from being discovered, it also prevents
1519 another connection from using the same encrypted password to
1520 connect to the database server at a later time.
1526 <term>Encrypting Data Across A Network</term>
1530 SSL connections encrypt all data sent across the network: the
1531 password, the queries, and the data returned. The
1532 <filename>pg_hba.conf</> file allows administrators to specify
1533 which hosts can use non-encrypted connections (<literal>host</>)
1534 and which require SSL-encrypted connections
1535 (<literal>hostssl</>). Also, clients can specify that they
1536 connect to servers only via SSL. <application>Stunnel</> or
1537 <application>SSH</> can also be used to encrypt transmissions.
1543 <term>SSL Host Authentication</term>
1547 It is possible for both the client and server to provide SSL keys
1548 or certificates to each other. It takes some extra configuration
1549 on each side, but this provides stronger verification of identity
1550 than the mere use of passwords. It prevents a computer from
1551 pretending to be the server just long enough to read the password
1552 send by the client. It also helps prevent "man in the middle"
1553 attacks where a computer between the client and server pretends to
1554 be the server and reads and passes all data between the client and
1561 <term>Client-Side Encryption</term>
1565 If the system administrator cannot be trusted, it is necessary
1566 for the client to encrypt the data; this way, unencrypted data
1567 never appears on the database server. Data is encrypted on the
1568 client before being sent to the server, and database results have
1569 to be decrypted on the client before being used.
1578 <sect1 id="ssl-tcp">
1579 <title>Secure TCP/IP Connections with SSL</title>
1581 <indexterm zone="ssl-tcp">
1582 <primary>SSL</primary>
1586 <productname>PostgreSQL</> has native support for using
1587 <acronym>SSL</> connections to encrypt client/server communications
1588 for increased security. This requires that
1589 <productname>OpenSSL</productname> is installed on both client and
1590 server systems and that support in <productname>PostgreSQL</> is
1591 enabled at build time (see <xref linkend="installation">).
1595 With <acronym>SSL</> support compiled in, the
1596 <productname>PostgreSQL</> server can be started with
1597 <acronym>SSL</> enabled by setting the parameter
1598 <xref linkend="guc-ssl"> to <literal>on</> in
1599 <filename>postgresql.conf</>. The server will listen for both standard
1600 and <acronym>SSL</> connections on the same TCP port, and will negotiate
1601 with any connecting client on whether to use <acronym>SSL</>. By
1602 default, this is at the client's option; see <xref
1603 linkend="auth-pg-hba-conf"> about how to set up the server to require
1604 use of <acronym>SSL</> for some or all connections.
1608 <productname>PostgreSQL</productname> reads the system-wide
1609 <productname>OpenSSL</productname> configuration file. By default, this
1610 file is named <filename>openssl.cnf</filename> and is located in the
1611 directory reported by <literal>openssl version -d</>.
1612 This default can be overridden by setting environment variable
1613 <envar>OPENSSL_CONF</envar> to the name of the desired configuration file.
1617 <productname>OpenSSL</productname> supports a wide range of ciphers
1618 and authentication algorithms, of varying strength. While a list of
1619 ciphers can be specified in the <productname>OpenSSL</productname>
1620 configuration file, you can specify ciphers specifically for use by
1621 the database server by modifying <xref linkend="guc-ssl-ciphers"> in
1622 <filename>postgresql.conf</>.
1627 It is possible to have authentication without encryption overhead by
1628 using <literal>NULL-SHA</> or <literal>NULL-MD5</> ciphers. However,
1629 a man-in-the-middle could read and pass communications between client
1630 and server. Also, encryption overhead is minimal compared to the
1631 overhead of authentication. For these reasons NULL ciphers are not
1637 To start in <acronym>SSL</> mode, the files <filename>server.crt</>
1638 and <filename>server.key</> must exist in the server's data directory.
1639 These files should contain the server certificate and private key,
1641 On Unix systems, the permissions on <filename>server.key</filename> must
1642 disallow any access to world or group; achieve this by the command
1643 <command>chmod 0600 server.key</command>.
1644 If the private key is protected with a passphrase, the
1645 server will prompt for the passphrase and will not start until it has
1650 To require the client to supply a trusted certificate, place
1651 certificates of the certificate authorities (<acronym>CA</acronym>)
1652 you trust in the file <filename>root.crt</filename> in the data
1653 directory. A certificate will then be requested from the client during
1654 SSL connection startup. (See <xref linkend="libpq-ssl"> for a
1655 description of how to set up client certificates.) The server will
1656 verify that the client's certificate is signed by one of the trusted
1657 certificate authorities. Certificate Revocation List (CRL) entries
1658 are also checked if the file <filename>root.crl</filename> exists.
1659 <!-- If this URL changes replace it with a URL to www.archive.org. -->
1661 url="http://h71000.www7.hp.com/DOC/83final/BA554_90007/ch04s02.html"></>
1662 for diagrams showing SSL certificate usage.)
1666 If the <filename>root.crt</filename> file is not present, client
1667 certificates will not be requested or checked. In this mode, SSL
1668 provides encrypted communication but not authentication.
1672 The files <filename>server.key</>, <filename>server.crt</>,
1673 <filename>root.crt</filename>, and <filename>root.crl</filename>
1674 are only examined during server start; so you must restart
1675 the server for changes in them to take effect.
1678 <table id="ssl-file-usage">
1679 <title>SSL Server File Usage</title>
1684 <entry>Contents</entry>
1685 <entry>Effect</entry>
1692 <entry><filename>server.crt</></entry>
1693 <entry>server certificate</entry>
1694 <entry>requested by client</entry>
1698 <entry><filename>server.key</></entry>
1699 <entry>server private key</entry>
1700 <entry>proves server certificate sent by owner; does not indicate
1701 certificate owner is trustworthy</entry>
1705 <entry><filename>root.crt</></entry>
1706 <entry>trusted certificate authorities</entry>
1707 <entry>requests client certificate; checks certificate is
1708 signed by a trusted certificate authority</entry>
1712 <entry><filename>root.crl</></entry>
1713 <entry>certificates revoked by certificate authorities</entry>
1714 <entry>client certificate must not be on this list</entry>
1721 <sect2 id="ssl-certificate-creation">
1722 <title>Creating a Self-Signed Certificate</title>
1725 To create a quick self-signed certificate for the server, use the
1726 following <productname>OpenSSL</productname> command:
1728 openssl req -new -text -out server.req
1730 Fill out the information that <application>openssl</> asks for. Make sure
1731 you enter the local host name as <quote>Common Name</>; the challenge
1732 password can be left blank. The program will generate a key that is
1733 passphrase protected; it will not accept a passphrase that is less
1734 than four characters long. To remove the passphrase (as you must if
1735 you want automatic start-up of the server), run the commands:
1737 openssl rsa -in privkey.pem -out server.key
1740 Enter the old passphrase to unlock the existing key. Now do:
1742 openssl req -x509 -in server.req -text -key server.key -out server.crt
1744 to turn the certificate into a self-signed certificate and to copy
1745 the key and certificate to where the server will look for them.
1748 chmod og-rwx server.key
1750 because the server will reject the file if its permissions are more
1752 For more details on how to create your server private key and
1753 certificate, refer to the <productname>OpenSSL</> documentation.
1757 A self-signed certificate can be used for testing, but a certificate
1758 signed by a certificate authority (<acronym>CA</>) (either one of the
1759 global <acronym>CAs</> or a local one) should be used in production
1760 so the client can verify the server's identity.
1767 <sect1 id="ssh-tunnels">
1768 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
1770 <indexterm zone="ssh-tunnels">
1771 <primary>ssh</primary>
1775 One can use <application>SSH</application> to encrypt the network
1776 connection between clients and a
1777 <productname>PostgreSQL</productname> server. Done properly, this
1778 provides an adequately secure network connection, even for non-SSL-capable
1783 First make sure that an <application>SSH</application> server is
1784 running properly on the same machine as the
1785 <productname>PostgreSQL</productname> server and that you can log in using
1786 <command>ssh</command> as some user. Then you can establish a secure
1787 tunnel with a command like this from the client machine:
1789 ssh -L 63333:localhost:5432 joe@foo.com
1791 The first number in the <option>-L</option> argument, 63333, is the
1792 port number of your end of the tunnel; it can be chosen freely.
1793 (IANA reserves ports 49152 through 65535 for private use.) The
1794 second number, 5432, is the remote end of the tunnel: the port
1795 number your server is using. The name or IP address between the
1796 port numbers is the host with the database server you are going to
1797 connect to, as seen from the host you are logging in to, which
1798 is <literal>foo.com</literal> in this example. In order to connect
1799 to the database server using this tunnel, you connect to port 63333
1800 on the local machine:
1802 psql -h localhost -p 63333 postgres
1804 To the database server it will then look as though you are really
1805 user <literal>joe</literal> on host <literal>foo.com</literal>
1806 connecting to <literal>localhost</literal> in that context, and it
1807 will use whatever authentication procedure was configured for
1808 connections from this user and host. Note that the server will not
1809 think the connection is SSL-encrypted, since in fact it is not
1810 encrypted between the
1811 <application>SSH</application> server and the
1812 <productname>PostgreSQL</productname> server. This should not pose any
1813 extra security risk as long as they are on the same machine.
1818 tunnel setup to succeed you must be allowed to connect via
1819 <command>ssh</command> as <literal>joe@foo.com</literal>, just
1820 as if you had attempted to use <command>ssh</command> to set up a
1825 You could also have set up the port forwarding as
1827 ssh -L 63333:foo.com:5432 joe@foo.com
1829 but then the database server will see the connection as coming in
1830 on its <literal>foo.com</literal> interface, which is not opened by
1831 the default setting <literal>listen_addresses =
1832 'localhost'</literal>. This is usually not what you want.
1836 If you have to <quote>hop</quote> to the database server via some
1837 login host, one possible setup could look like this:
1839 ssh -L 63333:db.foo.com:5432 joe@shell.foo.com
1841 Note that this way the connection
1842 from <literal>shell.foo.com</literal>
1843 to <literal>db.foo.com</literal> will not be encrypted by the SSH
1845 SSH offers quite a few configuration possibilities when the network
1846 is restricted in various ways. Please refer to the SSH
1847 documentation for details.
1852 Several other applications exist that can provide secure tunnels using
1853 a procedure similar in concept to the one just described.