Path: csiph.com!fu-berlin.de!uni-berlin.de!not-for-mail
From: Chris Warrick <kwpolska@gmail.com>
Newsgroups: comp.lang.python
Subject: Re: Problems connecting to PostgreSQL
Date: Sun, 8 Nov 2015 09:36:38 +0100
Lines: 59
Message-ID: <mailman.119.1446971807.16136.python-list@python.org>
References: <87wptt5qg4.fsf@Equus.decebal.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <87wptt5qg4.fsf@Equus.decebal.nl>
Precedence: list
Xref: csiph.com comp.lang.python:98428

On 8 November 2015 at 00:40, Cecil Westerhof <Cecil@decebal.nl> wrote:
> I followed http://zetcode.com/db/postgresqlpythontutorial/.
>
> I used:
>     sudo -u postgres createuser stressTest
> this create the role, but also gave:
>     could not change directory to "/root": Permission denied
> and I did not get the questions.

This is not an error, just a warning =E2=80=94 and it comes from sudo,
postgres doesn=E2=80=99t care. To silence it, you need to work from a
different directory than /root.  The commands actually worked just
fine.

> Then I used:
>     sudo -u postgres createdb stressTest -O stressTest
> This gave also:
>     could not change directory to "/root": Permission denied
>
> The database is created, but when I execute:
>     conn =3D psycopg2.connect(database =3D postgres_database, user =3D 's=
tressTest')
> I get:
>     psycopg2.OperationalError: FATAL:  Peer authentication failed for use=
r "stressTest"
>
> What do I need to do to get things working?

You need to configure your PostgreSQL database to use md5
authentication, and set a password for your user.

    # cd /
    # sudo -u postgres psql
    postgres=3D# ALTER ROLE stressTest WITH PASSWORD 'swordfish';
    postgres=3D# \q
    # vim /var/lib/postgres/data/pg_hba.conf

Change host settings to look like this:
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5

Then you can connect using:

    conn =3D psycopg2.connect(database=3D'stressTest', user=3D'stressTest',
password=3D'swordfish', host=3D'localhost')

Documentation:
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
http://www.postgresql.org/docs/current/static/auth-methods.html
http://www.postgresql.org/docs/current/static/sql-alterrole.html

(basically, the default peer authentication checks your Unix user name
to see if it matches 'stressTest', and fails)

--=20
Chris Warrick <https://chriswarrick.com/>
PGP: 5EAAEA16