1 <!-- $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.387 2007/12/16 10:17:13 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 OOM killer from
1260 invoking altogether, it will lower the chances significantly and
1261 will therefore lead to more robust system behavior. This is done
1262 by selecting strict overcommit mode via <command>sysctl</command>:
1264 sysctl -w vm.overcommit_memory=2
1266 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
1267 You might also wish to modify the related setting
1268 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
1269 file <filename>Documentation/vm/overcommit-accounting</>.
1273 Some vendors' Linux 2.4 kernels are reported to have early versions
1274 of the 2.6 overcommit <command>sysctl</command> parameter. However, setting
1275 <literal>vm.overcommit_memory</> to 2
1276 on a kernel that does not have the relevant code will make
1277 things worse not better. It is recommended that you inspect
1278 the actual kernel source code (see the function
1279 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
1280 to verify what is supported in your copy before you try this in a 2.4
1281 installation. The presence of the <filename>overcommit-accounting</>
1282 documentation file should <emphasis>not</> be taken as evidence that the
1283 feature is there. If in any doubt, consult a kernel expert or your
1290 <sect1 id="server-shutdown">
1291 <title>Shutting Down the Server</title>
1293 <indexterm zone="server-shutdown">
1294 <primary>shutdown</>
1298 There are several ways to shut down the database server. You control
1299 the type of shutdown by sending different signals to the master
1300 <command>postgres</command> process.
1304 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
1307 After receiving <systemitem>SIGTERM</systemitem>, the server
1308 disallows new connections, but lets existing sessions end their
1309 work normally. It shuts down only after all of the sessions
1310 terminate normally. This is the <firstterm>Smart
1311 Shutdown</firstterm>.
1317 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
1320 The server disallows new connections and sends all existing
1321 server processes <systemitem>SIGTERM</systemitem>, which will cause them
1322 to abort their current transactions and exit promptly. It then
1323 waits for the server processes to exit and finally shuts down. This is the
1324 <firstterm>Fast Shutdown</firstterm>.
1330 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
1333 This is the <firstterm>Immediate Shutdown</firstterm>, which
1334 will cause the master <command>postgres</command> process to send a
1335 <systemitem>SIGQUIT</systemitem> to all child processes and exit
1336 immediately, without properly shutting itself down. The child processes
1337 likewise exit immediately upon receiving
1338 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
1339 replaying the WAL log) upon next start-up. This is recommended
1340 only in emergencies.
1348 The <xref linkend="app-pg-ctl"> program provides a convenient
1349 interface for sending these signals to shut down the server.
1353 Alternatively, you can send the signal directly using <command>kill</>
1354 (or <command>pg_ctl kill TERM [process id]</> on <productname>Windows</>).
1355 The <acronym>PID</> of the <command>postgres</command> process can be
1356 found using the <command>ps</command> program, or from the file
1357 <filename>postmaster.pid</filename> in the data directory. For
1358 example, to do a fast shutdown:
1360 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
1366 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
1367 the server. Doing so will prevent the server from releasing
1368 shared memory and semaphores, which might then have to be done
1369 manually before a new server can be started. Furthermore,
1370 <systemitem>SIGKILL</systemitem> kills the <command>postgres</command>
1371 process without letting it relay the signal to its subprocesses,
1372 so it will be necessary to kill the individual subprocesses by hand as
1378 <sect1 id="encryption-options">
1379 <title>Encryption Options</title>
1381 <indexterm zone="encryption-options">
1382 <primary>encryption</primary>
1386 <productname>PostgreSQL</productname> offers encryption at several
1387 levels, and provides flexibility in protecting data from disclosure
1388 due to database server theft, unscrupulous administrators, and
1389 insecure networks. Encryption might also be required to secure
1390 sensitive data such as medical records or financial transactions.
1396 <term>Password Storage Encryption</term>
1400 By default, database user passwords are stored as MD5 hashes, so
1401 the administrator cannot determine the actual password assigned
1402 to the user. If MD5 encryption is used for client authentication,
1403 the unencrypted password is never even temporarily present on the
1404 server because the client MD5 encrypts it before being sent
1411 <term>Encryption For Specific Columns</term>
1415 The <filename>/contrib</> function library
1416 <function>pgcrypto</function> allows certain fields to be stored
1417 encrypted. This is useful if only some of the data is sensitive.
1418 The client supplies the decryption key and the data is decrypted
1419 on the server and then sent to the client.
1423 The decrypted data and the decryption key are present on the
1424 server for a brief time while it is being decrypted and
1425 communicated between the client and server. This presents a brief
1426 moment where the data and keys can be intercepted by someone with
1427 complete access to the database server, such as the system
1434 <term>Data Partition Encryption</term>
1438 On Linux, encryption can be layered on top of a file system mount
1439 using a <quote>loopback device</quote>. This allows an entire
1440 file system partition be encrypted on disk, and decrypted by the
1441 operating system. On FreeBSD, the equivalent facility is called
1442 GEOM Based Disk Encryption, or <acronym>gbde</acronym>.
1446 This mechanism prevents unencrypted data from being read from the
1447 drives if the drives or the entire computer is stolen. This does
1448 not protect against attacks while the file system is mounted,
1449 because when mounted, the operating system provides an unencrypted
1450 view of the data. However, to mount the file system, you need some
1451 way for the encryption key to be passed to the operating system,
1452 and sometimes the key is stored somewhere on the host that mounts
1459 <term>Encrypting Passwords Across A Network</term>
1463 The <literal>MD5</> authentication method double-encrypts the
1464 password on the client before sending it to the server. It first
1465 MD5 encrypts it based on the user name, and then encrypts it
1466 based on a random salt sent by the server when the database
1467 connection was made. It is this double-encrypted value that is
1468 sent over the network to the server. Double-encryption not only
1469 prevents the password from being discovered, it also prevents
1470 another connection from using the same encrypted password to
1471 connect to the database server at a later time.
1477 <term>Encrypting Data Across A Network</term>
1481 SSL connections encrypt all data sent across the network: the
1482 password, the queries, and the data returned. The
1483 <filename>pg_hba.conf</> file allows administrators to specify
1484 which hosts can use non-encrypted connections (<literal>host</>)
1485 and which require SSL-encrypted connections
1486 (<literal>hostssl</>). Also, clients can specify that they
1487 connect to servers only via SSL. <application>Stunnel</> or
1488 <application>SSH</> can also be used to encrypt transmissions.
1494 <term>SSL Host Authentication</term>
1498 It is possible for both the client and server to provide SSL keys
1499 or certificates to each other. It takes some extra configuration
1500 on each side, but this provides stronger verification of identity
1501 than the mere use of passwords. It prevents a computer from
1502 pretending to be the server just long enough to read the password
1503 send by the client. It also helps prevent "man in the middle"
1504 attacks where a computer between the client and server pretends to
1505 be the server and reads and passes all data between the client and
1512 <term>Client-Side Encryption</term>
1516 If the system administrator cannot be trusted, it is necessary
1517 for the client to encrypt the data; this way, unencrypted data
1518 never appears on the database server. Data is encrypted on the
1519 client before being sent to the server, and database results have
1520 to be decrypted on the client before being used.
1529 <sect1 id="ssl-tcp">
1530 <title>Secure TCP/IP Connections with SSL</title>
1532 <indexterm zone="ssl-tcp">
1533 <primary>SSL</primary>
1537 <productname>PostgreSQL</> has native support for using
1538 <acronym>SSL</> connections to encrypt client/server communications
1539 for increased security. This requires that
1540 <productname>OpenSSL</productname> is installed on both client and
1541 server systems and that support in <productname>PostgreSQL</> is
1542 enabled at build time (see <xref linkend="installation">).
1546 With <acronym>SSL</> support compiled in, the
1547 <productname>PostgreSQL</> server can be started with
1548 <acronym>SSL</> enabled by setting the parameter
1549 <xref linkend="guc-ssl"> to <literal>on</> in
1550 <filename>postgresql.conf</>. When
1551 starting in <acronym>SSL</> mode, the server will look for the
1552 files <filename>server.key</> and <filename>server.crt</> in the
1553 data directory, which must contain the server private key
1554 and certificate, respectively. These files must be set up correctly
1555 before an <acronym>SSL</>-enabled server can start. If the private key is
1556 protected with a passphrase, the server will prompt for the
1557 passphrase and will not start until it has been entered.
1561 The server will listen for both standard and <acronym>SSL</>
1562 connections on the same TCP port, and will negotiate with any
1563 connecting client on whether to use <acronym>SSL</>. By default,
1564 this is at the client's option; see <xref
1565 linkend="auth-pg-hba-conf"> about how to set up the server to
1566 require use of <acronym>SSL</> for some or all connections.
1570 <productname>OpenSSL</productname> supports a wide range of ciphers
1571 and authentication algorithms, whose strength varies significantly.
1572 You can restrict the list of ciphers that can be used to connect to
1573 your server by adjusting the <xref linkend="guc-ssl-ciphers"> parameter.
1577 <productname>PostgreSQL</productname> reads the system-wide
1578 <productname>OpenSSL</productname> configuration file. By default, this
1579 file is named <filename>openssl.cnf</filename> and is located in the
1580 directory reported by <literal>openssl version -d</>.
1581 This default can be overridden by setting environment variable
1582 <envar>OPENSSL_CONF</envar> to the name of the desired configuration file.
1586 For details on how to create your server private key and certificate,
1587 refer to the <productname>OpenSSL</> documentation. A
1588 self-signed certificate can be used for testing, but a
1589 certificate signed by a certificate authority (<acronym>CA</>)
1590 (either one of the global
1591 <acronym>CAs</> or a local one) should be used in production so the
1592 client can verify the server's identity. To create a quick
1593 self-signed certificate, use the following
1594 <productname>OpenSSL</productname> command:
1596 openssl req -new -text -out server.req
1598 Fill out the information that <application>openssl</> asks for. Make sure
1599 you enter the local host name as <quote>Common Name</>; the challenge
1600 password can be left blank. The program will generate a key that is
1601 passphrase protected; it will not accept a passphrase that is less
1602 than four characters long. To remove the passphrase (as you must if
1603 you want automatic start-up of the server), run the commands:
1605 openssl rsa -in privkey.pem -out server.key
1608 Enter the old passphrase to unlock the existing key. Now do:
1610 openssl req -x509 -in server.req -text -key server.key -out server.crt
1611 chmod og-rwx server.key
1613 to turn the certificate into a self-signed certificate and to copy the
1614 key and certificate to where the server will look for them.
1618 If verification of client certificates is required, place the
1619 certificates of the <acronym>CA</acronym>(s) you wish to check for in
1620 the file <filename>root.crt</filename> in the data directory. When
1621 present, a client certificate will be requested from the client
1622 during SSL connection startup, and it must have been signed by one of
1623 the certificates present in <filename>root.crt</filename>. (See <xref
1624 linkend="libpq-ssl"> for a description of how to set up client
1625 certificates.) Certificate Revocation List (CRL) entries are also
1626 checked if the file <filename>root.crl</filename> exists.
1630 When the <filename>root.crt</filename> file is not present, client
1631 certificates will not be requested or checked. In this mode, SSL
1632 provides communication security but not authentication.
1636 The files <filename>server.key</>, <filename>server.crt</>,
1637 <filename>root.crt</filename>, and <filename>root.crl</filename>
1638 are only examined during server start; so you must restart
1639 the server to make changes in them take effect.
1643 <sect1 id="ssh-tunnels">
1644 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
1646 <indexterm zone="ssh-tunnels">
1647 <primary>ssh</primary>
1651 One can use <application>SSH</application> to encrypt the network
1652 connection between clients and a
1653 <productname>PostgreSQL</productname> server. Done properly, this
1654 provides an adequately secure network connection, even for non-SSL-capable
1659 First make sure that an <application>SSH</application> server is
1660 running properly on the same machine as the
1661 <productname>PostgreSQL</productname> server and that you can log in using
1662 <command>ssh</command> as some user. Then you can establish a secure
1663 tunnel with a command like this from the client machine:
1665 ssh -L 3333:foo.com:5432 joe@foo.com
1667 The first number in the <option>-L</option> argument, 3333, is the
1668 port number of your end of the tunnel; it can be chosen freely. The
1669 second number, 5432, is the remote end of the tunnel: the port
1670 number your server is using. The name or IP address between
1671 the port numbers is the host with the database server you are going
1672 to connect to. In order to connect to the database server using
1673 this tunnel, you connect to port 3333 on the local machine:
1675 psql -h localhost -p 3333 postgres
1677 To the database server it will then look as though you are really
1678 user <literal>joe@foo.com</literal> and it will use whatever
1679 authentication procedure was configured for connections from this
1680 user and host. Note that the server will not think the connection is
1681 SSL-encrypted, since in fact it is not encrypted between the
1682 <application>SSH</application> server and the
1683 <productname>PostgreSQL</productname> server. This should not pose any
1684 extra security risk as long as they are on the same machine.
1688 tunnel setup to succeed you must be allowed to connect via
1689 <command>ssh</command> as <literal>joe@foo.com</literal>, just
1690 as if you had attempted to use <command>ssh</command> to set up a
1696 Several other applications exist that can provide secure tunnels using
1697 a procedure similar in concept to the one just described.