Linux – How to get rid of “Could not connect to database” with Linux and libnss-pgsql2

debian-squeezelinuxnsspostgresql

I use libnss-pgsql2 in order to have virtual system users stored in a PostgreSQL database.
The virtual users in the database work just fine. They can log in. I can see their uid, gid, groups via the 'id' command. Example:

# id backup001
uid=10001(backup001) gid=10001(backup001) groups=10001(backup001)

However, on systems that I use libnss, I frequently get this error:

Could not connect to database

It happens, for instance, often with cron-jobs. I have one cron-job that runs every hour that dumps the postgresql databases to a backup. The contrab is this:

04 *  *   *   *     postgres umask 077 && /usr/bin/pg_dumpall | gzip > ~postgres/backup/postgresql-complete-dump-$(date +\%H).sql.gz

This job always produces the error. Thus, flooding me with an e-mail every hour.

My setup is pretty simple:
The table layout I use to store the users is available here:
http://p.adora.dk/P2486.html

I use Debian Squeeze on the server.

Relevant config files are:
nsswitch.conf : http://p.adora.dk/P2489.html

(description: use "normal" system users in /etc/passwd and /etc/shadow, however, if the user is NOT found, then proceed with a lookup via pgsql)

nss-pgsql.conf : http://p.adora.dk/P2487.html

(description: contains the SQL queries that are used to look up various information that normally is found in /etc/passwd and /etc/group)

nss-pgsql-root.conf : http://p.adora.dk/P2488.html

(description: contains the SQL queries that are used to lookup confidential info that is normally found in /etc/shadow)

Things that I have done to debug this:

Verified that the connection strings in both nss-pgsql.conf and nss-pgsql-root.conf work as intended.
Verified that the timeout does not occur. I.e. the error is echoed immediately and not after 300 seconds. Also, this happens on a server that does not do anything – so the connection should be established without delay — I have verified that it does.

I really hope you can help me fix this error.

Update 2012-08-22:

I tried doing an strace on psql. The relevant part of the strace is in the bottom of this paste:
http://paste.adora.dk/P2492.html

I notice that it tries opening /etc/nss-pgsql-root.conf and get EACCESS, however, I do not believe this should be a problem. This file should be readable by root only as it corresponds to /etc/shadow which is also only readable by root.

25341 access("/etc/ld.so.nohwcap", F_OK) = -1 ENOENT (No such file or directory)
25341 open("/usr/lib/libgpg-error.so.0", O_RDONLY) = 4
25341 read(4, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0 \6\0\0004\0\0\0"..., 512) = 512
25341 fstat64(4, {st_mode=S_IFREG|0644, st_size=11540, ...}) = 0
25341 mmap2(NULL, 14512, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 4, 0) = 0xb6f6c000
25341 mmap2(0xb6f6f000, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 4, 0x2) = 0xb6f6f000
25341 close(4)                          = 0
25341 mprotect(0xb70bf000, 4096, PROT_READ) = 0
25341 mprotect(0xb73d8000, 4096, PROT_READ) = 0
25341 munmap(0xb7414000, 40018)         = 0
25341 open("/etc/nss-pgsql-root.conf", O_RDONLY) = -1 EACCES (Permission denied)
25341 write(2, "\nCould not connect to database\n", 31) = 31

It is possible that this is a bug in libnss-pgsql…. What do you think?

Update 2012-08-22:

OK. I dug up this five year old bug report:
http://pgfoundry.org/tracker/index.php?func=detail&aid=1010197&group_id=1000039&atid=234

It seems that this behavior is actually a bug. A patch has been provided, however, there is no activity on the bug report. Maybe the project is abandoned. I certainly hope not 🙁

Best Answer

I think the answer to your problem is in this this line:

open("/etc/nss-pgsql-root.conf", O_RDONLY) = -1 EACCES (Permission denied)

Try relaxing permissions on this file to be readable by "group" and "other" and see if that solves the problem.

You are wrong that the file corresponds to /etc/shadow. It corresponds to /etc/password, which is readable by "group" and "other". Your PostgreSQL database and tables used for authentication correspond to /etc/shadow.

It can't connect to the database because it can't read the database access credentials from this file.

How to fix all of your crontab related woes/problems (Linux)

This is a community wiki, if you notice anything incorrect with this answer or have additional information then please edit it.

First, basic terminology:

cron(8) is the daemon that executes scheduled commands.
crontab(1) is the program used to modify user crontab(5) files.
crontab(5) is a per user file that contains instructions for cron(8).

Next, education about cron:

Every user on a system may have their own crontab file. The location of the root and user crontab files are system dependant but they are generally below /var/spool/cron.

There is a system-wide /etc/crontab file, the /etc/cron.d directory may contain crontab fragments which are also read and actioned by cron. Some Linux distributions (eg, Red Hat) also have /etc/cron.{hourly,daily,weekly,monthly} which are directories, scripts inside which will be executed every hour/day/week/month, with root privilege.

root can always use the crontab command; regular users may or may not be granted access. When you edit the crontab file with the command crontab -e and save it, crond checks it for basic validity but does not guarantee your crontab file is correctly formed. There is a file called cron.deny which will specify which users cannot use cron. The cron.deny file location is system dependent and can be deleted which will allow all users to use cron.

If the computer is not powered on or crond daemon is not running, and the date/time for a command to run has passed, crond will not catchup and run past queries.

crontab particulars, how to formulate a command:

A crontab command is represented by a single line. You cannot use \ to extend a command over multiple lines. The hash (#) sign represents a comment which means anything on that line is ignored by cron. Leading whitespace and blank lines are ignored.

Be VERY careful when using the percent (%) sign in your command. Unless they are escaped \% they are converted into newlines and everything after the first non-escaped % is passed to your command on stdin.

There are two formats for crontab files:

User crontabs

 # Example of job definition:
 # .---------------- minute (0 - 59)
 # |  .------------- hour (0 - 23)
 # |  |  .---------- day of month (1 - 31)
 # |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
 # |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7)
 # |  |  |  |  |
 # *  *  *  *  *   command to be executed

System wide /etc/crontab and /etc/cron.d fragments

 # Example of job definition:
 # .---------------- minute (0 - 59)
 # |  .------------- hour (0 - 23)
 # |  |  .---------- day of month (1 - 31)
 # |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
 # |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7)
 # |  |  |  |  |
 # *  *  *  *  * user-name  command to be executed

Notice that the latter requires a user-name. The command will be run as the named user.

The first 5 fields of the line represent the time(s) when the command should be run. You can use numbers or where applicable day/month names in the time specification.

The fields are separated by spaces or tabs.
A comma (,) is used to specify a list e.g 1,4,6,8 which means run at 1,4,6,8.
Ranges are specified with a dash (-) and may be combined with lists e.g. 1-3,9-12 which means between 1 and 3 then between 9 and 12.
The / character can be used to introduce a step e.g. 2/5 which means starting at 2 then every 5 (2,7,12,17,22...). They do not wrap past the end.
An asterisk (*) in a field signifies the entire range for that field (e.g. 0-59 for the minute field).
Ranges and steps can be combined e.g. */2 signifies starting at the minimum for the relevant field then every 2 e.g. 0 for minutes( 0,2...58), 1 for months (1,3 ... 11) etc.

Debugging cron commands

Check the mail!

By default cron will mail any output from the command to the user it is running the command as. If there is no output there will be no mail. If you want cron to send mail to a different account then you can set the MAILTO environment variable in the crontab file e.g.

MAILTO=user@somehost.tld
1 2 * * * /path/to/your/command

Capture the output yourself

You can redirect stdout and stderr to a file. The exact syntax for capturing output may vary depending on what shell cron is using. Here are two examples which save all output to a file at /tmp/mycommand.log:

1 2 * * * /path/to/your/command &>/tmp/mycommand.log
1 2 * * * /path/to/your/command >/tmp/mycommand.log 2>&1

Look at the logs

Cron logs its actions via syslog, which (depending on your setup) often go to /var/log/cron or /var/log/syslog.

If required you can filter the cron statements with e.g.

grep CRON /var/log/syslog

Now that we've gone over the basics of cron, where the files are and how to use them let's look at some common problems.

Check that cron is running

If cron isn't running then your commands won't be scheduled ...

ps -ef | grep cron | grep -v grep

should get you something like

root    1224   1  0 Nov16 ?    00:00:03 cron

root    2018   1  0 Nov14 ?    00:00:06 crond

If not restart it

/sbin/service cron start

/sbin/service crond start

There may be other methods; use what your distro provides.

cron runs your command in a restricted environment.

What environment variables are available is likely to be very limited. Typically, you'll only get a few variables defined, such as $LOGNAME, $HOME, and $PATH.

Of particular note is the PATH is restricted to /bin:/usr/bin. The vast majority of "my cron script doesn't work" problems are caused by this restrictive path. If your command is in a different location you can solve this in a couple of ways:

Provide the full path to your command.
```
 1 2 * * * /path/to/your/command
```

Provide a suitable PATH in the crontab file

 PATH=/bin:/usr/bin:/path/to/something/else
 1 2 * * * command

If your command requires other environment variables you can define them in the crontab file too.

cron runs your command with cwd == $HOME

Regardless of where the program you execute resides on the filesystem, the current working directory of the program when cron runs it will be the user's home directory. If you access files in your program, you'll need to take this into account if you use relative paths, or (preferably) just use fully-qualified paths everywhere, and save everyone a whole lot of confusion.

The last command in my crontab doesn't run

Cron generally requires that commands are terminated with a new line. Edit your crontab; go to the end of the line which contains the last command and insert a new line (press enter).

Check the crontab format

You can't use a user crontab formatted crontab for /etc/crontab or the fragments in /etc/cron.d and vice versa. A user formatted crontab does not include a username in the 6th position of a row, while a system formatted crontab includes the username and runs the command as that user.

I put a file in /etc/cron.{hourly,daily,weekly,monthly} and it doesn't run

Check that the filename doesn't have an extension see run-parts
Ensure the file has execute permissions.
Tell the system what to use when executing your script (eg. put #!/bin/sh at top)

Cron date related bugs

If your date is recently changed by a user or system update, timezone or other, then crontab will start behaving erratically and exhibit bizarre bugs, sometimes working, sometimes not. This is crontab's attempt to try to "do what you want" when the time changes out from underneath it. The "minute" field will become ineffective after the hour is changed. In this scenario, only asterisks would be accepted. Restart cron and try it again without connecting to the internet (so the date doesn't have a chance to reset to one of the time servers).

Percent signs, again

To emphasise the advice about percent signs, here's an example of what cron does with them:

# cron entry
* * * * * cat >$HOME/cron.out%foo%bar%baz

will create the ~/cron.out file containing the 3 lines

foo
bar
baz

This is particularly intrusive when using the date command. Be sure to escape the percent signs

* * * * * /path/to/command --day "$(date "+\%Y\%m\%d")"

How to use `sudo` in cron jobs

when running as a non-root user,

crontab -e

will open the user's crontab, while

sudo crontab -e

will open the root user's crontab. It's not recommended to run sudo commands in a cron job, so if you're trying to run a sudo command in a user's cron, try moving that command to root's cron and remove sudo from the command.