karniv0re's Journal: Project: CraigsBots, Part 1: Setting Up PostgreSQL With PHP

I've launched into a new statistics project that takes a look at Craigslist. I won't go into detail yet. You can find that in the report when I'm finished.

What I want to share here is how I got PostgreSQL working with PHP 5 and Apache 2 on Debian Lenny.

First there's the usual steps of installing all the packages. That's not hard to figure out. The hard part is getting it all talking.

First, set up the user with the password. (stolen from: http://hocuspokus.net/2007/11/creating-new-accounts-in-postgresql

Getting a new account set up on PostgreSQL is a simple process...

Create our new user:

$ sudo su postgres -c createuser karniv0re

Then you have to give this new user role a name (I called it daz), and then say 'y' to the question "Shall the new role be a superuser?" if you want the user to be an administrator.

Give the user a database password (this does not have to be the same as their unix password):

$ sudo su postgres -c psql
postgres=# ALTER USER karniv0re WITH PASSWORD 'mypassword';
postgres=# \q

Finally, give the new user a database to play with:

$ sudo su postgres
postgres@home$ createdb -O karniv0re mynewdatabase

Then create your SQL in a file and import it: SQL (stolen from http://www.devshed.com/c/a/PHP/PHP-and-PostgreSQL/1/)

CREATE TABLE addressbook (id serial, name varchar(255), address text,
tel varchar(50), email varchar(255));

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Bugs
Bunny', 'The Rabbit Hole, Looney Toons, USA', '123 4567',
'[e-mail]');

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Robin
Hood', 'Sherwood Forest', 'None', '[e-mail]');

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Sherlock
Holmes', '221B Baker Street, London 16550, England', '911 1822',
'[e-mail]');

Import it:

$ /usr/local/pgsql/bin/psql -d test -f /home/postgres/addressbook.sql

Now, you have to grant permission to the individual tables. I don't know a better way to do this, but this works.

$ sudo su postgres
postgres@home $ psql
postgres=# grant all privileges on database mydbname to
karniv0re;
postgres=# grant all privileges on tablename1 to karniv0re;
postgres=# grant all privileges on tablename2 to karniv0re;
postgres=# grant all privileges on tablename3 to karniv0re;
postgres=# grant all privileges on tablename4 to karniv0re;
postgres=# grant all privileges on tablename5 to karniv0re;

And we're good to go.