2

I'm setting up a cron job to do some routine maintenance for a postgresql database. The script is as follows:

#!/bin/sh

dbname="dbname"
username="postgres"
psql -d $dbname -U $username << EOF
delete from links_link where submitted_on > now() - interval'4 days';
EOF

However when run via crontab, this fails because I have not provided the password for the user postgres.

My question is: how do I include the password for the user postgres in the shell script? That will get me up and running fast. Secondly, how do I improve security by not having to explicitly add a password in my script? If anyone's set up this kind of a thing before, their experience is most welcome. Thanks in advance.

Hassan Baig
  • 2,325
  • 12
  • 29
  • 48

2 Answers2

3

Take a look at a similar post here: Postgresql: Scripting psql execution with password

The overall accepted answer (which may or may not work for your version of Postgres) is to use the PGPASSWORD environment variable like so:

PGPASSWORD=[your password] psql -Umyuser < myscript.sql

EDIT: Seems as though you tried and this didn't work. Have you looked at the link I sent? There are a couple of stated options, check on the resources for configuring a pg_hba.conf file: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html, or look at the .pgpass file: https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html

If you try using the pg_hba.conf, it would appear that using the auth-method of trust with proper configuration of the user you would use for your script would be allowed access as stated by the following:

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.

Community
  • 1
Andrew
  • 2,142
  • 2
  • 19
  • 25
  • I'm aware of that answer. I've already tried `PGPASSWORD=1234 psql -d $dbname -U $username << EOF` but to no avail. My postgresql version is 9.3.10. – Hassan Baig Jun 24 '16 at 13:20
  • Well ideally, I don't want to set `auth-method` to `trust`. If there's a remotely exploitable vulnerability and someone manages to get shell access to an unprivileged user, they can connect to postgresql and take over. So while this works to get it up and running, it fails on my second count, i.e. some sense of security has to remain intact. – Hassan Baig Jun 25 '16 at 04:26
  • 1
    Did you try the .pgpass file? It might be a bit more easy to secure. You could also look at using the certificate auth method too; likely will take more configuration, but would be harder to access remotely or locally (assuming unprivileged access) – Andrew Jun 25 '16 at 04:47
2

You may want to switch to (or allow) ident auth, then create a local system user with that same name. Then you can just run that script as that user, and it will log you in to postgres with that same user.

https://www.postgresql.org/docs/9.0/static/auth-methods.html
https://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html

So something like this in pg_hba.conf:

local  dbname someuser ident

Then restart postgres, and you can su - someuser and run the script. That is a little more secure than 'local all all trust', which is the default and will allow all local users to connect unconditionally to any database.

lsd
  • 1,673
  • 10
  • 10
  • Apparently I can't have links in pre blocks. I'll have to figure out how to do preformatted links....maybe. – lsd Jun 25 '16 at 13:50