# rpm -UVh postgresql
Set postgresql to start on boot
To check the current setting:
# chkconfig postgresql --list
To set the service to start on boot:
# chkconfig postgresql --level 3456 on
PostgreSQL configuration is in /var/lib/pgsql/data/postgresql.conf
Test pgsql
To make a database (which will be owned by the operating system user who issues the command):
bash-2.05a$ createdb <dbname>
All user and db management can be done through the PostgreSQL interactive terminal (which accept both SQL statements and Postgres metacommands) called psql (man psql for reference):
bash-2.05a$ psql
which takes you to a prompt which looks like:
To add/manage users:
PGSQL User Management
To create users:
Every db must be VACUUMed at least once every billion transactions with the vacuum command in psql (or risk catastrophic data loss). See Routine Vacuuming.
Host Based Access configuration file is in /var/lib/pgsql/data/pg_hba.conf
A few useful tools.
To list all postgresql users:
skyTemplate=> SELECT * FROM pg_user;
To list all postgresql databases:
skyTemplate=> SELECT * FROM pg_database;
To find out the currently selected database, look at the name in the prompt:
skyTemplate=>
Configure pgsql users and databases.
First, the user called 'postgres', who is the superuser for the postgresql service, must have a new, secure password assigned. Next, a file called 'admins' must be created to indicate that the postgres user can connect to any database with a password challenge. Then, other users must be added to pgsql. One user should be defined for each timeLines installation on the machine. Finally, the new database(s) for the timeLines installation(s) must be created.
-
As root, change the password for user postgres in the operating system:
# passwd postgres
This command will now challenge you for the new secure database admin password which should be carefully kept track of, with the usual precautions of length, and a mix of letters, numbers, and symbols.
-
Start a session as the postgres user:
# su postgres
which takes you to a prompt which looks like:
bash-2.05a$
- Connect to template1 as user postgres by using the command psql, which opens the PostgreSQL interactive terminal. (Whenever in the terminal, be sure to expect confirmations of any action; a null response is a sure sign of no action performed.)
bash-2.05a$ psql template1
-
Change the password for user 'postgres' by using the 'ALTER USER' command. Be sure to surround the password with single quotes/apostrophes as shown. The terminal should confirm by replying 'ALTER USER'. When using the following example, <newpassword> should be replaced with the new, secure password.
template1=# ALTER USER postgres WITH PASSWORD '<newpassword>';
-
Exit psql.
template1=# \q
- Exit postgres user session.
bash-2.05a$ exit
-
Initially, PostgreSQL allows anyone from the local machine to connect to the databases without a password challenge, so as to allow the administrator to set the password for the postgres user. Once that has been done, access to the databases must be securely restricted. This is controlled from the file /var/lib/pgsql/data/pg_hba.conf. Make the following edits to this file:
- Copy lines 221 and 222 to the bottom of the file. Delete the pound sign '#' from the front of the first line (the '#' is a comment character, which tells the configuration engine to ignore that instruction). Comment out line 250 by putting a '#' at the front of it. There is a good description of what this allows in the lines preceding 221.
- Copy line 190 to the bottom of the page (to allow remote connections with md5 password validation; description available just pre-190). Edit the word 'template1' to say 'all', and edit the ip in this line to match the machine's ip.
- Restart the postgresql service.
# service postgresql restart
-
All user management should be done as the user postgres.
# su postgres
Create a new db user. This new user should correspond to the site that db will be supporting. For example, our test site is called "app.skybuilders.com". The corresponding username should be "app_skybuilders_com". This should also be the name of the database. Permissioning allows users other than the admin user "postgres" to connect only to "skyTemplate" and the database with the same name as the user.
To make users (outside psql) as user postgres:
bash-2.05a$ createuser <options> <username>
In this case:
bash-2.05a$ createuser -d -P -A app_skybuilders_com
-d = allowed to create databases
-P = prompt for a password
-A = not allowed to create users
The password for the new user will now be prompted for. This should be another equally secure password, preferably different from the postgres user password, especially if there will be multiple timeLines installations on this machine. This password will be required by the timeLines installation script later in order to initialize the database and enable the application.
The password will be challenged for a second time for confirmation.
Finally the postgres admin password will be asked for, to authorize the new user.
The new user has now been created! Repeat this for any other instances of timeLines being installed on the machine.
(man createuser for details, user dropuser to delete users, and ALTER USER from inside psql to change setting for a user)
-
Connect to template1 as the postgres user. The command will challenge you for the postgres password.
bash-2.05a$ psql template1
-
This command requires pgsql 7.3. Currently 7.2 is installed. RPMs are conveniently available. Should upgrading the rpm be part of this install?
Or should a follow-on command such as UPDATE pg_database SET datdba = (SELECT usesysid FROM pg_shadow WHERE usename = '$thisname') WHERE datname = '$thisname';"); be used?
Create the database corresponding to the new user's site, assigning the new user as owner, using the "CREATE DATABASE" command. psql should respond "CREATE DATABASE".
template1=# CREATE DATABASE "<dbname>" OWNER <username>;
In this case:
template1=# CREATE DATABASE "<dbname>" OWNER app_skybuilders_com;
-
Connect to skyTemplate as the new db user. (Notice that the psql prompt ends with a ">" rather than a "#", to show that you are not a superuser (postgres).)
# psql skyTemplate <username>
- Connect to the new database.
skyTemplate=> \c "<dbname>"; - PostgreSQL and databases are now configured.
Install the timeLines application
- Download the installation archive file.
- Create a directory under the web root (in this case "/var/www/html/") using the domain name of the new site (in this case "/var/www/html/app.skybuilders.com").
- Decompress the installation archive into the the new directory. All necessary application server pages, directories, and image files will now be in place.
-
Permissions must be set on the application directory. Create a group called "skyApp", if it has not yet been created. This will be the group that gives anonymous and administrator access to all timelines application sites.
# /usr/sbin/groupadd skyApp -
Add users to this group. The apache user (in this case "apache") must be added so that requests to the web server will have permission to operate on the site. Other users that may need to be added to this group are any non-root administrators of the machine or the site, or any users who require ftp or sftp access (though any users who require different perissioning from the web users should be permissioned separately).
# /usr/sbin/usermod -G skyApp apache
# /usr/sbin/usermod -G skyApp dtd -
Change the group setting for the site directory (recursively, for all the files and directories below it).
# cd /var/www
# chown -R :skyApp app.skybuilders.com -
Set permissions for the site directory (recursively, for all the files and directories below it).
# chmod -R 775 *.* -
Restart the web server httpd.
# service httpd restart
Enable virtual servers under apache.
In /etc/httpd/conf/httpd.conf, add these lines to the virtual servers section (substituting the appropriate machine name and domain name):
NameVirtualHost *
<VirtualHost *>
ServerName skybox3.skybuilders.com
DocumentRoot /var/www/html
</VirtualHost>
<VirtualHost *>
ServerName app.skybuilders.com
DocumentRoot /var/www/app.skybuilders.com
</VirtualHost>
Note: Apache 2.0.40-8 (RedHat 8.0 distribution) does not currently support the IP wildcard, '*' in the example above. This version requires IP numbers to entered directly into the httpd.conf entries for NameVirtualHost and VirtualHost entries.
Save the file and restart httpd.
Enable proper logging for pgsql.
In /etc/init.d/postgresql, edit the line that reads:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D #PGDATA -p /usr/bin/postmaster start > /dev/null 2>&1" < /dev/null
Edit it to read:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D #PGDATA -p /usr/bin/postmaster -o '-i' start >> /var/log/pgsql 2>&1" < /dev/null
Save the file and restart postgresql.
Documentation on php.net is for version 4.2.0 and above. This machine is running 4.1.2. Perhaps an upgrade is in order (let us hope for a painless experience).
php 4.2.3 RPMs
Download all relevant RPMs and follow the instructions on the page. Very easy.
Run the timeLines installation script.
This script will build the new database's schema and populate it with initial data. Open a timeLines-compatible browser (Mozilla > 1.1, Netscape > 4.0, IE > 4) and navigate to the following URL (where <domain name> is the domain name of your new site):
http://<domain name>/timelines/
timeLines will now challenge for the database password for the new site (as set in the section Configure pgsql users and databases).
