Home > Articles > Data > MySQL

Setting up a PostgreSQL Database

  • Print
  • + Share This
Learn to set up a PostgreSQL database and use that database to store information that will be made available to Web browsers.
This sample chapter is from PHP Developer's Dictionary, by Allen Wyke.

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"}   |
 +----------+--------------------------+
  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.