Configuring Postgresql and Dropdb

Seems like an on-going nightmare. I am configuring a new Ubuntu 14.04 system and having troubles with getting passwords from .pgpass. Here are the fundamentals:

  • .pgpass should be in the users home dir with permissions 0600 (rw). Lesser permissions will cause postgresql to ignore this file
  • /etc/postgresql/9.3/main/pg_hba.conf is set to md5

Let the fun begin. I have a this line in my .pgpass file:

localhost:5432:my_db:my_user:my_password

When I run:

psql -U my_user -d my_db

I am able to launch psql. When I run the psql command “\conninfo”, I get:

You are connected to database "my_db" as user "my_user" via socket in "/var/run/postgresql" at port "5432"

But when I exit psql and run:

dropdb my_db -U my_user -w

I get:

dropdb: could not connect to database template1: fe_sendauth: no password supplied

WTF? Then I saw this statement on Stackoverflow:

recall that creatdb and dropdb aren’t really acting on “myprojectdb” despite the fact that you may be asking them to create and drop that db

Maybe my attempts at tightening security are the problem. When I changed .pgadmin to:

localhost:5432:*:my_user:my_password

Then dropdb worked.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s