Oliver Elphick wrote:
You seem to have an extra comma there. It is doing a host look up of "127.0.0.1," and of course it is not finding it.The word "localhost" is quite acceptable. Since you are accessing the database from a program running on the same machine, you could also leave the hostname completely blank, which would force the use of a Unix socket instead of TCP/IP. well...the php script which attempt to connect to PostgreSQL is this ( i guess ) :<?php function opendb() { global $host, $username, $database, $password; $dbconn = pg_connect("host=$host, dbname=$database user=$username password=$password") or die("Cannot connect"); return $dbconn; } I don't make any mistype ( by typing coma after host value. the host value is store in a different file out this PHP script. as you can see in the script above, there's a coma after $host which is this techtables is bring from its tar ball. If I delete it then web page again displaying error message says that there's no such host called dbname=$database which is a variable after that coma. Which is meant that the coma thing is needed. I'm no PHP expert so I dunno how to fix it. ( the fact that there's no any coma after $database and so on is quiet make me confused ). Before I throwed this question in this room, I have already made some kind of experiment by changing the $host value from 127.0.0.1, localhost, hostname, even my IP address. and still not work. To make everything more clear, I attach two files which i guess ( ...again) hold the main rule of this techtables web apps. The global.inc file contain a very secret information about database connection use by techtables and the other file functions.inc contain every scripts which techtables depend on it to be run properly. I also include its HOW TO...... thanks for your attentions, - me - |
Novice Installation "How To"
(Thanks to Paul Woroshow for starting this HOWTO)
Current Version: April 21, 2003 version 1.11
History: See "History" at the end of this document
Index
1 - Introduction
2 - Preliminary Steps
2-1 - Apache
2.2 - PHP
2-3 - PostgreSQL
2-4 - Patches
2-5 - Start Services
3 - Install techtables
3-1 - Install the Files
3-2 - Create User and Database
4 - Adding Security
4-1 - Apache Web Server
4-2 - PostgreSQL
4-2.1 - PostgreSQL version 7.2.x
4-2.2 - PostgreSQL version 7.3.x
4-3 - Techtables Application
5 - Other Topics
5-1 - Upgrading from Red Hat 8.0 to 9.0
Appendices
Appendix A - Deleting Information
Appendix B - psql Utility
Appendix C - Reference Web Sites
History
1 - Introduction
This HOWTO is aimed primarily at people with minimal experience using postgreSQL and
Linux. The original idea being to share my experience and thereby assist other people
in successfully getting techtables up and running on their server.
These notes are specific to the Red Hat versions 8.0 and 9.0 distributions. There are
differences between Red Hat versions 8.0 and 9.0 which are noted throughout the
document. Keep in mind that if you are using another version or a different
distribution, the following will most likely need to be adjusted. However, the basic
steps will still apply.
It also assumes that you have root access to the server. If not, then you will likely
need to cajole your friendly system administrator in helping you or actually doing it
for you.
2 - Preliminary Steps
This first section deals with making sure that the basic environment is in place on
the Linux server.
2-1 - Apache
Run "rpm -qa | grep httpd" to see what rpms are already on your server.
My server has (RH v8.0/posgreSQL v7.2.x):
httpd-devel-2.0.40-11
httpd-2.0.40-11
httpd-manual-2.0.40-11
My server has (RH v9.0/posgreSQL v7.3.x):
httpd-2.0.40-21.1
httpd-manual-2.0.40-21.1
Optionally, make the postgreSQL documentation available via the web server. This
makes if very simple to look things up in the manual. You can make it available by
adding the following to /etc/httpd/conf/httpd.conf:
# postgreSQL manual
Alias /pgsqldoc "/usr/share/doc/postgreSQL-7.2.3/html"
<Directory "/usr/share/doc/postgreSQL-7.2.3/html">
Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
Allow from all
</Directory>
For RH v9.0/posgreSQL v7.3.x, change the directory above to
"/usr/share/doc/postgreSQL-7.3.2/html".
Remember that you will have to adjust the file locations if the documentation is
located in a different directory.
By adding the above and restarting your web server, you can then view the postgreSQL
documentation via http://<server name or ip>/pgsqldoc. Of course, this change will
not actually display the documentation until you install postgreSQL docs later in the
following steps.
2.2 - PHP
Run "rpm -qa | grep php" to see what rpms are already on your server.
My server has (RH v8.0/posgreSQL v7.2.x):
php-4.2.2-8.0.7
php-manual-4.2.2-8.0.7
php-pgsql-4.2.2-8.0.7
My server has (RH v9.0/posgreSQL v7.3.x):
php-4.2.2-17
php-manual-4.2.2-17
php-pgsql-4.2.2-17
For any rpm missing from your system, obtain it from Red Hat (ftp.redhat.com) or your
favourite mirror.
Install it with "rpm -ivh <package name>". The Red Hat rpm tool is excellent at
informing you of prerequisite packages. Simply obtain and install any perquisite
packages first then retry adding the python packages.
Make sure the file php.conf exists in /etc/httpd/conf.d after installing php. It
should contain:
#
# PHP is an HTML-embedded scripting language which attempts to make it
# easy for developers to write dynamically generated webpages.
#
LoadModule php4_module modules/libphp4.so
#
# Cause the PHP interpreter handle files with a .php extension.
#
<Files *.php>
SetOutputFilter PHP
SetInputFilter PHP
LimitRequestBody 524288
</Files>
#
# Add index.php to the list of files that will be served as directory
# indexes.
#
DirectoryIndex index.php
2-3 - PostgreSQL
Run "rpm -qa | grep postgresql" to see what rpms are already on your server.
My server has (RH v8.0/posgreSQL v7.2.x):
postgresql-libs-7.2.3-5.80
postgresql-7.2.3-5.80
postgresql-server-7.2.3-5.80
postgresql-docs-7.2.3-5.80
postgresql-devel-7.2.3-5.80
My server has (RH v9.0/posgreSQL v7.3.x):
postgresql-docs-7.3.2-3
postgresql-server-7.3.2-3
postgresql-libs-7.3.2-3
postgresql-7.3.2-3
On Red Hat, the default installation does not have the appropriate access permissions.
Failure to make the change which is about to be described, will result in techtables
failing with:
" Warning: pg_connect() unable to connect to postgreSQL server: FATAL 1: IDENT
authentication failed for user "techtables" in /var/www/html/techtables/functions.inc
on line 4 Cannot connect"
The trick is to change as /var/lib/pgsql/data/pg_hba.conf as follows:
RH v8.0/posgreSQL v7.2.x: Comment out the line near the end of the file which is
"local all ident sameuser". Change to "#local all ident sameuser". Then insert
"local all trust".
RH v9.0/posgreSQL v7.3.x: Comment out the line near the end of the file which is
"local all all ident sameuser". Change to "#local all all ident sameuser". Then
insert "local all all trust".
This allows different unix ids to be able to run as various postgreSQL ids.
IMPORTANT: The default postgreSQL superuser "postgres" can be used by anybody since
it does not have a password. This is really bad in a production environment. Same
applies to the "techtables" id which is created later. This all has to be cleaned up
before turning the server loose in a production environment.
ANOTHER IMPORTANT NOTE: If you already have postgreSQL up and running in a production
environment then you should not blindly make changes to pg_hba.conf. Making changes
will affect the security of your environment. You may require a password to add the
user and tables in the following steps. Since there are a variety of scenarios, I
can't provide specific recommendations.
2-4 - Patches
Remember to check if Red Hat has released any updated rpms for the applications above.
Red Hat continuously releases updated rpms which correct bugs or fixes security
flaws. Check the Red Hat site or your favourite mirror.
2-5 - Start Services
Make sure the services are running. You can start them individually by entering the
following as root:
service httpd start
service postgreSQL start
Add these to your startup scripts. How to do this is beyond this "how to". Consult
with your friendly administrator or search the Internet regarding how to do this.
There is a lot of good material on the Internet with the specifics.
How to check if the web server is running by doing one or more of the following:
a) Connect with your browser to the home page.
b) Enter "ps aux" to see which processes are running. You should see apache running.
c) Enter "netstat -atuvp". You should see port 80 open and associated with httpd
program.
How to check if postgreSQL is running:
There is a default superuser added by the Red Hat installation of "postgres".
Enter the following commands without the quotations:
"psql -U postgres template1"
It will look as follows:
[EMAIL PROTECTED] root]# psql -U postgres template1
Welcome to psql, 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
template1=#
At this point simply enter "\q" to quit.
In the above, the "-U postgres" means to run the command under the postgreSQL account
of postgres. You can only do this if /var/lib/pgsql/data/pg_hba.conf has been changed
per the instructions earlier in this howto.
If postgreSQL is not running then you will get an error message similar to:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
You will need to troubleshoot any errors before proceeding. How to do this is beyond
this "how to". Consult with your friendly administrator or search the Internet for
how to do this.
Make sure you do not proceed until you believe all the packages are functioning
properly.
3 - Install techtables
3-1 - Install the Files
Download and copy the installation file to the web server root directory. On RH, it
is /var/www/html.
Expand the file with "tar -zxvf techtables*". This extracts all the files and creates
a subdirectory of "techtables".
3-2 - Create User and Database
Enter: createuser -U postgres techtables
Results:
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
Enter: createdb -U postgres techtables
Results:
CREATE DATABASE
Change directory to where the installed files are: cd /var/www/html/techtables
Enter: psql -U techtables techtables < schema.postgres
Results:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'locations_pkey' for
table 'locations'
CREATE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'asset_status_pkey' for
table 'asset_status'
CREATE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'asset_types_pkey' for
table 'asset_types'
CREATE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'ticket_categories_pkey' for table 'ticket_categories'
CREATE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'contacts_pkey' for
table 'contacts'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'assets_pkey' for table
'assets'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
NOTICE: CREATE TABLE will create implicit sequence 'tickets_ticket_number_seq' for
SERIAL column 'tickets.ticket_number'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tickets_pkey' for
table 'tickets'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 16948 1
a bunch of lines deleted
INSERT 16975 1
4 - Adding Security
Before you can really turn this into a production system, you need to add some
security.
You should do a quick test of the techtables application prior to applying the
security restrictions. This way, if you encounter problems later, you know if the
basic installation has flaws or the security you added caused the problem.
So do the application tests now before proceeding.
I really can't emphasize worrying about security enough. Too many people say "it
can't happen to me". Well, it does happen, so take it seriously.
This section is by no means a definitive guide to securing your server. It is simply
a starting point.
4-1 - Apache Web Server
There is huge amount of material on the Internet concerning how to secure Apache plus
large numbers of books. So do a bit of research and apply it.
4-2 - PostgreSQL
4-2.1 - PostgreSQL version 7.2.x
Red Hat version 8.0 uses postgreSQL version 7.2.x. By default, this version has
password encryption turned off. This means passwords are stored in the clear.
To confirm this, log into posgreSQL using the psql command and do the following"
postgres=# SHOW PASSWORD_ENCRYPTION;
The system will respond with:
NOTICE: password_encryption is off
SHOW VARIABLE
The value of "PASSWORD_ENCRYPTION" affects how you setup pg_hba.conf. With encryption
disabled, you need to specify "password" as indicated in the instructions later in
this section.
At a minimum, you should have passwords on the various accounts.
Log into posgreSQL using the psql command and do the following.
postgres=# alter user techtables with password 'techtables';
postgres=# alter user postgres with password 'postgres';
Enter "\q" to quit.
IMPORTANT: Pick quality passwords. Do not use the lame ones in the examples above.
Now you have to update the configuration to enforce passwords. The trick is to change
as /var/lib/pgsql/data/pg_hba.conf as follows:
Comment out the line near the end of the file which is "local all trust". Change to
"#local all trust". Then insert "local all password".
Restart postgreSQL with "service postgresql restart". Now you need passwords.
To following shows the values of passwords and confirms that passwords are now in
place:
psql -U postgres
select * from pg_shadow;
NOTE: The semi-colon at the end of the select statement is important. Without it,
the command will not work.
NOTE: If you are not prompted for a password then double-check the setup. If cannot
login due to a typo when you originally created the passwords, you can reset
pg_hba.conf back to "trust" and redo the steps above.
When you set a password for the techtables user, then you also need to add the
password to /var/ww/html/techtables/globals.inc. Add it to the password field
($password = "techtables";).
4-2.2 - PostgreSQL version 7.3.x
Red Hat version 9.0 uses postgreSQL version 7.3.x. By default, this version has
password encryption turned on. This means passwords are stored as an MD5 hash value.
To confirm this, log into posgreSQL using the psql command and do the following"
postgres=# SHOW PASSWORD_ENCRYPTION;
The system will respond with:
password_encryption
---------------------
on
(1 row)
The value of "PASSWORD_ENCRYPTION" affects how you setup pg_hba.conf. With encryption
enabled, you need to specify "MD5" as indicated in the instructions later in this
section.
At a minimum, you should have passwords on the various accounts.
Log into posgreSQL using the psql command and do the following.
postgres=# alter user techtables with password 'techtables';
postgres=# alter user postgres with password 'postgres';
Enter "\q" to quit.
IMPORTANT: Pick quality passwords. Do not use the lame ones in the examples above.
Now you have to update the configuration to enforce passwords. The trick is to change
as /var/lib/pgsql/data/pg_hba.conf as follows:
Comment out the line near the end of the file which is "local all all trust". Change
to "#local all all trust". Then insert "local all all MD5".
Restart postgreSQL with "service postgresql restart". Now you need passwords.
To following shows the MD5 values of passwords and confirms that passwords are now in
place:
psql -U postgres
select * from pg_shadow;
NOTE: The semi-colon at the end of the select statement is important. Without it,
the command will not work.
NOTE: If you are not prompted for a password then double-check the setup. If cannot
login due to a typo when you originally created the passwords, you can reset
pg_hba.conf back to "trust" and redo the steps above.
When you set a password for the techtables user, then you also need to add the
password to /var/ww/html/techtables/globals.inc. Add it to the password field
($password = "techtables";).
4-3 - Techtables Application
The files in /var/www/html/techtables need to have their ownership and permissions
changed. Otherwise somebody can just view the file with the techtables postgreSQL
user name and password.
You will need to be root to do this.
Commands:
cd /var/www/html/techtables
chown apache:apache *
chmod 660 *
Now you can do ls -l and confirm that all files are owned by apache and only apache
has read/write. The "other" should have no access.
All of the files should look similar to this:
-rw-rw---- 1 apache apache 3174 Mar 9 17:37 functions.inc
Remember that if your web server is running under a different user name then that name
will need to be substituted for it in the above instructions.
5 - Other Topics
5-1 - Upgrading from Red Hat 8.0 to 9.0
Red Hat uses a different version of postgreSQL in their version 8.0 and 9.0
distribution. RH Version 8.0 uses postgreSQL v7.2.x and RH version 9.0 uses
postgreSQL v7.3.x. This will impact you when you upgrade your server from RH 8.0 to
RH 9.0 because the database internal format is different. The following instructions
guide you through the steps necessary to upgrade and continue using techtables.
These same steps will apply if you simply upgrade postgreSQL from v7.2.x to v7.3.x.
Background (quoted from the PostgreSQL 7.3.2 Documentation section 9.3. Migration
between releases): As a general rule, the internal data storage format is subject to
change between releases of PostgreSQL. This does not apply to different "patch
levels", these always have compatible storage formats. For example, releases 7.0.1,
7.1.2, and 7.2 are not compatible, whereas 7.1.1 and 7.1.2 are. When you update
between compatible versions, then you can simply reuse the data area in disk by the
new executables. Otherwise you need to "back up" your data and "restore" it on the
new server, using pg_dump. (There are checks in place that prevent you from doing the
wrong thing, so no harm can be done by confusing these things.)
The following instructions assume that techtables is basically the only database in
the system. If you have multiple databases then you will need to coordinate with the
other people using the database server prior to upgrading.
Before you upgrade, do the following on the working system.
"service postgresql stop"(stop the postgresql postmaster)
Being conservative, make a full disk backup of the files.
"cp -R /var/lib/pgsql/data /var/lib/pgsql/backups/data"
Edit /var/lib/pgsql/data/pg_hba.conf and change the security back to "trust".
"service postgresql start"(start the postgresql postmaster)
"cd /var/lib/pgsql/backups"
"pg_dumpall -U postgres > backup.txt"
Now perform you upgrade from RH 8.0 to 9.0. Once you have done this, you will not be
able to start postgreSQL. It will generate an error message indicating that the
database format is old and needs to be upgraded.
"service postgresql stop"(stop the postgresql postmaster)
Make sure you have a backup of the data before deleting it in the next step.
"/bin/rm -R /var/lib/pgsql/data" (Deletes the data directory)
"su postgres" (For the next step, you need to be the postgres user)
"initdb -D /var/lib/pgsql/data" (Creates an empty postgres environment)
"exit" (Back to root)
Edit /var/lib/pgsql/data/pg_hba.conf and change the security back to "trust". Note
that the format in this version is different: "local all all trust".
"service postgresql start"(start the postgresql postmaster)
"psql -U postgres template1 < backup.txt" (Imports the users and data)
By default, the postgres account within postgreSQL has a blank password. At this
point, set a new password per the instructions elsewhere in this document.
Edit /var/lib/pgsql/data/pg_hba.conf and change the security to "md5". Note that the
format in this version is different: "local all all md5".
"service postgresql restart"(restart the postgresql postmaster)
You are done! Go for it.
APPENDICIES
Appendix A - Deleting Information
This is a compendium of commands that I found and is not very structured.
There may be times when you have to delete something from a table but there is no
on-line screen to do this. In this case, use "psql -U techtables techtables".
Example:
delete from assets where asset_tag = '';
There following are examples of how to delete users / databases from the command line.
bash-2.05b$ dropuser -U postgres techtables
DROP USER
bash-2.05b$ dropdb -U postgres techtables
DROP DATABASE
Appendix B - psql Utility
Invoke with "psql -U techtables techtables".
\q - Quit
\z - To show privileges on table.
Appendix C - Reference Web Sites
http://www.postgreSQL.org/
postSQL home site
http://www.commandprompt.com/ppbook/book1.htm
Practical postgreSQL book
HISTORY
April 21, 2003 version 1.11
- Minor corrections
- Updated to reflect Red Hat version 9
- Updated to include migration instructions for upgrading from PostgreSQL v7.2 to v7.3
March 16/2003 version 1.00
- Initial release of the document
Assets | Tickets | Contacts | Admin";
}
function search_box($table){
global $dbconn;
$query="select * from $table limit 1";
$result=pg_exec($dbconn, $query);
$numfields=pg_numfields($result);
echo "";
echo "";
echo "";
// loop through field names
for ($i=0; $i < $numfields; $i++) {
$name=pg_fieldname($result, $i);
echo "$name";
};
echo "";
echo "";
echo "";
}
function fill_options($query) {
// fills out an option drop-down
global $dbconn;
$result = pg_exec($dbconn, $query);
$numrows = pg_numrows($result);
//echo"";
// Loop through result rows
for ($j = 0; $j < $numrows; $j++) {
$currentrow = pg_fetch_row($result, $j);
echo"$currentrow[0]\n";
}
}
function fill_table($query, $linkto) {
// prints out table contents, creates link for first column
global $dbconn;
$show = 20;
$result = pg_exec($dbconn, $query);
$numrows = pg_numrows($result);
// Loop through result rows
for($j = 0; $j < $numrows; $j++) {
$current_row = pg_fetch_row($result, $j);
// alternate colors for each row
if($j%2==0) { echo"Search Results:"; if ($table == "assets") { fill_table("select asset_tag, asset_type, asset_model, asset_status from assets where $field ~* '$item' order by asset_tag", "assetdetail.php"); } else if ($table == "contacts") { fill_table("select contact_name, contact_type, contact_email, contact_phone, contact_location from contacts where $field ~* '$item' order by contact_name", "contactdetail.php"); } else if ($table == "tickets") { fill_table("select ticket_number, ticket_short_description, ticket_status, ticket_contact from tickets where $field ~* '$item' order by ticket_status DESC, ticket_number ASC", "ticketdetail.php"); } } ?>
signature.asc
Description: OpenPGP digital signature
