Problems connecting to PostgreSQL
Chris Warrick
kwpolska at gmail.com
Sun Nov 8 03:36:38 EST 2015
On 8 November 2015 at 00:40, Cecil Westerhof <Cecil at 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 — and it comes from sudo,
postgres doesn’t 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 = psycopg2.connect(database = postgres_database, user = 'stressTest')
> I get:
> psycopg2.OperationalError: FATAL: Peer authentication failed for user "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=# ALTER ROLE stressTest WITH PASSWORD 'swordfish';
postgres=# \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 = psycopg2.connect(database='stressTest', user='stressTest',
password='swordfish', host='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)
--
Chris Warrick <https://chriswarrick.com/>
PGP: 5EAAEA16
More information about the Python-list
mailing list