Installing Postgres 9 from source

Ok, so serious post time here. This morning I need to build a new postgres reporting database on RHEL 6.1. The RPM's that ship with RHEL 6.1 only provide 8.4.7, so it's source code time...

It's not that I'm against a 9.0 RPM install, I just don't like the default installation path, and much prefer to set it the same as many other servers, simply for consistency.

So, here we go:

• Step one - Create the postgres user
  Whilst logged in as a superuser.
  [root@pgreporting ~]# id
  uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
  [root@pgreporting ~]# useradd -c "PostgreSQL Server" -d /var/lib/pgsql -m postgres
  [root@pgreporting ~]# grep postgres /etc/passwd
  postgres:x:500:501:PostgreSQL Server:/var/lib/pgsql:/bin/bash
• Step 2 - Create the necessary mount points.
  There are a series of default mount points that I use, and these need to be set up. 
  A quick edit of the fstab, followed by a mount <%s> command where <%s> is the mount name is done (forgive me for not providing the full details here! Security and all!)
  
  Ouch!! " mount.nfs: Remote I/O error " - This can't be good... My cifs mount worked, but nfs mounts didn't. Is nfs installed?
  [root@pgreporting sqlbackups]# rpm -qa | grep nfs-utils
  nfs-utils-1.2.3-7.el6.x86_64
  nfs-utils-lib-1.1.5-3.el6.x86_64
  Restart nfslock then...
  [root@pgreporting sqlbackups]# /etc/init.d/nfslock restart
  Stopping NFS statd:                                        [  OK  ]
  Starting NFS statd:                                        [  OK  ]
  
  Nope - didn't work.
  
  rpcbind perhaps?
  [root@pgreporting sqlbackups]# /etc/init.d/rpcbind restart
  Stopping rpcbind:                                          [  OK  ]
  Starting rpcbind:                                          [  OK  ]
  
  Nope - Didn't work! Hmm....
  
  Ok - got it. Normally I'd apply the "defaults" option to an nfs mount, however, it seems there's a bug in this version on RHEL 6, as it uses version 4, so adding the option vers=3 solved it.
  
• Step 3 - Get the source, unzip and get ready to configure it!
  I already have the source located on a cifs mount, so a quick cp to /var/lib/pgsql/src:
  [root@pgreporting ~]# cd /var/lib/pgsql
  [root@pgreporting pgsql]# cp /sqlbackups/postgres/PostgresSource/Postgres/9.0.1/openlogic-postgresql-9.0.1-all-src-1.zip ./src
  
  [root@pgreporting pgsql]# cd ./src
  [root@pgreporting pgsql]# unzip openlogic-postgresql-9.0.1-all-src-1.zip 
  [ ... snip ... ]
  
  And we now have source code!


• Step 4 - Prerequisites.
  Now I plan to run Postgres with some bits and pieces enabled, so I'm going to need to add some packages to the machine:
  
  Pam:
  [root@pgreporting pgsql]# yum list available | grep pam-devel
  pam-devel.i686                          1.1.1-8.el6                       rhel61
  pam-devel.x86_64                        1.1.1-8.el6                       rhel61
  [root@pgreporting pgsql]# yum install pam-devel.x86_64
  
  Tcl:
  [root@pgreporting pgsql]# yum list available | grep tcl-devel
  tcl-devel.i686                          1:8.5.7-6.el6                     rhel61
  tcl-devel.x86_64                        1:8.5.7-6.el6                     rhel61
  [root@pgreporting pgsql]# yum install tcl-devel.x86_64
  
  Krb5
  Oooh - Hang on...
  [root@pgreporting pgsql]# yum list installed | grep krb5-devel
  krb5-devel.x86_64                   1.9-9.el6                          @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
  Already installed!
  
  ssl
  [root@pgreporting pgsql]# yum list available | grep openssl-devel
  openssl-devel.i686                      1.0.0-10.el6                      rhel61
  [root@pgreporting pgsql]# yum list installed | grep openssl-devel
  openssl-devel.x86_64                1.0.0-10.el6                       @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
  Again - Already installed
  
  [root@pgreporting pgsql]# yum list available | grep libxml2-devel
  libxml2-devel.i686                      2.7.6-1.el6                       rhel61
  [root@pgreporting pgsql]# yum list installed | grep libxml2-devel
  libxml2-devel.x86_64                2.7.6-1.el6                        @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
  Already installed.
  
  [root@pgreporting pgsql]# yum list available | grep openldap-developenldap-devel.i686                     2.4.23-15.el6                     rhel61openldap-devel.x86_64                   2.4.23-15.el6                     rhel61[root@pgreporting pgsql]# yum install openldap-devel.x86_64
  
  So let's configure!

• Step 5 - Configure
  Now this is the bit where I get all anal about file/folder locations. I have a load of databases scattered over all sorts of places. Typically they're all located in /var/lib/pgsql (unless I'm hiding them) so we need to configure with these options. The easiest way I find to get the correct locations is by running "pg_config --configure" on a server already installed and running:
  [pgsql1 ~]# pg_config --configure
  '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--disable-rpath' '--with-perl' '--with-tcl' '--with-tclconfig=/usr/lib' '--with-python' '--with-openssl' '--with-pam' '--with-krb5' '--enable-nls' '--enable-thread-safety' '--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql' '--with-docdir=/usr/share/doc' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables' 'build_alias=i686-redhat-linux-gnu' 'host_alias=i686-redhat-linux-gnu' 'target_alias=i386-redhat-linux-gnu'
We then "steal" some of these values and run tham against ./configure, however, we need to rememeber that the above config is a 32 bit config, and we're using 64 bit, so /usr/lib needs to become /usr/lib64 !!

Something I always get issues with is Tcl, so I nip this in the bud before it even happens:
[root@pgreporting src]# ln -s /usr/lib64/tclConfig.sh /usr/lib/tclConfig.sh

Basically, if you have the x86_64 install of Tcl, then postgres can't find tclConfig.sh, even with lib64 specified!

Now we're all configured: it's Compile time!
[root@pgreporting src]# make install
And wait for about 10-15 minutes for it all to "come together"

A quick:
[root@pgreporting src]# which postgres
/usr/bin/postgres
Tells me it worked!

• Step 6 - Initdb
  for this next step we need to be the user "postgres", so a quick "su - postgres" sorts that for us.
  
  I prefer to set some variables for postgres here, typically "PGDATA" and some aliases for pg_ctl, I'm not going to insult anyones intelligence here, I'm sure that this is a common task for all, so I'll cut out the posting of the edit here. I will however state that my default data directory is set as:
  /var/lib/pgsql/data
  
  So lets initdb!
  
Success. You can now start the database server using:


    postgres -D /var/lib/pgsql/data
or
    pg_ctl -D /var/lib/pgsql/data -l logfile start

Exactly what I wanted to see! A quick edit of postgresql.conf, pg_hba.conf and a few other symlinks etc, and we're all done!

Happy installations!

pg_hba.conf

So, the first of my technical posts to serve as me "taking in" the information I'm putting down, and to help me understand by explaining it! 


Before anyone comments that this is taken from the documentation, I have done this on purpose as a learning aid!


So the pg_hba.conf file (host based authentication) is located in the $PGDATA directory, and is used to allow and disallow access to the database by host. Now this is something I like about PostgreSQL.


Each entry comprises of the following format:


#TYPE DATABASE USER ADDRESS METHOD 


Without going into too much detail, this breaks down as follows:


TYPE:
local (Unix domain sockets from the local machine)
host (TCP/IP connections from remote hosts)
hostssl (As above but only with SSL enabled)
hostnossl (Opposite of above)

DATABASE
Name of the database that the entry pertains to. Can be set to "all" for any, or "sameuser" to connect to a database with the same name as the user connecting. "samerole"|"samegroup" to connect to a database that has the same name as role|group allocated to the connecting user. can be a comma separated list, or a file with a list of databases located on the host by specified @<filename>


USER
"all" meaning all users, a "+<groupname>" to allow users assigned to a particular group, again comma serperated, or file with: "@<filename>"


ADDRESS
This can be a CIDR-Address. 
   (eg: 192.168.1.1/32, 
         192.168.0.0/16 or 
          0.0.0.0/0 etc.Although this can be set to any bit range 0-32, however, it's typically easier to set within 8 bit ranges in my opinion. Also, this will accept IPV6 addresses too... 0 is all hosts)
or an IP and Mask.
   (e.g 192.168.1.1    255.255.255.255, 
          192.168.0.0    255.255.0.0 or
          0.0.0.0            0.0.0.0)
  These only apply to non Unix socket connections


METHOD
This is where things get a little more complex!
options are:
trust
A straight allow reject
A straight denial md5
Encrypted password crypt
Legacy Encrypted password method password
Clear text password - Errm... not advised!
krb5
Kerberos V5 authentication, libraries required ident
Check against pg_ident map - requires <mapname> after "ident" pam
Pluggable authentication Module - OS Verification, libraries required




So a typical set of entries being:
#TYPE DATABASE USER ADDRESS METHOD
local all all trust
# localhost connections are all trusted
host all dba 192.168.1.1/32 trust
#dba connection trusted
host all user1 194.216.0.0/16 md5
#passwords from 194.216* for user1
host postgres postgres 194.216.0.0/16 trust
#trust from 194.216* for postgres database connection by postgres

host all baduser 0.0.0.0/0 reject

# baduser is banned from all dbs

host all all 0.0.0.0/0 pam
# all other users pam authenticate



The rows in the file are processed top to bottom, and when a rule is matched it is used. so in this example:
host all user2 0.0.0.0/0 reject
host all user2 194.218.65.9/32 trust


User2 will *always* be rejected access from every ip as the second rule will never be hit. In this instance, I always arrange the file in the following order:

• local entries Then

• 32 bit host entries, rejects first, then auths, then trusts
• 24 bit host entries, rejects first, then auths, then trusts
• 16 bit host entries, rejects first, then auths, then trusts
• 8 bit host entries, rejects first, then auths, then trusts
• 0 bit host entries, rejects first, then auths, then trusts

and this seems to work for me!


In a nutshell, the permissions aren't actually that difficult to work out and can be quite secure... Well, only as secure as the server itself of course!