S60postgreSQL

Synopsys


#
# Ok We need to configure and enable PostgreSQL
#

Script


set -x
echo "%Post: Fixup for PostgreSQL"
# IT IS IMPERATIVE THAT WE HAVE NOT YET MOVED TO USING
# A PAM AUTHENTICATION SERVICE WHICH IS NOT FILES OTHERWISE THIS COMMAND
# WILL NOT WORK
if [ "$OS" != "fc4" ]; then
/bin/su -l postgres -s /bin/sh -c "/usr/bin/initdb -n -D /var/lib/pgsql/data"
echo "%Post: Fixup for PostgreSQL $?"
# we have to do this to init the databases so we can then fix the configs
# The first time the machine starts it will populate the database
# the important files are all /var/lib/pgsql/data
# 
echo "%Post: Fixup for pg_hba.conf"
# pg_hba.conf contains the authentication mechanisms
#------------
mv /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf-$VERSION
cat >/var/lib/pgsql/data/pg_hba.conf <<EOF
#Type Db   user IP-address IP-Mask           Method
host  all  all  0.0.0.0 0.0.0.0              pam
local all  all                               pam
EOF
chown postgres:postgres /var/lib/pgsql/data/pg_hba.conf
#
# auth  required /lib/security/pam_ldap.so debug
# account required /lib/security/pam_ldap.so debug
#
echo "%Post: Fixup for postgresql.conf"
#  postgresql.conf contains syslog parameters etc
#------------------------
mv /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf-$VERSION
cat >/var/lib/pgsql/data/postgresql.conf  <<EOF
                  tcpip_socket = true
               max_connections = 32
superuser_reserved_connections = 2
                          port = 5432
                   LC_MESSAGES = 'en_GB.UTF-8'
                   LC_MONETARY = 'en_GB.UTF-8'
                    LC_NUMERIC = 'en_GB.UTF-8'
                       LC_TIME = 'en_GB.UTF-8'
                        syslog = 2
               syslog_facility = 'LOCAL2'
                  syslog_ident = 'postgres'
EOF

# Distribution specific configuration
if [ "$OS" == "rhel3" ]; then
  cat >> /var/lib/pgsql/data/postgresql.conf <<EOF
# pre-version 7.4
               hostname_lookup = false
              show_source_port = false
EOF

else
  cat >> /var/lib/pgsql/data/postgresql.conf <<EOF
		   log_hostname = true
                log_source_port = true
EOF
fi

chown postgres:postgres /var/lib/pgsql/data/postgresql.conf
fi

if [ "$OS" == "fc4" ]; then
# FC4 doesn't allow initing of the DB during kickstart
# (Due to issues with the permissions on /dev/null among other things)

# However, the db gets initialised on first startup, if /var/lib/pgsql is empty
# So we can bung a test into rc.local to do the config fix ups

cat >> /etc/rc.d/rc.local<<EOF

# Fix ups for postgres
if [ -f /var/lib/pgsql/data/pg_hba.conf ]; then
if [ ! -f /var/lib/pgsql/data/pg_hba.conf-$VERSION ]; then
# pg_hba.conf contains the authentication mechanisms
#------------
  mv /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf-$VERSION
  cat > /var/lib/pgsql/data/pg_hba.conf <<HBA
#Type Db   user IP-address IP-Mask           Method
host  all  all  0.0.0.0    0.0.0.0           pam
local all  all                               pam
HBA
  chown postgres:postgres /var/lib/pgsql/data/pg_hba.conf
  PGRESTART=1
fi
fi

if [ -f /var/lib/pgsql/data/postgresql.conf ]; then
if [ ! -f /var/lib/pgsql/data/postgresql.conf-$VERSION ]; then
#  postgresql.conf contains syslog parameters etc
#------------------------
mv /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf-$VERSION  
cat > /var/lib/pgsql/data/postgresql.conf <<PGCONF
max_connections 		= 32
superuser_reserved_connections 	= 2
LC_MESSAGES			= 'en_GB.UTF-8'
LC_MONETARY			= 'en_GB.UTF-8'
LC_NUMERIC			= 'en_GB.UTF-8'
LC_TIME				= 'en_GB.UTF-8'
log_destination			= 'syslog'
syslog_facility			= 'LOCAL2'
syslog_ident			= 'postgres'
log_line_prefix			= '%r'
log_hostname			= true
PGCONF
  chown postgres:postgres /var/lib/pgsql/data/postgresql.conf
  PGRESTART=1
fi
fi

if [ "\$PGRESTART" == "1" ]; then
   service postgresql restart
fi    
EOF

fi

# PAM always needs configuring
echo "%Post: Fixup for pam"
# we discovered that both auth and account were required..
cat >/etc/pam.d/postgresql <<EOF
auth       required     pam_stack.so service=system-auth
account    required     pam_stack.so service=system-auth
#auth       required     pam_warn.so
EOF

# Always need to set postgres to start on boot

if [ "$OS" != "rhel3" ]; then
  chkconfig --level 345 postgresql on
else
  chkconfig --level 345 rhdb on
fi

Examples and Testing


#
# For PAM authentication to work, you need to create the users
# within the posgtres users tables using
#     createuser  -i 'uid' username
# ideally this should be taken care of by a script querying getent
# and building and updating the table dynamically
# You can test that they work by doing
#     psql -U username template1
# this should asking for the username's password and authentication
# NB...
#    when creating users, this must initially be done as the "postgres" user
# and the password it asks for is that of "postgres" which must be set.
#
# to manipulate the database you need to do
# su - postgres
# createdb  testdb
# psql testdb
#   create table test_table ( col_1 char(15), col_2 char(20));
#   insert into table test_table ( 'test 1', 'test 2' );
#   select * from test_table;
# -      col_1      |        col_2
# - ----------------+----------------------
# -  test 1         | test 2
# - (1 row)
# -
#   \q