Why doesn't sudo know where psql is?

The problem

The CKAN installation documentation shows you how to list the installed PostgreSQL databases.

The command looks like this:

sudo -u postgres psql -l

When I try that in my shell, I get an error:

$ sudo -u postgres psql -l
sudo: psql: command not found

The workaround

Daniel2d2art on the CentOS forum worked around the problem by fully qualifying the path to psql.

My psql lives in the directory /usr/pgsql-9.2/bin, so my workaround now looks like this:

sudo -u postgres /usr/pgsql-9.2/bin/psql -l

When I try that in my shell, it works:

$ sudo -u postgres /usr/pgsql-9.2/bin/psql -l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 postgis_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
(4 rows)

How do I fix it properly?

I shouldn't have to fully qualify the path, right?

The postgres user already has psql in its path:

$ sudo -u postgres echo $PATH
/usr/pgsql-9.2/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

How do I fix this properly?

Why sudo ignores your path

sudo doesn't use your user's path or the postgres user's path. sudo has its own path, defined by the the secure_path variable in the file /etc/sudoers.

The output of echo $PATH is misleading in this case. To see the path that sudo really uses, use printenv PATH instead. In my case, the output looked like this:

$ sudo -u postgres printenv PATH
/sbin:/bin:/usr/sbin:/usr/bin

The output doesn't contain /usr/pgsql-9.2/bin, where psql lives, so it's not in sudo's path either.

To fix the problem you can add where psql lives to the secure_path variable.

How to tell sudo where psql lives

Use sudo visudo to open /etc/sudoers in vi.

The file should contain a line like this:

Defaults    secure_path = /sbin:/bin:/usr/sbin:/usr/bin

This line sets the path for sudo. The part after the equals sign is the same as the output of the previous printenv PATH example.

Replace it with something like this:

Defaults    secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/pgsql-9.2/bin

The replacement appends /usr/pgsql-9.2/bin to the path list. The path list separator is a colon (:).

Save and close the file.

To check that it worked, try the printenv PATH command again:

$ sudo -u postgres printenv PATH
/sbin:/bin:/usr/sbin:/usr/bin:/usr/pgsql-9.2/bin

Looks good!

Now try the psql -l command:

$ sudo -u postgres psql -l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 postgis_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
(4 rows)

It works!

Thanks to Drew Khoury who pointed me to a solution for a similar problem on Super User.