Postgresql cannot change to root with -u shortcut

Question

Recently updated from Postgresql 9.1 to 9.3.

Everything works fine, but I noticed now when I type in:

sudo -u postgres psql

I am getting hit with a permission denied error for changing dir to root.

"Could not change directory to /home/root.

However, when I use:

sudo su - postgres 
psql

It accesses it fine. How can I fix this?

score 22 · Answer 1 · answered Mar 04 '16 at 14:08

22

change directory to someplace that postgres has access to:

cd /tmp
sudo -u postgres psql

answered Mar 04 '16 at 14:08

Kirk Roybal

Why then does su work? It should have access to the dir, or I should get the same error, should I not? – Johnathan Mar 04 '16 at 19:03
I don't know. I went through this post to figure it out http://stackoverflow.com/questions/4598001/how-do-you-find-the-original-user-through-multiple-sudo-and-su-commands and i couldn't find a difference that made sense. – Kirk Roybal Mar 04 '16 at 21:12
See [my answer](https://stackoverflow.com/questions/35782366/postgresql-cannot-change-to-root-with-u-shortcut/45145825#45145825). `sudo su postgres` will give you similar errors. – DylanYoung Jul 17 '17 at 13:39

score 20 · Answer 2 · answered Jul 17 '17 at 13:39

Try this:

sudo -i -u postgres psql

This accomplishes (almost) the same thing as your

sudo su - postgres

The - in the above indicates that you want to use the postgres account's environment. If you remove the -, it will fail similarly to sudo -u

The -i indicates that you want to run the postgres account's login shell (hence cding to their home directory).

score 0 · Answer 3 · answered Jan 11 '19 at 10:30

For me this did the trick (or you'll get a could not change directory to "/root": Permission denied), pay attention to quotes (')

sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)_someTable.sql'

Note the -Hiufor sudo, or use su - postgres

you can also put that in a cronjob for root with crontab -e

3 Answers3