18.4. PHP and Perl | Mac OS X Tiger for Unix Geeks

< Day Day Up >

18.3. PostgreSQL

To get the source distribution of PostgreSQL, download the latest tarball from http://www.postgresql.org/download. At the time of this writing, the latest release was 8.0.1, so we downloaded postgresql-8.0.1.tar.bz2.

18.3.1. Compiling PostgreSQL

Before installing PostgreSQL, you must install readline (http://www.gnu.org/directory/readline.html). This program enables support for command-line editing and history in the PostgreSQL shell (psql). Use fink install readline to install it, if you have Fink installed. To compile PostgreSQL from source:

Extract the tarball:

     $ cd ~/src     $ tar xvfj ~/Desktop/postgresql-8.0.1.tar.bz2

Change to the top-level directory of the tar and run the configure script. We suggest specifying a prefix of /usr/local/pgsql so it stays out the way of any other binaries you have in /usr/local.
```
     $ cd postgresql-8.0.1/     $ ./configure --prefix=/usr/local/pgsql \     >   --with-includes=/sw/include --with-libs=/sw/lib
```
Next, type make to compile PostgreSQL. Go take a walk around the block while you wait (compiling could take 20 minutes or more).

18.3.2. Installing PostgreSQL

If everything went OK, you're ready to install. If it didn't go OK, check the PostgreSQL mail list archives (http://www.postgresql.org/lists.html) to see if anyone has reported the same problem you experienced and whether a fix is available (otherwise, you should submit a bug report).

Run make install as root:
```
     $ sudo make install
```

Create the postgres group and user (this is the PostgreSQL superuser). Be sure to choose an unused group ID and user ID:

     $ sudo niload group . <<EOF     > postgres:*:1001:     > EOF     $ sudo niload passwd . <<EOF     > postgres:*:1001:1001::0:0:PostgreSQL:/usr/local/pgsql:/bin/bash     > EOF

Create the data subdirectory and make sure that the postgres user is the owner of that directory:

     $ sudo mkdir /usr/local/pgsql/data     $ sudo chown postgres /usr/local/pgsql/data

Use sudo to get a shell as the postgres user (supply your own password at this prompt):
```
     $ sudo -u postgres -s     Password: ********     postgres$
```
Run the following commands to initialize the PostgreSQL installation:
```
     $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
```
You can now log out of the postgres user's shell.

18.3.3. Adding the Startup Item

Now you're ready to create a startup script for PostgreSQL (see "Startup Items" in Chapter 4). First, create the script shown in Example 18-1, save it as /Library/StartupItems/PostgreSQL/PostgreSQL, and mark it as an executable.

Example 18-1. Startup script for PostgreSQL

 #!/bin/sh # Source common setup, including hostconfig. # . /etc/rc.common StartService( ) {     # Don't start unless PostgreSQL is enabled in /etc/hostconfig     if [ "${PGSQL:=-NO-}" = "-YES-" ]; then         ConsoleMessage "Starting PostgreSQL"         sudo -u postgres /usr/local/pgsql/bin/pg_ctl  \           -D /usr/local/pgsql/data \           -l /usr/local/pgsql/data/logfile start     fi } StopService( ) {     ConsoleMessage "Stopping PostgreSQL"     su postgres -c \       "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop" } RestartService( ) {     # Don't restart unless PostgreSQL is enabled in /etc/hostconfig     if [ "${PGSQL:=-NO-}" = "-YES-" ]; then         ConsoleMessage "Restarting PostgreSQL"         StopService         StartService     else         StopService     fi } RunService "$1"

Next, create the following file as /Library/StartupItems/PostgreSQL/StartupParameters.plist:

     {       Description     = "PostgreSQL";       Provides        = ("PostgreSQL");       Requires        = ("Network");       OrderPreference = "Late";     }

Then, add the following line to /etc/hostconfig:

     PGSQL=-YES-

Now PostgreSQL will start automatically when you reboot the system. If you want, you can start PostgreSQL right away with:

     $ sudo SystemStarter start PostgreSQL

18.3.4. Configuring PostgreSQL

Before you proceed, you should add the following line to the .bash_profile and start a new Terminal window to pick up the settings (you should also add this to the postgres user's .bash_profile):

     export PATH=$PATH:/usr/local/pgsql/bin

By default, PostgreSQL comes with weak permissions; any local user can connect to the database without authentication. Before changing anything, you must start a shell as the postgres user with sudo and stay in this shell until the end of this section:

     $ sudo -u postgres -s     Password: ********     postgres$

To start locking things down and to set up a non-privileged user:

Create the postgres user's home database
```
     $ createdb
```

Set a password for the PostgreSQL superuser:

     postgres$ psql -U postgres -c \     >   "alter user postgres with password 'password' ;"

Under the default permissions, any local user can impersonate another user. So, even though you've set a password, it's not doing any good! You should edit /usr/local/pgsql/data/pg_hba.conf to require MD5 passwords, give the postgres user control over all databases, and change the configuration so users have total control over databases that have the same name as their username. To do this, change pg_hba.conf to read:

     # TYPE DATABASE USER     IP-ADDR   IP-MASK                        METHOD     local  all      postgres                                             md5     local  sameuser all                                                  md5     host   all      postgres 127.0.0.1 255.255.255.255                   md5     host   sameuser all      127.0.0.1 255.255.255.255                   md5     host   all      postgres ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff md5     host   sameuser all      ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff md5

Once you've made this change, reload the configuration with pg_ctl (from here on in, you'll be prompted for a password when you run psql as the postgres user):
```
     postgres$ pg_ctl -D /usr/local/pgsql/data reload
```

Now you're ready to add a normal user. Use the psql command to create the user and a database. Because the username and database name are the same, that user will be granted access to the database:

     postgres$ psql -U postgres -c "create database username ;"     Password: ********     CREATE DATABASE     postgres$ psql -U postgres -c \     >   "create user username with password 'password' ;"     Password: ********     CREATE USER

To give more than one user access to a database, create a group with the same name as the database (for example, create group databasename), and create users with the create user command as shown in step 5. Finally, add each user to the group with this command:

     alter group databasename add user username

18.3.5. Using PostgreSQL

After configuring PostgreSQL's security and setting up an unprivileged user, you can log in as that user and play around with the database:

     $ psql -U  username      Password: ********      Welcome to psql 7.4, the PostgreSQL interactive terminal.     Type:  \copyright for distribution terms            \h for help with SQL commands            \? for help on internal slash commands            \g or terminate with semicolon to execute query            \q to quit     username=> CREATE TABLE foo (bar CHAR(10));      CREATE TABLE     username=> INSERT INTO foo VALUES('Hello');      INSERT 17148 1     username=> INSERT INTO foo VALUES('World');      INSERT 17149 1     username=> SELECT * FROM foo;          bar     ------------      Hello      World     (2 rows)     username-> \q

For more information on building and using PostgreSQL, see Practical PostgreSQL by John C. Worsley and Joshua D. Drake (O'Reilly). Practical PostgreSQL covers installing, using, administrating, and programming PostgreSQL.

< Day Day Up >