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