postgresql - The LCFG PostgreSQL component
Configures and controls the PostgreSQL Database Server.
The directory containing the PostgreSQL database and configuration. Make sure that this location is adequately backed up, and ideally on a redundant disk set.
Note that backing up can not be performed by copying the data directory; instead use the run backup methods provided, or else the PostgreSQL pgdumpall command.
default /var/lib/pgsql/data (the default PostgreSQL data directory).
The port on which to listen for connections
default 5432 (the default PostgreSQL port)
The version running on the system, for example 8.1.
This should be set by the system to match the installed version of PostgreSQL. This will warn if the DB managed was generated using a different version. The component will not run if this resource is undefined.
default undefined (must be defined for component to run)
This user is the owner of the PostgreSQL database server, and can be set to any user. PostgreSQL will be run as this user, and appropriate directory permissions will be granted to allow this. This resource should and can not be set to root. The PostgreSQL server will not assume the Kerberos principal of the pgowner user, so the database files must be in a location that does not require Kerberos authentication, i.e. a local disk.
default postgres (the local user)
This value sets the group to which the PostgreSQL files are set. Mostly this value has no effect, as PostgreSQL requires 700 (user-only) permissions on its data, but this resource can allow a secondary group access to backup and log files.
default postgres (the local postgres group)
This value sets the directory to which PostgreSQL error logs are written, as redirected from stderr.
default undefined (errors are not written to file)
This value sets the directory to which PostgreSQL backup files are written, and from which backup files can be restored if necessary, when using the run backup methods provided by this component.
Note that the default backup directory is most likely unsuitable for most production databases, as /var/lib/pgsql is normally part of the root partition, and backups can be several gigabytes in size.
default /var/lib/pgsql/backups (should be changed)
This directory controls the location of the PostgreSQL key resources which postgres uses for SSL (x509) and Kerberos authentication. Leaving this to the default is usually acceptable. Key and certificate files are linked from this directory to the pgdata directory.
default /etc/postgresql
Sets any additional arguments required to create the database cluster (i.e. arguments to pass to initdb) on installation.
default --encoding=UTF-8
The options resource defines a list of key-value pairs to input directly to the postgresql.conf file.
These values might be set in a profile as follows:
!postgresql.options mSET(foo bar)
option_foo on
option_bar '/etc/nonexistent.conf'Some or none of these values may already be set according to the component's environment, so note that the option list might then require to be ADDed to, rather than SET, in order to retain these default values.
options listen_addresses ssl
option_listen_addresses '*'
option_ssl offSee the PostgreSQL manual for your server version for more details.
Note that the options resource provides a means to break a postgresql server without breaking that machine's LCFG profile, if an option name or value is not recognised by the server. Remember to check all PostgreSQL logs carefully after altering such a value.
The lcfg-postgresql component provides access control resources which configure a pg_hba.conf file on the database server. The file can be constructed using by adding an authentication entry to one of the auth type lists locals, hostip, hostssl and hostnossl.
The following connection types are available:
locals: Local connections, using unix sockets. * database : The database name. (default 'all') * user : The users allowed to access the database. (default 'all') * authtype : Authentication type (default 'md5'). * authopt : Authentication options (for postgres). (default undefined)
hostip: Host type connections, covering all IP connections. * ipdatabase : The database name. * ipuser : The users allowed to access the database. * ipaddress : The ip address of the authorised client. (default 127.0.0.1) * ipnetmask : The netmask to go with the ip addresss. (default 255.255.255.255) * ipauthtype : Authentication type. * ipauthopt : Authentication options (for postgres).
NOTE that this differs from lcfg-postgresql version 1.x, which uses "hosts" as its listname, and has no 'ip' prefix.
hostssl: HostSSL type connections, which the psql client will use by default if available. * ssldatabase : The database name. * ssluser : The users allowed to access the database. * ssladdress : The ip address of the authorised client. * sslnetmask : The netmask to go with the ip addresss. * sslauthtype : Authentication type. * sslauthopt : Authentication options (for postgres).
NOTE that this differs from lcfg-postgresql version 1.x, which uses "hostssls" as its list name.
hostnsl: HostNoSSL type connections, IP connections in an SSL environment, where SSL is disabled or unavailable for the specific connection. * nsldatabase : The database name. * nsluser : The users allowed to access the database. * nsladdress : The ip address of the authorised client. * nslnetmask : The netmask to go with the ip addresss. * nslauthtype : Authentication type. * nslauthopt : Authentication options (for postgres).
NOTE that this connection type is new to lcfg-postgresql version 2.
The options above can be used as follows. To configure access for all users to their own database via kerberos when using SSL, the following configuration would be required:
/* add the entry to the list of hostssl connections */
!postgresql.hostssl mADD(samek)
/* apply to all users who connect to the 'same' (i.e. named) database */
postgresql.ssluser_samek all
postgresql.ssldatabase_samek same
/* apply to all 129.215 subnet clients */
postgresql.ssladdress_samek 129.215.0.0
postgresql.sslnetmask_samek 255.255.0.0
/* force the use of kerberos authentication */
postgresql.sslauthtype_samek krb5
/* sslauthopt is blank by default */As well as specifying individual users or roles (or the all class) the lcfg-postgresql component allows servers to define user lists which contain a set of users. This can be specified using the '@' prefix, e.g.
!postgresql.user_xname @staffthe 'entity list' @staff can then be defined as follows:
!postgresql.entitylists mADD(staff)
!postgresql.members_staff mSETQ("user1,user2,user3")This generates a file called 'staff' in the postgres directory, whose content is specified by the members_staff resource. This list can be used by of the connection types above.
The default entity list 'admins' is defined to include the user 'postgres', which can be added to and/or used in a profile, for example as follows:
/* Allow admins auth-less local access */
!postgresql.locals mSET(lsk)
postgresql.user_lsk @admins
postgresql.authtype_lsk trustNote that the 'entity lists' are not true lists, and should be rewritten in full, and not mADD()ed to. For example,
!postgresql.members_admins mSETQ("postgres, adminuser")is a valid rewrite of the @admins entity list.
The name of a database for the monitoring system to check for existence of.
The postgres role that the monitoring system should use to connect.
The lcfg-postgresql component supports the standard methods:
* start starts the server as configured
* stop stops the server (using fast shutdown)
* restart stops the server if it is running, then starts it
* reload instructs postgres to reload its configuration from file.
* configure rewrites local configuration from the server's profile
and calls reload (see above)In addition to the standard methods, the lcfg-postgresql component augments the component run method to support the following commands:
* backupdb [database] Backs up the database with the given name.
Outputs into a gzipped SQL file named [database].sql.gz
Database backup files, like all other backup files, are placed in the C<pgbackupdir>
defined backup directory.
* backuprole [rolename] Backs up the role with the given name.
Outputs into a gzipped SQL file named [rolename].sql.gz
This backup contains the role and its password hash. It does not contain group roles
of which the selected role is a member. Role backup files, like all other backup files,
are placed in the C<pgbackupdir> defined backup directory.
* backupall [opts] [file] Backs up the data from the entire database server.
Passes [opts] directly to the pg_dumpall command.
Outputs into a gzipped SQL file named [filename].sql.gz
Take care when entering the backup options, as options are validated only by the
PostgreSQL server, and not by the component. If no filename is specified, the output
is dumped to 'backup.sql.gz'. Server backup files, like all other backup files, are
placed in the C<pgbackupdir> defined backup directory.
* resetperms Resets the permissions on an already-created database
such that it can be run with a new owner ID.
As a database owner change requires the server to be restarted and file-level
modification, this method must be run manually whenever the server's pgowner resource
changes. When starting, the PostgreSQL server will not run unless the server owns
its data directory.
The augmented Run commands are new to lcfg-postgresql version 2.Operating System: * Fedora3, Fedora5, Fedora6
PostgreSQL compatibility: * Tested with PostgreSQL 7.4 -- 8.2. * Some features unavailable on 7.x * Should work fully with versions >= 8.x * Might work with older versions at your own risk.
Component compatibility: * Intentionally incompatible with lcfg-postgresql version 1.x. * Resources must be modified to continue to work.
Research and Teaching Unit <rat-unit@inf.ed.ac.uk>
Hey! The above document had some coding errors, which are explained below:
You forgot a '=back' before '=head1'
'=item' outside of any '=over'