Home Explore Blog CI



postgresql

3rd chunk of `doc/src/sgml/runtime.sgml`
c55a42df84d3cc20e708d97c68d36fd05de79e1e27ae41330000000100000fa6
 directory.  It's generally recommendable that the
   <productname>PostgreSQL</productname> user own not just the data
   directory but its parent directory as well, so that this should not
   be a problem.  If the desired parent directory doesn't exist either,
   you will need to create it first, using root privileges if the
   grandparent directory isn't writable.  So the process might look
   like this:
<screen>
root# <userinput>mkdir /usr/local/pgsql</userinput>
root# <userinput>chown postgres /usr/local/pgsql</userinput>
root# <userinput>su postgres</userinput>
postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
</screen>
  </para>

  <para>
   <command>initdb</command> will refuse to run if the data directory
   exists and already contains files; this is to prevent accidentally
   overwriting an existing installation.
  </para>

  <para>
   Because the data directory contains all the data stored in the
   database, it is essential that it be secured from unauthorized
   access. <command>initdb</command> therefore revokes access
   permissions from everyone but the
   <productname>PostgreSQL</productname> user, and optionally, group.
   Group access, when enabled, is read-only.  This allows an unprivileged
   user in the same group as the cluster owner to take a backup of the
   cluster data or perform other operations that only require read access.
  </para>

  <para>
   Note that enabling or disabling group access on an existing cluster requires
   the cluster to be shut down and the appropriate mode to be set on all
   directories and files before restarting
   <productname>PostgreSQL</productname>.  Otherwise, a mix of modes might
   exist in the data directory.  For clusters that allow access only by the
   owner, the appropriate modes are <literal>0700</literal> for directories
   and <literal>0600</literal> for files.  For clusters that also allow
   reads by the group, the appropriate modes are <literal>0750</literal>
   for directories and <literal>0640</literal> for files.
  </para>

  <para>
   However, while the directory contents are secure, the default
   client authentication setup allows any local user to connect to the
   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> options to assign a password to the
   database superuser.<indexterm>
     <primary>password</primary>
     <secondary>of the superuser</secondary>
   </indexterm>
   Also, specify <option>-A scram-sha-256</option>
   so that the default <literal>trust</literal> authentication
   mode is not used; or modify the generated <filename>pg_hba.conf</filename>
   file after running <command>initdb</command>, but
   <emphasis>before</emphasis> you start the server for the first time. (Other
   reasonable approaches include using <literal>peer</literal> authentication
   or file system permissions to restrict connections. See <xref
   linkend="client-authentication"/> for more information.)
  </para>

  <para>
   <command>initdb</command> also initializes the default
   locale<indexterm><primary>locale</primary></indexterm> for the database cluster.
   Normally, it will just take the locale settings in the environment
   and apply them to the initialized database.  It is possible to
   specify a different locale for the database; more information about
   that can be found in <xref linkend="locale"/>.  The default sort order used
   within the particular database cluster is set by
   <command>initdb</command>, and while you can create new databases using
   different sort order, the order used in the template databases that initdb
   creates cannot be changed without dropping and recreating them.
   There is also a performance impact for using locales
   other than <literal>C</literal> or <literal>POSIX</literal>. Therefore, it is
   important to make this choice

Title: Securing and Configuring a PostgreSQL Database Cluster
Summary
This section discusses the security and configuration of a PostgreSQL database cluster, including setting permissions, enabling group access, securing the data directory, setting a password for the database superuser, and configuring the default locale and sort order for the cluster.