Setting up a PostgreSQL Database
- Working with Connections and Data Sources
- Using PostgreSQL and PHP
- Select, Insert, Update, and Delete Queries
- Other Database Functions
This chapter discusses setting up a database. More specifically, it discusses setting up a PostgreSQL database and using that database to store information that will be made available to Web browsers. Many open-source applications are available to Web developers. This chapter assumes that you have an Apache Web server configured to use PHP, and that your configuration of PHP is compiled to integrate with PostgreSQL. Examples of this configuration are detailed in Chapter 1, "Basic PHP."
Working with Connections and Data Sources
The standard PostgreSQL installation uses port 5432 to listen for TCP/IP connections. This is the port on which the postmaster process listens for connections. The postmaster is the process that manages the communications between the front-end clients and the back-end server. For the examples included in this book, it is assumed that the port is left at the default, 5432. In other words, the connections made to the PostgreSQL back end are assumed to be on port 5432. You will see how to make this connection using PHP later in this chapter.
Setting Up the Database
Initial installation and configuration of the PostgreSQL package is beyond the scope of this chapter. Please refer to the PostgreSQL Web site (http://www.postgresql.org) for installation and configuration documentation.
This section describes the initial creation of the test database that will be used in the remainder of the examples in this chapter. The first thing to do is check your PostgreSQL installation for the existence of a database named test. All the examples in this chapter refer to this test database; if this database already exists, you might want to create a database with a different name.
To check for the existence of a test database, use the following command:
psql -l
The psql application is the PostgreSQL client that is used to interact with the backend. The -l option lists the available databases. Your output should look similar to this:
[postgres@phoenix bin]$ psql -l datname |datdba|encoding|datpath ---------+------+--------+--------- template1| 40| 0|template1 (1 row)
To create a database named test, use the following command:
[postgres@phoenix bin]$ createdb test
You can now view your newly created database by using the –l option once again. The output should be similar to this:
[postgres@phoenix bin]$ psql -l datname |datdba|encoding|datpath ---------+------+--------+--------- template1| 40| 0|template1 test | 40| 0|test (2 rows)
Now that the database is created, you must create a user that will have access to the database. Remember that this user will be running with the same permissions as the Web server. In our examples, the Web server will be running as the user nobody, so the user that must be created in PostgreSQL must be named nobody.
To check for the existence of the user nobody, you can query the pg_user table using psql. The sequence of commands looks like this:
[postgres@phoenix bin]$ psql test Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: test test=>
This will bring you to the psql command prompt. Use the following SQL select query to check for the nobody user:
test=> select * from pg_user; usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd | --------+--------+-----------+--------+--------+---------+--------+ postgres| 40|t |t |t |t |********| (1 row)
To create a database user named nobody, use the createuser utility that is included in the PostgreSQL installation. The output of the command will look something like this:
[postgres@phoenix bin]$ createuser Enter name of user to add ---> nobody Enter user's postgres ID or RETURN to use unix user ID: 99 -> Is user "nobody" allowed to create databases (y/n) n Is user "nobody" a superuser? (y/n) n createuser: nobody was successfully added Shall I create a database for "nobody" (y/n) n don't forget to create a database for nobody
After the user is created, the pg_user table will look like this:
test=> select * from pg_user; usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd | --------+--------+-----------+--------+--------+---------+--------+ postgres| 40|t |t |t |t |********| nobody | 99|f |t |f |t |********| (2 rows)
For simplicity, we will use a single table in our examples. Create a text file named database.sql with the following contents:
create table contacts (
cid int4 DEFAULT NEXTVAL('c'),
name char (50),
address char (50),
city char (50),
state char (2),
zip char (10),
phone char (25),
fax char (25),
email char (50),
primary key (cid));
create sequence c start 101;
grant all on contacts to nobody;
grant all on c to nobody;
These SQL commands create a table named contacts in the test database. The script also creates a sequence that will be used to generate a unique contact ID. The last thing that this script does is grant permissions to the nobody user for the table and the sequence. From the psql command line, the output of this command will look like this:
test=> \i database.sql
create table contacts (
cid int4 DEFAULT NEXTVAL('c'),<
name char (50),
address char (50),
city char (50),
state char (2),
zip char (10),
phone char (25),
fax char (25),
email char (50),
primary key (cid));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'contacts_pkey'
for table 'contacts'
CREATE
create sequence c start 101;
CREATE
grant all on contacts to nobody;
CHANGE
grant all on c to nobody;
CHANGE
EOF
test=>
You can now check for the existence of the table and sequence by using the \dt (display tables) and \ds (display sequences) commands. The output of these commands should look like this:
test=> \dt Database = test +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | postgres | contacts | table | +------------------+----------------------------------+----------+ test=> \ds Database = test +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | postgres | c | sequence | +------------------+----------------------------------+----------+
To verify that the permissions were changed, use the \z command. The output should look something like this:
test=> \z
Database = test
+----------+--------------------------+<
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| c | {"=","nobody=arwR"} |
| contacts | {"=","nobody=arwR"} |
+----------+--------------------------+