14.2 PostgreSQL

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

14.2.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. You also need the most recent version of bison (http://www.gnu.org/software/bison/bison.html), which you can obtain with fink install bison (double check to ensure that /sw/bin appears first in your $PATH ; this is the default after you've installed Fink).

To compile PostgreSQL from source:

  1. Extract the tarball:

     $  cd ~/src  $  tar xvfj ~/Desktop/  postgresql-7.4.tar.bz2 
  2. 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-7.4  $  ./configure --prefix=/usr/local/pgsql \  >  --with-includes=/sw/include --with-libs=/sw/lib  
  3. Next, type make to compile PostgreSQL. Go take a walk around the block while you wait (compiling could take 30 minutes or more).

14.2.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).

  1. Run make install as root:

     $  sudo make install  
  2. 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  
  3. 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  
  4. Use sudo to get a shell as the postgres user (supply your own password at this prompt):

     $  sudo -u postgres -s  Password:  ********  postgres$ 
  5. Run the following commands to initialize the PostgreSQL installation:

     $  /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data  
  6. You can now log out of the postgres user's shell.

14.2.3 Adding the Startup Item

Now you're ready to create a startup script for PostgreSQL (see Section 2.2 in Chapter 2). First, create the script shown in Example 14-1, save it as /Library/StartupItems/PostgreSQL/PostgreSQL , and mark it as an executable.

Example 14-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"     /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 "" 

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 :


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

 $  sudo SystemStarter start PostgreSQL  

14.2.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:

  1. Create the postgres user's home database

     $  createdb  
  2. Set a password for the PostgreSQL superuser:

     postgres$  psql -U postgres -c \  >  "alter user postgres with password '   password   ';"  
  3. 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                   md5 host   sameuser all                   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 
  4. 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  
  5. 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   

14.2.5 Playing with 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.

Mac OS X Panther for Unix Geeks
Mac OS X Panther for Unix Geeks
ISBN: 0596006071
EAN: 2147483647
Year: 2003
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net