Installing and configuring PostgreSQL
I’ve tried quite a few times to get PostgreSQL up and running on my system. I’ve read plenty about how it’s ‘better’ than MySQL. Although I’ve not been convinced by that, purely the fact that I couldn’t install it was irritating me enough to give it another go.
For the first part, I was mostly able to follow the Ubuntu PostgreSQL documentation.
First, I installed it:
$ sudo aptitude install postgresql
then I connected using the
postgres default user:
$ sudo -u postgres psql template1
created a password for the
postgres user and quit:
template1=# \password postgres template1=# \q
I like to be able to administer my databases under my own username, so next I created a user for me, gave it a password and created a database:
$ sudo -u postgres createuser --superuser myusername $ sudo -u postgres psql postgres=# \password myusername postgres=# \q $ createdb myusername
with these changes and the default
Authentication configuration, PostgreSQL is now configured to now accept my linux user as a valid user, all I need to do is type
psql at the command prompt and I’m away.
Next I wanted to be able to connect to databases within Python scripts. I also wanted to have a separate user for doing that, most likely a separate user per application.
psycopg2 appears to be the most prolific Python package for working with PostgreSQL. Installing it was unfortunately not as easy as
pip install psycopg2, but it almost was. Searching for the error I got pointed me straight at a Stack Overflow answer for how to install psycopg2 with pip on python. I’m not sure how the world got anything done before Stack Overflow.
$ sudo aptitude install libpq-dev python-dev $ sudo pip install psycopg2
That worked fine, allowing me to write a quick test in Python:
#!python import psycopg2 DSN = "dbname=myusername" conn = psycopg2.connect(DSN) curs = conn.cursor() curs.execute("SELECT * from test") print curs.fetchall() conn.close()
I had already created a
test table with
psql. I then tested running the script as a different user:
$ sudo python postgres_test.py
as expected, it didn’t work; There is no user
root in PostgreSQL. Creating a new user for my application was almost as straightforward as creating my own user, with one extra bit at the end:
$ createuser myapp Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n $ createdb myapp $ psql myusername=# \password myapp myusername=# ALTER DATABASE myapp OWNER TO myapp
The last line changes the owner of the
myapp database to belong to the
myapp user, that way only that user has any access to it, and nothing else. There’s probably a better way to do this, but this worked for me!
To test, I connected to the database as the
myapp user to check that everything was working:
$ psql -U myapp psql: FATAL: Ident authentication failed for user "myapp"
Hmm. I then remembered from my previous excursions that there’s a configuration file for PostgreSQL that needs monkeying with. I found mine in
$ sudo nano /etc/postgresql/8.4/main/pg_hba.conf
There’s a lot of comment-documentation in the file. By default, it only lets in linux users, so I could have created a new linux user
myapp and run
psql as that user (as I had been above as the
postgres user). I didn’t want to do that though.
Initially, I added a line to the file:
# Database administrative login by UNIX sockets local all postgres ident # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident local myapp myapp password
but it didn’t work, then I realised that PostgreSQL takes the rules in order and stops processing when it finds one that matches, the line above matches all users, so my line would need to go above it:
# "local" is for Unix domain socket connections only local myapp myapp password local all all ident
$ psql -U myapp Password for user myapp:
I could use my new user and database in python by changing the
#!python DSN = "dbname=myapp user=myapp password=secret"
All working! Next I should probably get my head around SQLAlchemy.
I’m not really sure why I struggled so much in my past tries with PostgreSQL, I think I hadn’t quite gotten my head around how it dealt with users, and more than likely I made the same mistake in the
pg_hba.conf file but never noticed. It’s the kind of thing I do all the time!