My settings:
- Ubuntu 9.10
- PostgreSQL 8.4.2
I created a new user(jdoe) under my local PostgreSQL with the following command in the psql shell:
CREATE USER jdoe WITH PASSWORD 'password';
I also have created a new database called mydb
. My goal is to give user jdoe
full access to database mydb
. I did a few things through the GUI pgAdmin III and after running \l
in the psql shell, I get the following output:
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+---------------
mydb | jdoe | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =CTc/mydb
: hmart=CTc/mydb
postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 |
Showing my list of users(\du
), I get:
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
jdoe | | {}
postgres | Superuser | {}
: Create role
: Create DB
I can't really tell from the above if my goal has been achieved, but is there a way to test if the user jdoe
can communicate with the database mydb
? I'm testing it though Django's settings:
DATABASE_ENGINE = 'postgresql_psycopg2'
DATABASE_NAME = 'mydb'
DATABASE_USER = 'jdoe'
DATABASE_PASSWORD = 'password'
DATABASE_HOST = ''
DATABASE_PORT = ''
but I'm getting the following error after a python manage.py syncdb
:
psycopg2.OperationalError: FATAL: Ident authentication failed for user "jdoe"
I did all the above on Win XP with the latest PostgreSQL installer and everything work flawlessly with Django. I'm not sure what I'm missing under Ubuntu.
You probably have set it up in pg_hba.conf so that local socket connections must use ident authorization, while tcp connections to localhost are trusted. Read the documentation on pg_hba.conf for your version, eg 8.4 is here, and you can configure it correctly for your situation.
This is weird, setting
DATABASE_HOST
tolocalhost
fix the issue: