I have some data I found on the Office for National Statistics (UK) website which I'm trying to port from a csv file into a PostgreSQL database to perform queries, data manipulations and create visualisations. I'm using a program I found called pgfutter to try to make this process simpler (I anticipate having to do this a number of times for different csv data).

However, as I've tried to run the pgfutter program when logged in as root or from my user directory (./pgfutter csv downloadedfile.csv) I've gotten this error:

pq: password authentication failed for user "postgres"

Ok, I thought, so I switched to the postgres user account, and made sure to enable postgres in the sudoers file. When running as the postgres user the command failed with the same error, and there was no option given to input a password for the postgres user.

So... I dropped into the postgres user account and instead ran sudo ./pgfutter csv downloadedfile.csv from there. This at least prompted for a password, but resulted in this error:

postgres is not in the sudoers file.  This incident will be reported.

So, I dropped back into root and opened up /etc/sudoers and edited in this line:

username ALL=(ALL) ALL

Which resulted in the same error. I even tried changing this to:

%username ALL=(ALL) ALL

Which results in the same error.

I was under the understanding that adding username ALL=(ALL) ALL to the sudoers file would allow all users access to sudo (I also have the %sudo ALL=(ALL:ALL) ALL in the sudoers file) but this does not appear to be the case at present.

How do I give the postgres user sudo permissions and hence allow the program to correctly execute and port the data over to my database?

  • Database authentication is a whole different thing from system authentication - you should probably focus on configuring pgfutter to use the credentials that you normally use to access your database - rather than trying to strongarm things using sudo. Did you read the Database Connection section of the usage information in the README.md file? – steeldriver Aug 4 '17 at 13:54
  • I've looked through the Database Connections section but this only lists "Database connection details can be provided via environment variables or as separate flags." and some aliases, but does not say which environment variables it means. There does not appear to be any space for environment variables in the pgfutter code on the Github. I have the postgres Linux user password set to the same one as the password for database I'm using, and have this setup in my backend API (Elixir) but I don't see that this could be relevant. You think this is something to do with the way my database is setup? – Peter David Carter Aug 4 '17 at 14:03
  • Do you usually connect to the database as user postgres? does that require a password, or not? Have you tried setting the environment variables as described e.g. DB_PASS=yourpostgrespassword ./pgfutter csv downloadedfile.csv (or running export DB_PASS=yourpostgrespassword before executing the pgfutter command?) – steeldriver Aug 4 '17 at 14:07
  • I usually connect to postgres via su - postgres followed by psql -U postgres. Neither of these require a password when the action is performed from root, though there is a password set on the postgres user account. I don't know which environment variables you mean. Do you mean global Ubuntu environment variables of some sort? – Peter David Carter Aug 4 '17 at 14:10
  • @steeldriver I tried running the commands you suggested and they don't work :/. – Peter David Carter Aug 4 '17 at 14:23

Your Answer

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

Browse other questions tagged or ask your own question.