Getting Started with PostgreSQL and Python

Posted:	2007-10-25 12:54
Tags:	PostgreSQL

Install it (and the Python drivers like this):

$ sudo apt-get install postgresql-8.1 psycopg2

Or you can install psycopg2 from source like this:

$ apt-get install gcc-2.95 libpq-dev
$ export CC=/usr/bin/gcc-2.95
$ easy_install psycopg2

Setup a user and database (you need to be the postgres user):

$ su postgres
$ createdb --encoding=UTF8 yourdatabase

You can check the encoding of your database like this:

$ psql -l
          List of databases
     Name     |  Owner   | Encoding
--------------+----------+----------
 postgres     | postgres | UTF8
 template0    | postgres | UTF8
 template1    | postgres | UTF8
 yourdatabase | postgres | UTF8
(4 rows)

Now create a user:

$ createuser james

Answering no to the questions sets up the user with limited rights, if you want a user with lots of rights, choose yes.

Give the user a password (still as postgres):

$ psql yourdatabase
yourdatabase=# alter user james password 'password';
ALTER ROLE
yourdatabase=# \q

If you are signed into Debian as james you should now be able to connect as yourself:

$ psql yourdatabase

If you aren't james you need to do a bit more work. Finish being the postgres user if you haven't already and edit your /etc/postgresql/8.1/main/pg_hba.conf to give your new user access to the database. You'll need to read the full docs for all the details but adding a line like this will work to give james access only locally:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   yourdatabase    james                             md5

Restart PostgreSQL:

$ /etc/init.d/postgresql-8.1 restart

That's it. You should now be able to connect after being asked for your password.

Here is some more detailed info if you want to set up other permissions:

Setting up: http://glasnost.beeznest.org/articles/218

User administration: http://www.davidpashley.com/articles/postgresql-user-administration.html

(view source)