4

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?

8
  • 1
    have you tried this stackoverflow.com/a/257666/2213874 or this superuser.com/a/232258/213081 ... otherwise maybe a symlink of pgsql to /bin Sep 26, 2013 at 13:03
  • You should be able to use sudo -E -u postgres psql -l as long as your $PATH has /usr/pgsql-9.2/bin/ in it.
    – krowe
    Sep 26, 2013 at 13:21
  • @krowe /usr/pgsql-9.2/bin (no trailing slash) is in my path, but sudo -E -u postgres psql -l produces the same error: sudo: psql: command not found. Sep 26, 2013 at 13:36
  • @DrewKhoury Awesome. Your second link had the answer. I used it to write my own answer. Thanks! Sep 26, 2013 at 14:23
  • Just for fun do you mind trying: sudo -E -U postgres psql -l ? I think I had a mistake in my original answer.
    – krowe
    Sep 26, 2013 at 14:38

2 Answers 2

7

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.

3
  • BTW, the command: sudo -l -U postgres is a better way to see the sudo environment for a user. Be sure to use a big U.
    – krowe
    Sep 26, 2013 at 14:36
  • @krowe Here that produces the message: User postgres is not allowed to run sudo on <hostname>.. Sep 26, 2013 at 15:26
  • That could be gotten rid of if you setup postgres as a sudo user. But that isn't really any easier than your solution as it is.
    – krowe
    Sep 26, 2013 at 17:01
0

a small workaround - if you have no problem with executing more commands, you can switch user

sudo su postgres

and then

psql -l

should work.

1
  • This works, but I need a one-liner. As a one-liner this becomes sudo su postgres --command="psql -l", which works, but doesn't solve the fundamental problem. Thanks for the tip all the same! Sep 26, 2013 at 14:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .