From cdd402f8b3adc9992337a67da866031b35776282 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 26 Dec 2004 23:06:56 +0000 Subject: [PATCH] More minor updates and copy-editing. --- doc/src/sgml/client-auth.sgml | 208 +++++++++---------- doc/src/sgml/install-win32.sgml | 23 +-- doc/src/sgml/installation.sgml | 51 +++-- doc/src/sgml/runtime.sgml | 353 ++++++++++++++++++-------------- 4 files changed, 339 insertions(+), 296 deletions(-) diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index c88370b65f..126d3a810d 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1,5 +1,5 @@ @@ -53,12 +53,15 @@ $PostgreSQL: pgsql/doc/src/sgml/client-auth.sgml,v 1.68 2004/11/15 06:32:13 neil - Client authentication is controlled by the file - pg_hba.conf in the data directory, e.g., - /usr/local/pgsql/data/pg_hba.conf. + Client authentication is controlled by a configuration file, + which traditionally is named + pg_hba.conf and is stored in the database + cluster's data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data - directory is initialized by initdb. + directory is initialized by initdb. It is + possible to place the authentication configuration file elsewhere, + however; see the configuration parameter. @@ -151,8 +154,8 @@ hostnossl database user hostnossl - This record is similar to hostssl but with the - opposite logic: it only matches connection attempts made over + This record type has the opposite logic to hostssl: + it only matches connection attempts made over TCP/IP that do not use SSL. @@ -167,7 +170,7 @@ hostnossl database user The value sameuser specifies that the record matches if the requested database has the same name as the requested user. The value samegroup specifies that - the requested user must a member of the group with the same + the requested user must be a member of the group with the same name as the requested database. Otherwise, this is the name of a specific PostgreSQL database. Multiple database names can be supplied by separating them with @@ -199,20 +202,23 @@ hostnossl database user CIDR-address - Specifies the client machine IP addresses that this record + Specifies the client machine IP address range that this record matches. It contains an IP address in standard dotted decimal notation and a CIDR mask length. (IP addresses can only be - specified numerically, not as domain or host names.) For example, - an IPv4 CIDR mask of 8 is equivalent to an IP mask of 255.0.0.0, - an IPv6 CIDR mask of 64 is equivalent to an IP mask of - ffff:ffff:ffff:ffff::. A IPv4 CIDR mask of 32 is used for single - hosts. + specified numerically, not as domain or host names.) The mask + length indicates the number of high-order bits of the client + IP address that must match. Bits to the right of this must + be zero in the given IP address. + There must not be any white space between the IP address, the + /, and the CIDR mask length. - A typical CIDR address is 172.20.143.89/32. - There should be no white space between the IP address, the - /, and the CIDR mask length. + A typical CIDR-address is + 172.20.143.89/32 for a single host, or + 172.20.143.0/24 for a network. + To specify a single host, use a CIDR mask of 32 for IPv4 or + 128 for IPv6. @@ -226,7 +232,7 @@ hostnossl database user - These fields only apply to host, + This field only applies to host, hostssl, and hostnossl records. @@ -234,20 +240,19 @@ hostnossl database user IP-address - IP-masklen + IP-mask - This may be used as an alternative to the + These fields may be used as an alternative to the CIDR-address notation. Instead of specifying the mask length, the actual mask is specified in a - separate column. For example, 255.0.0.0 represents a IPv4 CIDR - mask length of 8, and 255.255.255.255 represents a CIDR mask - length of 32. The same matching logic is used as for a dotted - notation IP-mask. + separate column. For example, 255.0.0.0 represents an IPv4 + CIDR mask length of 8, and 255.255.255.255 represents a + CIDR mask length of 32. - This field only applies to host, + These fields only apply to host, hostssl, and hostnossl records. @@ -266,7 +271,7 @@ hostnossl database user trust - The connection is allowed unconditionally. This method + Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they like, @@ -280,7 +285,7 @@ hostnossl database user reject - The connection is rejected unconditionally. This is useful for + Reject the connection unconditionally. This is useful for filtering out certain hosts from a group. @@ -290,9 +295,8 @@ hostnossl database user md5 - Requires the client to supply an MD5 encrypted password for - authentication. This is the only method that allows encrypted - passwords to be stored in pg_shadow. + Require the client to supply an MD5-encrypted password for + authentication. See for details. @@ -302,9 +306,10 @@ hostnossl database user crypt - Like the md5 method but uses older crypt() - encryption, which is needed for pre-7.2 clients. - md5 is preferred for 7.2 and later clients. + Require the client to supply a crypt()-encrypted + password for authentication. + md5 is preferred for 7.2 and later clients, + but pre-7.2 clients only support crypt. See for details. @@ -314,8 +319,10 @@ hostnossl database user password - Same as md5, but the password is sent in clear text over the - network. This should not be used on untrusted networks. + Require the client to supply an unencrypted password for + authentication. + Since the password is sent in clear text over the + network, this should not be used on untrusted networks. See for details. @@ -325,7 +332,7 @@ hostnossl database user krb4 - Kerberos V4 is used to authenticate the user. This is only + Use Kerberos V4 to authenticate the user. This is only available for TCP/IP connections. See for details. @@ -336,7 +343,7 @@ hostnossl database user krb5 - Kerberos V5 is used to authenticate the user. This is only + Use Kerberos V5 to authenticate the user. This is only available for TCP/IP connections. See for details. @@ -353,30 +360,7 @@ hostnossl database user operating system) and check if the user is allowed to connect as the requested database user by consulting the map specified after the ident key word. - - - - If you use the map sameuser, the user - names are required to be identical. If not, the map name is - looked up in the file pg_ident.conf - in the same directory as pg_hba.conf. - The connection is accepted if that file contains an - entry for this map name with the operating-system user name - and the requested PostgreSQL user - name. - - - - For local connections, this only works on machines that - support Unix-domain socket credentials (currently - Linux, FreeBSD, NetBSD, - OpenBSD, and - BSD/OS). - - - - See below for details. + See for details. @@ -402,7 +386,7 @@ hostnossl database user The meaning of this optional field depends on the chosen - authentication method and is described in the next section. + authentication method. Details appear below. @@ -423,13 +407,6 @@ hostnossl database user range of allowed client IP addresses. - - - Do not prevent the superuser from accessing the template1 - database. Various utility commands need access to template1. - - - The pg_hba.conf file is read on start-up and when the main server process (postmaster) receives a @@ -441,13 +418,13 @@ hostnossl database user - An example of a pg_hba.conf file is shown in + Some examples of pg_hba.conf entries are shown in . See the next section for details on the different authentication methods. - An example <filename>pg_hba.conf</filename> file + Example <filename>pg_hba.conf</filename> entries # Allow any user on the local system to connect to any database under # any user name using Unix-domain sockets (the default for local @@ -463,7 +440,7 @@ host all all 127.0.0.1/32 trust # The same as the last line but using a separate netmask column # -# TYPE DATABASE USER CIDR-ADDRESS METHOD +# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host all all 127.0.0.1 255.255.255.255 trust # Allow any user from any host with IP address 192.168.93.x to connect @@ -473,11 +450,6 @@ host all all 127.0.0.1 255.255.255.255 trust # TYPE DATABASE USER CIDR-ADDRESS METHOD host template1 all 192.168.93.0/24 ident sameuser -# The same as the last line but using a separate netmask column -# -# TYPE DATABASE USER CIDR-ADDRESS METHOD -host template1 all 192.168.93.0 255.255.255.0 ident sameuser - # Allow a user from host 192.168.12.10 to connect to database # "template1" if the user's password is correctly supplied. # @@ -486,7 +458,7 @@ host template1 all 192.168.12.10/32 md5 # In the absence of preceding "host" lines, these two lines will # reject all connection from 192.168.54.1 (since that entry will be -# matched first), but allow Kerberos V connections from anywhere else +# matched first), but allow Kerberos 5 connections from anywhere else # on the Internet. The zero mask means that no bits of the host IP # address are considered so it matches any host. # @@ -527,7 +499,7 @@ local db1,db2,@demodbs all md5 Authentication methods - The following describes the authentication methods in more detail. + The following subsections describe the authentication methods in more detail. @@ -538,9 +510,10 @@ local db1,db2,@demodbs all md5 PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user they specify (including the database superuser). - Of course, restrictions placed in the user column still apply. - This method should only be used when there is adequate operating system-level - protection on connections to the server. + Of course, restrictions made in the database and + user columns still apply. + This method should only be used when there is adequate + operating-system-level protection on connections to the server. @@ -594,7 +567,13 @@ local db1,db2,@demodbs all md5 The password-based authentication methods are md5, crypt, and password. These methods operate similarly except for the way that the password is sent across the - connection. If you are at all concerned about password + connection. But only md5 supports encrypted + passwords stored in pg_shadow; + the other two require unencrypted passwords to be stored there. + + + + If you are at all concerned about password sniffing attacks then md5 is preferred, with crypt a second choice if you must support pre-7.2 clients. Plain password should especially be avoided for @@ -606,19 +585,12 @@ local db1,db2,@demodbs all md5 PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_shadow system - catalog table. Passwords can be managed with the SQL - commands CREATE USER and ALTER - USER, e.g., CREATE USER foo WITH PASSWORD - 'secret';. By default, that is, if no password has - been set up, the stored password is null and - password authentication will always fail for that user. - - - - To restrict the set of users that are allowed to connect to - certain databases, list the users in the user - column of pg_hba.conf, as explained in the - previous section. + catalog table. Passwords can be managed with the SQL commands + and + , + e.g., CREATE USER foo WITH PASSWORD 'secret';. + By default, that is, if no password has been set up, the stored password + is null and password authentication will always fail for that user. @@ -634,7 +606,7 @@ local db1,db2,@demodbs all md5 Kerberos is an industry-standard secure authentication system suitable for distributed computing over a public network. A description of the Kerberos system - is far beyond the scope of this document; in all generality it can be + is far beyond the scope of this document; in full generality it can be quite complex (yet powerful). The Kerberos FAQ or MIT @@ -680,9 +652,9 @@ local db1,db2,@demodbs all md5 Make sure that your server key file is readable (and preferably only readable) by the PostgreSQL server - account. (See also ). The location + account. (See also .) The location of the key file is specified by the run-time configuration + linkend="guc-krb-server-keyfile"> configuration parameter. (See also .) The default is /etc/srvtab if you are using Kerberos 4 and /usr/local/pgsql/etc/krb5.keytab (or whichever @@ -728,10 +700,10 @@ local db1,db2,@demodbs all md5 - The ident authentication method works by inspecting the client's + The ident authentication method works by obtaining the client's operating system user name and determining the allowed database - user names by using a map file that lists the permitted - corresponding user name pairs. The determination of the client's + user names using a map file that lists the permitted + corresponding pairs of names. The determination of the client's user name is the security-critical point, and it works differently depending on the connection type. @@ -791,14 +763,15 @@ local db1,db2,@demodbs all md5 On systems without SO_PEERCRED requests, ident authentication is only available for TCP/IP connections. As a - work around, it is possible to specify the localhost address 127.0.0.1 and make connections to this - address. + address. This method is trustworthy to the extent that you trust + the local ident server. - + Ident Maps @@ -815,21 +788,26 @@ local db1,db2,@demodbs all md5 - Ident maps - other than sameuser are defined in the file - pg_ident.confpg_ident.conf - in the data directory, which contains lines of the general form: + Ident maps other than sameuser are defined in the + ident map file, which by default is named + pg_ident.confpg_ident.conf + and is stored in the + cluster's data directory. (It is possible to place the map file + elsewhere, however; see the + configuration parameter.) + The ident map file contains lines of the general form: map-name ident-username database-username - Comments and whitespace are handled in the usual way. The + Comments and whitespace are handled in the same way as in + pg_hba.conf. The map-name is an arbitrary name that will be used to refer to this mapping in pg_hba.conf. The other two fields specify which operating system user is allowed to connect as which database user. The same map-name can be used repeatedly to specify more user-mappings within a single map. There is no restriction regarding how many database users a given - operating system user may correspond to and vice versa. + operating system user may correspond to, nor vice versa. @@ -875,7 +853,7 @@ omicron bryanh guest1 - PAM Authentication + PAM authentication PAM @@ -886,9 +864,9 @@ omicron bryanh guest1 password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The default PAM service name is postgresql. You can - optionally supply you own service name after the pam - key word in the file pg_hba.conf. For more information about PAM, please read - the pam + key word in the file pg_hba.conf. + For more information about PAM, please read the Linux-PAM Page and the @@ -11,9 +11,9 @@ $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.21 2004/12/24 19:20:18 mo - Although PostgreSQL is written for - Unix-like operating systems and can be built using - MinGW and + Although a complete PostgreSQL installation + for Windows can only be built using + MinGW or Cygwin, the C client library (libpq) and the interactive terminal (psql) can be compiled using other Windows @@ -25,10 +25,9 @@ $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.21 2004/12/24 19:20:18 mo - If you are using a Windows NT-based operating system or newer you can - build and use all of PostgreSQL the - Unix way if you install the MinGW - toolkit first. In that case see . + Using MinGW or + Cygwin is preferred. If using one of + those tool sets, see . @@ -110,7 +109,7 @@ $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.21 2004/12/24 19:20:18 mo on this machine, you will have to add the src\include and src\interfaces\libpq subdirectories of the source - tree to the include path in your compilers settings. + tree to the include path in your compiler's settings. @@ -123,10 +122,10 @@ $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.21 2004/12/24 19:20:18 mo psql is compiled as a console application. As the Windows console windows use a different encoding than the rest of the system, you must take special care - when using 8-bit characters at the psql - prompt. When psql detects a problematic + when using 8-bit characters within psql. + If psql detects a problematic console code page, it will warn you at startup. To change the - console code page, two things are neccessary: + console code page, two things are necessary: diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index 143e51d3dc..56e4277df3 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -1,4 +1,4 @@ - + <![%standalone-include[<productname>PostgreSQL</>]]> @@ -109,7 +109,10 @@ su - postgres <filename>configure</>. (On <productname>NetBSD</productname>, the <filename>libedit</filename> library is <productname>Readline</productname>-compatible and is used if - <filename>libreadline</filename> is not found.) + <filename>libreadline</filename> is not found.) If you are using + a package-based Linux distribution, be aware that you need both + the <literal>readline</> and <literal>readline-devel</> packages, + if those are separate in your distribution. </para> </listitem> @@ -120,14 +123,22 @@ su - postgres <secondary>on Windows</secondary> </indexterm> - To build on <productname>NT</>-based versions of - <productname>Windows</> like Windows XP and 2003 see - <filename>doc/FAQ_MINGW</>. For earlier <productname>Windows</> - releases see <filename>doc/FAQ_CYGWIN</>. - - To build <productname>Windows</> client-only interfaces using - tools like <productname>Visual C++</> and <productname>Borland - C++</> see + Additional software is needed to build + <productname>PostgreSQL</productname> on <productname>Windows</>. + You can build <productname>PostgreSQL</productname> for + <productname>NT</>-based versions of <productname>Windows</> + (like Windows XP and 2003) using <productname>MinGW</productname>; + see <filename>doc/FAQ_MINGW</> for details. You can also build + <productname>PostgreSQL</productname> using + <productname>Cygwin</productname>; see <filename>doc/FAQ_CYGWIN</>. + A <productname>Cygwin</productname>-based build will work on older + versions of <productname>Windows</>, but if you have a choice, + we recommend the <productname>MinGW</productname> approach. + While these are the only tool sets recommended for a complete build, + it is possible to build just the C client library + (<application>libpq</application>) and the interactive terminal + (<application>psql</application>) using other <productname>Windows</> + tool sets. For details of that see <![%standalone-include[the documentation chapter "Client-Only Installation on Windows"]]> <![%standalone-ignore[<xref linkend="install-win32">]]>. @@ -245,7 +256,7 @@ su - postgres <listitem> <para> - <application>Kerberos</>, <productname>OpenSSL</>, or + <application>Kerberos</>, <productname>OpenSSL</>, and/or <application>PAM</>, if you want to support authentication or encryption using these services. </para> @@ -271,7 +282,7 @@ su - postgres <primary>yacc</primary> </indexterm> - <application>Flex</> and <application>Bison</> + GNU <application>Flex</> and <application>Bison</> are needed to build a CVS checkout or if you changed the actual scanner and parser definition files. If you need them, be sure to get <application>Flex</> 2.5.4 or later and @@ -299,7 +310,7 @@ su - postgres 25 MB, databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra - 90 MB. Use the <command>df</command> command to check for disk + 90 MB. Use the <command>df</command> command to check free disk space. </para> </sect1> @@ -416,9 +427,11 @@ su - postgres <para> Very old versions might not have <application>pg_ctl</>. If you can't find it or it doesn't work, find out the process ID of the - old server, for - example by typing <userinput>ps ax | grep postmaster</>, and - signal it to stop this way: + old server, for example by typing +<screen> +<userinput>ps ax | grep postmaster</userinput> +</screen> + and signal it to stop this way: <screen> <userinput>kill -INT <replaceable>processID</></userinput> </screen> @@ -1108,8 +1121,10 @@ All of PostgreSQL is successfully made. Ready to install. program, so that you can rebuild everything with <command>gmake</> later on. To reset the source tree to the state in which it was distributed, use <command>gmake distclean</>. If you are going to - build for several platforms from the same source tree you must do - this and re-configure for each build. + build for several platforms within the same source tree you must do + this and re-configure for each build. (Alternatively, use + a separate build tree for each platform, so that the source tree + remains unmodified.) </para> </formalpara> diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 7e938cd61e..fad715d005 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.299 2004/12/26 23:06:56 tgl Exp $ --> <Chapter Id="runtime"> @@ -7,7 +7,7 @@ $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp <Para> This chapter discusses how to set up and run the database server - and the interactions with the operating system. + and its interactions with the operating system. </para> <sect1 id="postgres-user"> @@ -18,7 +18,7 @@ $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp </indexterm> <para> - As with any other server daemon that is connected to outside world, + As with any other server daemon that is accessible to the outside world, it is advisable to run <productname>PostgreSQL</productname> under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other @@ -31,8 +31,8 @@ $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp <para> To add a Unix user account to your system, look for a command <command>useradd</command> or <command>adduser</command>. The user - name <systemitem>postgres</systemitem> is often used but is by no - means required. + name <systemitem>postgres</systemitem> is often used, and is assumed + throughout this book, but you can use another name if you like. </para> </sect1> @@ -51,14 +51,14 @@ $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp <para> Before you can do anything, you must initialize a database storage area on disk. We call this a <firstterm>database cluster</firstterm>. - (<acronym>SQL</acronym> uses the term catalog cluster instead.) A - database cluster is a collection of databases is accessible by a + (<acronym>SQL</acronym> uses the term catalog cluster.) A + database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named <literal>template1</literal>. As the name suggests, this will be used as a template for subsequently created databases; it should not be - used for actual work. (See <xref linkend="managing-databases"> for information - about creating databases.) + used for actual work. (See <xref linkend="managing-databases"> for + information about creating new databases within a cluster.) </para> <para> @@ -71,7 +71,7 @@ $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.298 2004/12/18 18:36:33 tgl Exp <filename>/var/lib/pgsql/data</filename> are popular. To initialize a database cluster, use the command <command>initdb</command>,<indexterm><primary>initdb</></> which is installed with <productname>PostgreSQL</productname>. The desired - file system location of your database system is indicated by the + file system location of your database cluster is indicated by the <option>-D</option> option, for example <screen> <prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput> @@ -107,7 +107,7 @@ postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput> <para> <command>initdb</command> will refuse to run if the data directory - looks like it it has already been initialized.</para> + looks like it has already been initialized.</para> <para> Because the data directory contains all the data stored in the @@ -123,14 +123,15 @@ postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput> database and even become the database superuser. If you do not trust other local users, we recommend you use one of <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option> - or <option>--pwfile</option> option to assign a password to the + or <option>--pwfile</option> options to assign a password to the database superuser.<indexterm><primary>password</><secondary>of the - superuser</></indexterm> After <command>initdb</command>, modify - the <filename>pg_hba.conf</filename> file to use <literal>md5</> or - <literal>password</> instead of <literal>trust</> authentication + superuser</></indexterm> Also, specify <option>-A md5</> or + <option>-A password</> so that the default <literal>trust</> authentication + mode is not used; or modify the generated <filename>pg_hba.conf</filename> + file after running <command>initdb</command>, <emphasis>before</> you start the server for the first time. (Other - approaches include using <literal>ident</literal> authentication or - file system permissions to restrict connections. See <xref + reasonable approaches include using <literal>ident</literal> authentication + or file system permissions to restrict connections. See <xref linkend="client-authentication"> for more information.) </para> @@ -185,22 +186,23 @@ $ <userinput>postmaster -D /usr/local/pgsql/data >logfile 2>&1 &</ <para> The <command>postmaster</command> also takes a number of other - command line options. For more information, see the reference page + command line options. For more information, see the + <xref linkend="app-postmaster"> reference page and <xref linkend="runtime-config"> below. </para> <para> - This shell syntax can get tedious quickly. Therefore the shell - script wrapper - <command>pg_ctl</command><indexterm><primary>pg_ctl</primary></indexterm> + This shell syntax can get tedious quickly. Therefore the wrapper + program + <xref linkend="app-pg-ctl"><indexterm><primary>pg_ctl</primary></indexterm> is provided to simplify some tasks. For example: <programlisting> pg_ctl start -l logfile </programlisting> will start the server in the background and put the output into the named log file. The <option>-D</option> option has the same meaning - here as in the <command>postmaster</command>. <command>pg_ctl</command> is also - capable of stopping the server. + here as in the <command>postmaster</command>. <command>pg_ctl</command> + is also capable of stopping the server. </para> <para> @@ -453,19 +455,20 @@ psql: could not connect to server: No such file or directory <para> All parameter names are case-insensitive. Every parameter takes a - value of one of the four types: boolean, integer, floating point, - and string. Boolean values are <literal>ON</literal>, + value of one of four types: boolean, integer, floating point, + or string. Boolean values may be written as <literal>ON</literal>, <literal>OFF</literal>, <literal>TRUE</literal>, <literal>FALSE</literal>, <literal>YES</literal>, <literal>NO</literal>, <literal>1</literal>, <literal>0</literal> - (case-insensitive) or any non-ambiguous prefix of these. + (all case-insensitive) or any unambiguous prefix of these. </para> <para> One way to set these parameters is to edit the file - <filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></> - in the data directory. (A default file is installed there.) An - example of what this file might look like is: + <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, + which is normally kept in the data directory. (<command>initdb</> + installs a default copy there.) An example of what this file might look + like is: <programlisting> # This is a comment log_connections = yes @@ -476,7 +479,7 @@ search_path = '$user, public' value is optional. Whitespace is insignificant and blank lines are ignored. Hash marks (<literal>#</literal>) introduce comments anywhere. Parameter values that are not simple identifiers or - numbers should be single-quoted. + numbers must be single-quoted. </para> <para> @@ -502,11 +505,14 @@ search_path = '$user, public' postmaster -c log_connections=yes -c log_destination='syslog' </programlisting> Command-line options override any conflicting settings in - <filename>postgresql.conf</filename>. + <filename>postgresql.conf</filename>. Note that this means you won't + be able to change the value on-the-fly by editing + <filename>postgresql.conf</filename>, so while the command-line + method may be convenient, it can cost you flexibility later. </para> <para> - Occasionally it is also useful to give a command line option to + Occasionally it is useful to give a command line option to one particular session only. The environment variable <envar>PGOPTIONS</envar> can be used for this purpose on the client side: @@ -515,18 +521,16 @@ env PGOPTIONS='-c geqo=off' psql </programlisting> (This works for any <application>libpq</>-based client application, not just <application>psql</application>.) Note that this won't work for - parameters that are fixed when the server is started, nor for - parameters that require superuser permissions to change (not even - if you are logging in as superuser). + parameters that are fixed when the server is started or that must be + specified in <filename>postgresql.conf</filename>. </para> <para> Furthermore, it is possible to assign a set of option settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The - commands <xref linkend="sql-alterdatabase" - endterm="sql-alterdatabase-title"> and <xref - linkend="sql-alteruser" endterm="sql-alteruser-title">, + commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title"> + and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">, respectively, are used to configure these settings. Per-database settings override anything received from the <command>postmaster</command> command-line or the configuration @@ -546,8 +550,8 @@ SET ENABLE_SEQSCAN TO OFF; <command>SET</command>: for example, if they control behavior that cannot reasonably be changed without restarting <productname>PostgreSQL</productname>. Also, some parameters can - be modified via <command>SET</command> by superusers, but not by - ordinary users. + be modified via <command>SET</command> or <command>ALTER</> by superusers, + but not by ordinary users. </para> <para> @@ -567,6 +571,20 @@ SET ENABLE_SEQSCAN TO OFF; <sect2 id="runtime-config-file-locations"> <title>File Locations + + In addition to the postgresql.conf file + already mentioned, PostgreSQL uses + two other manually-edited configuration files, which control + client authentication (their use is discussed in ). + By default, all three configuration files are stored + in the database cluster's data directory. The options described + in this subsection allow the configuration files to be placed elsewhere. + (Doing so can ease administration. In particular it is often + easier to ensure that the configuration files are properly backed-up + when they are kept separate.) + + data_directory (string) @@ -602,7 +620,8 @@ SET ENABLE_SEQSCAN TO OFF; - Specifies the configuration file for host-based authentication. + Specifies the configuration file for host-based authentication + (customarily called pg_hba.conf). This option can only be set at server start. @@ -616,7 +635,8 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the configuration file for - ident authentication. + ident authentication + (customarily called pg_ident.conf). This option can only be set at server start. @@ -629,34 +649,33 @@ SET ENABLE_SEQSCAN TO OFF; - Specifies that the postmaster should create an - additional process-id (PID) file for use by server administration - programs. - This option can only be set at server start. + Specifies the name of an additional process-id (PID) file that the + postmaster should create for use by server + administration programs. + This option can only be set at server start. - In a default installation, none of the above options is set explicitly - in the postgresql.conf file. Instead, the + In a default installation, none of the above options are set explicitly. + Instead, the data directory is specified by the command-line option or the PGDATA environment variable, and the - configuration files are all placed within the data directory. + configuration files are all found within the data directory. - It is also possible to separate the configuration files from the data - directory, which can ease administration. (In particular it is often - easier to ensure that the configuration files are properly backed-up - when they are kept separate.) To do this, the + If you wish to keep the configuration files elsewhere than the + data directory, the postmaster's command-line option or PGDATA environment variable must point to the directory containing the configuration files, - and the data_directory option is set in + and the data_directory option must be set in postgresql.conf (or on the command line) to show where the data directory is actually located. Notice that - data_directory overrides for the location + data_directory overrides and + PGDATA for the location of the data directory, but not for the location of the configuration files. @@ -756,7 +775,7 @@ SET ENABLE_SEQSCAN TO OFF; - Determines the number of connection slots that + Determines the number of connection slots that are reserved for connections by PostgreSQL superusers. At most connections can ever be active simultaneously. Whenever the @@ -816,8 +835,8 @@ SET ENABLE_SEQSCAN TO OFF; - Sets the access permissions of the Unix-domain socket. Unix - domain sockets use the usual Unix file system permission set. + Sets the access permissions of the Unix-domain socket. Unix-domain + sockets use the usual Unix file system permission set. The option value is expected to be a numeric mode specification in the form accepted by the chmod and umask @@ -830,8 +849,8 @@ SET ENABLE_SEQSCAN TO OFF; anyone can connect. Reasonable alternatives are 0770 (only user and group, see also unix_socket_group) and 0700 - (only user). (Note that actually for a Unix domain socket, only write - permission matters and there is no point in setting or revoking + (only user). (Note that for a Unix-domain socket, only write + permission matters and so there is no point in setting or revoking read or execute permissions.) @@ -855,8 +874,8 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the Rendezvous broadcast name. By default, the computer name is used, specified as an - empty string ''. This option is only meaningful on platforms - that support Rendezvous. This + empty string ''. This option is ignored if the server was not + compiled with Rendezvous support. This option can only be set at server start. @@ -1163,8 +1182,8 @@ SET ENABLE_SEQSCAN TO OFF; - If mylib or mylib_init are not found, the - server will fail to start. + If a specified library or initialization function is not found, + the server will fail to start. @@ -1179,8 +1198,9 @@ SET ENABLE_SEQSCAN TO OFF; By preloading a shared library (and initializing it if applicable), the library startup time is avoided when the library is first used. However, the time to start each new - server process may increase, even if that process never - uses the library. + server process may increase slightly, even if that process never + uses the library. So this option is recommended only for + libraries that will be used in most sessions. @@ -1589,7 +1609,7 @@ SET ENABLE_SEQSCAN TO OFF; - Write a message to the server logs if checkpoints caused by + Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds. The default is 30 seconds. Zero turns off the warning. @@ -1622,8 +1642,8 @@ SET ENABLE_SEQSCAN TO OFF; file. - It is important for the command to return a zero exit status only if - it succeeds. Examples: + It is important for the command to return a zero exit status if + and only if it succeeds. Examples: archive_command = 'cp "%p" /mnt/server/archivedir/"%f"' archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows @@ -1643,20 +1663,22 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Planner Method Configuration - These configuration parameters provide a crude method for + These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution may be found by using one of these configuration parameters to force the optimizer to - choose a better plan. Other ways to improve the quality of the - plans chosen by the optimizer include configuring the , running more frequently, increasing the value of the configuration parameter, - and increasing the amount of statistics collected for a - particular column using ALTER TABLE SET + and increasing the amount of statistics collected for + specific columns using ALTER TABLE SET STATISTICS. @@ -1669,8 +1691,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Enables or disables the query planner's use of hashed - aggregation plan types. The default is on. This is used for - debugging the query planner. + aggregation plan types. The default is on. @@ -1683,8 +1704,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Enables or disables the query planner's use of hash-join plan - types. The default is on. This is used for debugging the query - planner. + types. The default is on. @@ -1700,8 +1720,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Enables or disables the query planner's use of index-scan plan - types. The default is on. This is used for debugging the query - planner. + types. The default is on. @@ -1714,8 +1733,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Enables or disables the query planner's use of merge-join plan - types. The default is on. This is used for debugging the query - planner. + types. The default is on. @@ -1731,7 +1749,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows plans. It's not possible to suppress nested-loop joins entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default is - on. This is used for debugging the query planner. + on. @@ -1750,7 +1768,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows plan types. It's not possible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available. The - default is on. This is used for debugging the query planner. + default is on. @@ -1766,7 +1784,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows steps. It's not possible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default - is on. This is used for debugging the query planner. + is on. @@ -1779,8 +1797,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Enables or disables the query planner's use of TID - scan plan types. The default is on. This is used for debugging - the query planner. + scan plan types. The default is on. @@ -2383,6 +2400,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. + Only superusers can change this setting. @@ -2505,7 +2523,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows ERROR - Reports an error that caused the current transaction to abort. + Reports an error that caused the current command to abort. @@ -2565,15 +2583,16 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows - These options enable various debugging output to be sent to - the client or server log. For each executed query, they print + These options enable various debugging output to be emitted. + For each executed query, they print the resulting parse tree, the query rewriter output, or the execution plan. debug_pretty_print indents these displays to produce a more readable but much longer output format. client_min_messages or log_min_messages must be - DEBUG1 or lower to send the output to the - client or server logs. These options are off by default. + DEBUG1 or lower to actually send this output + to the client or the server log, respectively. + These options are off by default. @@ -2585,7 +2604,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows - This outputs a line to the server logs detailing each successful + This outputs a line to the server log detailing each successful connection. This is off by default, although it is probably very useful. This option can only be set at server start or in the postgresql.conf configuration file. @@ -2600,7 +2619,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows - This outputs a line in the server logs similar to + This outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session. This is off by default. This option can only be set at server start or in the @@ -2781,11 +2800,12 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows - By default, connection logs only show the IP address of the - connecting host. If you want it to show the host name you can - turn this on, but depending on your host name resolution setup - it might impose a non-negligible performance penalty. This - option can only be set at server start. + By default, connection log messages only show the IP address of the + connecting host. Turning on this option causes logging of the + host name as well. Note that depending on your host name resolution + setup this might impose a non-negligible performance penalty. This + option can only be set at server start or in the + postgresql.conf file. @@ -3039,7 +3059,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows This parameter is normally true. When set to false, it disables - validation of the function body string in . Disabling validation is occasionally useful to avoid problems such as forward @@ -3102,7 +3122,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows - Aborts any statement that takes over the specified number of + Abort any statement that takes over the specified number of milliseconds. A value of zero (the default) turns off the limitation. @@ -3419,7 +3439,10 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' The shared lock table is sized on the assumption that at most max_locks_per_transaction * max_connections distinct objects will need to - be locked at any one time. The default, 64, has historically + be locked at any one time. (Thus, this parameter's name may be + confusing: it is not a hard limit on the number of locks taken + by any one transaction, but rather a maximum average value.) + The default, 64, has historically proven sufficient, but you might need to raise this value if you have clients that touch many different tables in a single transaction. This option can only be set at server start. @@ -3469,7 +3492,8 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' advanced, extended, or basic. The default is advanced. The extended setting may be useful for exact backwards compatibility with - pre-7.4 releases of PostgreSQL. + pre-7.4 releases of PostgreSQL. See + for details. @@ -3501,7 +3525,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' This controls whether CREATE TABLE and - CREATE TABLE AS will include OIDs in + CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by @@ -3589,7 +3613,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' The following parameters are read-only, and are determined when PostgreSQL is compiled or when it is installed. As such, they have been excluded from the sample - postgresql.conf file. These options determine + postgresql.conf file. These options report various aspects of PostgreSQL behavior that may be of interest to certain applications, particularly administrative front-ends. @@ -3919,7 +3943,7 @@ plruby.bar = true # generates error, unknown class name Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current - transaction. Setting zero_damaged_pages to true causes + command. Setting zero_damaged_pages to true causes the system to instead report a warning, zero out the damaged page, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. But it allows you to get @@ -4170,7 +4194,7 @@ $ postmaster -o '-S 1024 -s' SEMVMX Maximum value of semaphore - at least 1000 (The default is often 32767, don't change unless asked to.) + at least 1000 (The default is often 32767, don't change unless forced to) @@ -4183,16 +4207,23 @@ $ postmaster -o '-S 1024 -s' shared memory parameter is SHMMAX, the maximum size, in bytes, of a shared memory segment. If you get an error message from shmget like Invalid argument, it is - possible that this limit has been exceeded. The size of the required + likely that this limit has been exceeded. The size of the required shared memory segment varies both with the number of requested buffers ( + + + Some systems also have a limit on the total amount of shared memory in + the system (SHMALL). Make sure this is large enough + for PostgreSQL plus any other applications that + are using shared memory segments. (Caution: SHMALL + is measured in pages rather than bytes on many systems.) @@ -4200,10 +4231,8 @@ $ postmaster -o '-S 1024 -s' memory segments (SHMMIN), which should be at most approximately 256 kB for PostgreSQL (it is usually just 1). The maximum number of segments system-wide - (SHMMNI) or per-process (SHMSEG) should - not cause a problem unless your system has them set to zero. Some - systems also have a limit on the total amount of shared memory in - the system; see the platform-specific instructions below. + (SHMMNI) or per-process (SHMSEG) are unlikely + to cause a problem unless your system has them set to zero. @@ -4261,15 +4290,16 @@ $ postmaster -o '-S 1024 -s' By default, only 4 MB of shared memory is supported. Keep in mind that shared memory is not pageable; it is locked in RAM. To increase the amount of shared memory supported by your - system, add the following to your kernel configuration - file. A SHMALL value of 1024 represents 4 MB of - shared memory. The following increases the maximum shared - memory area to 32 MB: + system, add something like the following to your kernel configuration + file: options "SHMALL=8192" options "SHMMAX=\(SHMALL*PAGE_SIZE\)" - For those running 4.3 or later, you will probably need to increase + SHMALL is measured in 4KB pages, so a value of + 1024 represents 4 MB of shared memory. Therefore the above increases + the maximum shared memory area to 32 MB. + For those running 4.3 or later, you will probably also need to increase KERNEL_VIRTUAL_MB above the default 248. Once all changes have been made, recompile the kernel, and reboot. @@ -4296,9 +4326,9 @@ options "SYSPTSIZE=16" Semaphores</> <para> - You may need to increase the number of semaphores. By - default, <productname>PostgreSQL</> allocates 34 semaphores, - which is over half the default system total of 60. Set the + You will probably want to increase the number of semaphores + as well; the default system total of 60 will only allow about + 50 <productname>PostgreSQL</productname> connections. Set the values you want in your kernel configuration file, e.g.: <programlisting> options "SEMMNI=40" @@ -4427,10 +4457,14 @@ sysctl -w kern.sysv.shmseg sysctl -w kern.sysv.shmall </programlisting> In OS X 10.3, these commands have been moved to <filename>/etc/rc</> - and must be edited there. Note that <filename>/etc/rc</> is usually + and must be edited there. You'll need to reboot to make changes + take effect. Note that <filename>/etc/rc</> is usually overwritten by OS X updates (such as 10.3.6 to 10.3.7) so you should expect to have to redo your editing after each update. </para> + <para> + <varname>SHMALL</> is measured in 4KB pages on this platform. + </para> </listitem> </varlistentry> @@ -4742,7 +4776,7 @@ sysctl -w vm.overcommit_memory=2 This is the <firstterm>Immediate Shutdown</firstterm>, which will cause the <command>postmaster</command> process to send a <systemitem>SIGQUIT</systemitem> to all child processes and exit - immediately (without properly shutting itself down). The child processes + immediately, without properly shutting itself down. The child processes likewise exit immediately upon receiving <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by replaying the WAL log) upon next start-up. This is recommended @@ -4753,29 +4787,34 @@ sysctl -w vm.overcommit_memory=2 </variablelist> </para> - <important> - <para> - It is best not to use <systemitem>SIGKILL</systemitem> to shut down - the server. This will prevent the server from releasing - shared memory and semaphores, which may then have to be done by - manually. - </para> - </important> + <para> + The <xref linkend="app-pg-ctl"> program provides a convenient + interface for sending these signals to shut down the server. + </para> <para> - The <acronym>PID</> of the <command>postmaster</command> process can be found using the - <command>ps</command> program, or from the file - <filename>postmaster.pid</filename> in the data directory. So for + Alternatively, you can send the signal directly using <command>kill</>. + The <acronym>PID</> of the <command>postmaster</command> process can be + found using the <command>ps</command> program, or from the file + <filename>postmaster.pid</filename> in the data directory. For example, to do a fast shutdown: <screen> $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput> </screen> </para> - <para> - The program <command>pg_ctl</command> is a shell script - that provides a more convenient interface for shutting down the - server. - </para> + + <important> + <para> + It is best not to use <systemitem>SIGKILL</systemitem> to shut down + the server. Doing so will prevent the server from releasing + shared memory and semaphores, which may then have to be done + manually before a new server can be started. Furthermore, + <systemitem>SIGKILL</systemitem> kills the <command>postmaster</command> + process without letting it relay the signal to its subprocesses, + so it will be necessary to kill the individual subprocesses by hand as + well. + </para> + </important> </sect1> <sect1 id="ssl-tcp"> @@ -4798,10 +4837,11 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput With <acronym>SSL</> support compiled in, the <productname>PostgreSQL</> server can be started with <acronym>SSL</> enabled by setting the parameter - <xref linkend="guc-ssl"> to on in <filename>postgresql.conf</>. When + <xref linkend="guc-ssl"> to <literal>on</> in + <filename>postgresql.conf</>. When starting in <acronym>SSL</> mode, the server will look for the files <filename>server.key</> and <filename>server.crt</> in the - data directory, which should contain the server private key + data directory, which must contain the server private key and certificate, respectively. These files must be set up correctly before an <acronym>SSL</>-enabled server can start. If the private key is protected with a passphrase, the server will prompt for the @@ -4811,16 +4851,18 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput <para> The server will listen for both standard and <acronym>SSL</> connections on the same TCP port, and will negotiate with any - connecting client on whether to use <acronym>SSL</>. See <xref + connecting client on whether to use <acronym>SSL</>. By default, + this is at the client's option; see <xref linkend="auth-pg-hba-conf"> about how to set up the server to require use of <acronym>SSL</> for some or all connections. </para> <para> For details on how to create your server private key and certificate, - refer to the <productname>OpenSSL</> documentation. A simple - self-signed certificate can be used to get started for testing, but a - certificate signed by a certificate authority (<acronym>CA</>) (either one of the global + refer to the <productname>OpenSSL</> documentation. A + self-signed certificate can be used for testing, but a + certificate signed by a certificate authority (<acronym>CA</>) + (either one of the global <acronym>CAs</> or a local one) should be used in production so the client can verify the server's identity. To create a quick self-signed certificate, use the following @@ -4881,7 +4923,8 @@ chmod og-rwx server.key One can use <application>SSH</application> to encrypt the network connection between clients and a <productname>PostgreSQL</productname> server. Done properly, this - provides an adequately secure network connection. + provides an adequately secure network connection, even for non-SSL-capable + clients. </para> <para> @@ -4896,7 +4939,7 @@ ssh -L 3333:foo.com:5432 joe@foo.com The first number in the <option>-L</option> argument, 3333, is the port number of your end of the tunnel; it can be chosen freely. The second number, 5432, is the remote end of the tunnel: the port - number your server is using. The name or the address in between + number your server is using. The name or IP address between the port numbers is the host with the database server you are going to connect to. In order to connect to the database server using this tunnel, you connect to port 3333 on the local machine: @@ -4905,7 +4948,15 @@ psql -h localhost -p 3333 template1 </programlisting> To the database server it will then look as though you are really user <literal>joe@foo.com</literal> and it will use whatever - authentication procedure was set up for this user. In order for the + authentication procedure was configured for connections from this + user and host. Note that the server will not think the connection is + SSL-encrypted, since in fact it is not encrypted between the + <application>SSH</application> server and the + <productname>PostgreSQL</productname> server. This should not pose any + extra security risk as long as they are on the same machine. + </para> + <para> + In order for the tunnel setup to succeed you must be allowed to connect via <command>ssh</command> as <literal>joe@foo.com</literal>, just as if you had attempted to use <command>ssh</command> to set up a -- 2.40.0