35

PostgreSQL users peer authentication on unix sockets by default, where the unix user must be the same as the PostgreSQL user. So people frequently use su or sudo to become the postgres superuser.

I often see people using constructs like:

sudo su - postgres

rather than

sudo -u postgres -i

and I'm wondering why. Similarly, I've seen:

sudo su - postgres -c psql

instead of

sudo -u postgres psql

Without the leading sudo the su versions would make some sense if you were on an old platform without sudo. But why on a less than prehisoric UNIX or Linux would you use sudo su ?

38

Forget sudo su

There is no benefit to using sudo su, it's an anachronistic habit from when people were used to using su. People started tacking sudo in front when Linux distros stopped setting a root password and made sudo the only way to access the root account. Rather than change their habits, they just used sudo su. (I was one of them until relatively recently when using boxes with sudoers configs forced me to change my habit).

Use sudo -u

For a login shell, sudo -u postgres -i is preferable to sudo su - postgres. It doesn't require that the user have root access in /etc/sudoers, they only need the right to become user postgres. It also lets you enforce better access controls.

For command execution

sudo -u postgres psql -c "SELECT 1"

is superior to the alternative:

sudo su - postgres -c "psql -c \"SELECT 1\""

in that you don't have to double-escape quotes and other shell metacharacters as well as the other security advantages of not needing root. You'll probably accidentally land up writing:

sudo su - postgres -c psql -c "SELECT 1"

sometimes, which won't work properly.

Finally, it's way easier to set environment variables via sudo, e.g.

sudo PATH=/usr/pgsql-9.3/bin:$PATH -u postgres /usr/pgsql-9.3/bin/initdb -D /var/lib/pgsql/testcluster

than via su. (Here, the PATH setting is required so that initdb can find the correct postgres executable).

So. Forget the su command exists. You don't need it anymore. To break the habit, alias it to something that'll print an error. (Some init and package setup scripts still use su so you can't remove it, though).

See also:

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.