0

Context

I have a PostgreSQL server (localhost:5432) which holds a database called my_database.

I have created long time ago, a simple role which owns this database; my_user, different from the postgres role;

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 my_user   | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This manually created role does not have an OS equivalent user, like postgres does.

I have a bash script to reinitialize my_database with a set of schemas and tables (from the create_db.sql file);

#!/bin/bash
set -e
dropdb -U my_user my_database || true
createdb -U my_user my_database --owner=my_user
psql -U my_user -d my_database -h localhost -v ON_ERROR_STOP=1 -a -f create_db.sql

Issue

Since a few days, this script is asking me 3 times the password for my_user. I guess one time per line.
Before that, I was able to run it without any prompt for any password. That was really cool.

The only thing I changed in between was this;
I usually connect to this server using pgadmin3 with the postgres user to checkout what's inside my tables.
In pgadmin3, I once set up this server credentials to my_user and its password to checkout some differences with the postgres login.
Then, always in pgadmin3, I switched back the credentials to postgres as they were before.

Question

I cannot understand why this would have broken something which had since then made my script asking for password...
It may be related to something other, but I really don't know what?

I also tried to set up a .pgpass file with this line;

localhost:5432:my_database:my_user:my_user_password

and changing its access to 0600 as stated in the documentation.

But it doesn't seem to be recognized; the bash script is still asking 3 times for my_user password.
And, as I (the ubuntu normal OS user, which is not the same as the role my_user which owns my_database) owns this file , I wonder if I should set up permissions to this file to the postgres OS user instead?

Anyway, this .pgpass file was empty before, when I was able to reset my_database without any password prompt. So if I can recover the previous behavior it would be nice, except if this is not a good practice.

Any clue would be appreciated as I'm not much used to database management.

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

EDIT

I just noticed that if I set the .pgpass file with this content;

*:*:*:my_user:my_user_password

it works (it stops asking for a password).

But with the database name;

*:*:my_database:my_user:my_user_password

it doesn't (it keeps asking for a password).

5
  • 1
    dropdb and createdb do not connect to the database passed on the command line, so that would be why * or a specific dbname in .pgpass have different results. See the --maintenance-db option in their manual pages. – Daniel Vérité Mar 18 '20 at 18:52
  • You seems on a right track; the single last psql command is actually working with md5 auth and the complete full line in the ~/.pgpass file. But this; dropdb -U my_user --maintenance-db=my_database my_database || true raises an error; dropdb: database removal failed: ERROR: cannot drop the currently open database (nothing else is connected to the db). – s.k Mar 18 '20 at 19:05
  • 1
    In .pgpass you can allow connections to the postgres database and do not specify --maintenance-db in your dropdb and createdb invocations. That would let your script pass without asking for a password. – Daniel Vérité Mar 18 '20 at 19:30
  • Sounds good... But what to specify in the .pgpass file the case the postgres role is actually using a peer auth (based on unix socket)? Setting for example *:*:*:postgres:* doesn't work; it still asks for a password for the user my_user. – s.k Mar 18 '20 at 19:37
  • Then you need to refine the rules in pg_hba.conf first. Please ask a new specific question if you need an elaborate answer. – Daniel Vérité Mar 19 '20 at 12:57
0

You edited away the problem.

In your comments you say that the database is really projATX_db. At least that is what you think. But PostgreSQL translated all unquoted text to lower case, so when you run

CREATE DATABASE projATX_db;

the database is really called projatx_db

Now the entries in the password file are not subject to this lower case conversion (that is specific to SQL), so projATX_db in the password file does not match your database.

8
  • PGHOST is and was never set. Local auth is set to md5 in pg_hba.conf. I'd like to keep it so (it's working when setting it to trust of course). My .pgpass is my home directory but it seems to me it is not read by postgres, even if I export PGPASSFILE=~/.pgpass before running my script. – s.k Mar 18 '20 at 18:14
  • As I wrote in my answer, the contents of the file are wrong. The correct host is not localhost. – Laurenz Albe Mar 18 '20 at 18:41
  • Sorry, I didn't precise that I used your proposed new content for the ~/.pgpass file. – s.k Mar 18 '20 at 18:49
  • From your updates it looks like you made an error with the database name. Is the database really called my_database? If not, you should show the real name. – Laurenz Albe Mar 18 '20 at 19:24
  • Hmm, i cannot see (I may have missed something important) what's wrong with the fact that the name in my edit is the same than in the aforementioned commands, such as dropdb and createdb. But indeed, the database is name after a project, such as projATX_db. I may then search and replace all occurrences of my_database with this specific name in the whole text of this page. Would it change something? – s.k Mar 19 '20 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.