Bug 233820 - databases/postgresql96-server: holds a pty unnecessarily
Summary: databases/postgresql96-server: holds a pty unnecessarily
Status: Closed Overcome By Events
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Many People
Assignee: pgsql
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-12-06 03:58 UTC by Jeremy Chadwick
Modified: 2022-03-14 21:01 UTC (History)
1 user (show)

See Also:
bugzilla: maintainer-feedback? (pgsql)


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jeremy Chadwick 2018-12-06 03:58:43 UTC
It appears that databases/postgresql96-server's rc.d script starts pgsql directly (via su(1)), rather than using daemon(8).  The result can be a wasted pty due to the lack of redirecting stdout and stderr prior to process start, if the daemon is started from an interactive shell directly.  That pty also continues to hold the username/uid of whomever started postgres itself.

Issue likely affects other versions of databases/postgresql*-server.

Solution:

daemon(8) should be used instead of su(1), particularly with the -f flag, when starting the daemon. There are other example ports which use daemon(8) if the maintainer is unfamiliar with it; I believe databases/mysql*-server is a good example:

https://svnweb.freebsd.org/ports/head/databases/mysql80-server/files/mysql-server.in?revision=466508&view=markup

You may also find additional flags (such as -u to setuid() in advance) useful.

Evidence:

Confirmed on stable/11 (r339174).  Note that fd 1 (stdout) and fd 2 (stderr) are still being held open and are not /dev/null; pts/0 is being "held" by postgresql.

$ w
 7:43PM  up 51 days, 19:17, 1 user, load averages: 2.08, 2.10, 2.08
USER       TTY      FROM                                      LOGIN@  IDLE WHAT
jdc        pts/0    koitsu                                    7:43PM     - w
$ sudo service postgresql stop
$ sudo service postgresql start
LOG:  ending log output to stderr
HINT:  Future log output will go to log destination "syslog".
$ ls -l /dev/pts
total 0
crw--w----    1 jdc       tty       0x61 Dec  5 19:44 0
$ logout
{closes session}
{open new session}
$ w
 7:44PM  up 51 days, 19:18, 1 user, load averages: 2.02, 2.08, 2.07
USER       TTY      FROM                                      LOGIN@  IDLE WHAT
jdc        pts/1    XXXXXX                                    7:44PM     - w
$ ls -l /dev/pts
total 0
crw--w----    1 jdc       tty       0x61 Dec  5 19:44 0
crw--w----    1 jdc       tty       0x63 Dec  5 19:44 1
$ sudo pgrep -a -S -t 0
66606
$ ps -auxw | grep 66606
postgres 66606   0.0  0.3 167640  28648  0- S    19:43        0:00.03 /usr/local/bin/postgres -D /postgresql
jdc      66709   0.0  0.0   6640   2476  1  R+   19:45        0:00.00 grep 66606
$ sudo fstat -p 66606
USER     CMD          PID   FD MOUNT      INUM MODE         SZ|DV R/W
postgres postgres   66606 text /        12771700 -rwxr-xr-x  6285760  r
postgres postgres   66606   wd /postgresql      4 drwx------      29  r
postgres postgres   66606 root /             2 drwxr-xr-x     512  r
postgres postgres   66606    0 /dev         28 crw-rw-rw-    null  r
postgres postgres   66606    1 -         -         bad    -
postgres postgres   66606    2 -         -         bad    -
postgres postgres   66606    3* internet stream tcp fffff800214fe000
postgres postgres   66606    4* local stream fffff800402bc4b0
postgres postgres   66606    5* pipe fffff800089125f0 <-> fffff80008912758      0 rw
postgres postgres   66606    6* pipe fffff80008912758 <-> fffff800089125f0      0 rw
postgres postgres   66606    7* local dgram fffff80011d4a2d0 <-> fffff80011cd1780
postgres postgres   66606    8* internet dgram udp fffff800119b6910

Other irrelevant details:

$ grep postgresql /etc/rc.conf
postgresql_enable="yes"
postgresql_data="/postgresql"
Comment 1 Rene Ladan freebsd_committer freebsd_triage 2022-03-11 12:01:28 UTC
Removing expired port, is this still a problem with contemporary versions of PostgreSQL?
Comment 2 Jeremy Chadwick 2022-03-14 21:01:52 UTC
Quite rude to close this ticket without doing the proper verification, especially given that all details incl. commands are provided.  This should have been looked into over 3 years ago.  Anyway...

Here is my analysis as of this writing, using FreeBSD 13.1-PRERELEASE as a test system and the latest pkg/ports.  It turns out this is/was a PostgreSQL problem and not a FreeBSD problem:

databases/postgresql10-server (10.20): problem still exists
databases/postgresql11-server (11.15): problem still exists
databases/postgresql12-server (12.10): problem no longer present
databases/postgresql13-server (13.6):  problem no longer present
databases/postgresql14-server (14.2):  problem no longer present

Below numbers refer to PostgreSQL versions and not FreeBSD versions.

I tried to go through the PostgreSQL changelog to find out what changed.  Since 11.15 is the latest 11.x version, we start with 12.0 and work our way up to 12.10: https://www.postgresql.org/docs/release/ . Things we look for are the terms "stdout", "stderr", "pts", "pty", "tty", and "freebsd" (in case it's FreeBSD-specific).

Sadly, we find nothing relevant.  We are thus forced to do a git clone of the postgres source and sift through git logs, annotations, and the code ourselves.

12.0 was released on 2019-10-13.  "git log" and looking for "daemon" turns up a few possibly relevant commits:

c95ede41b8 In count_usable_fds(), duplicate stderr not stdin.
bb24439cef Detach postmaster process from pg_ctl's session at server startup.

bb24439cef sounds likely, but a git show of the commit doesn't really turn up anything code-wise that looks obviously relevant (it looks signal and SIGINT-related).  That commit is also from Mon Jan 14 14:50:58 2019 +0200.  The discussion that lead to it, which hints at this possibly being relevant after all: https://www.postgresql.org/message-id/CAEET0ZH5Bf7dhZB3mYy8zZQttJrdZg_0Wwaj0o1PuuBny1JkEw%40mail.gmail.com

c95ede41b8 on the other hand seems to be a report about a user going from 12.x to 13.0 and running into issues on OS X, who then later tracked it down as a kind of regression (introduced in 13.x but not present in 12.4), but admitted it had to do with how they were starting pgctl.  However, this is from 2020-10-05: https://postgr.es/m/48bafc63-c30f-3962-2ded-f2e985d93e86@emmenlauer.de

Their mailing list web interface for pgsql-general does not turn up any results for "tty", but pgsql-hackers turns up quite a lot, but most are during a date long, long prior to 12.0 (and since we know 11.15 does not have the problem fixed, we can safely assume the issue was fixed in 12.x branch at some point).  One may want to dig through their REL_12_STABLE branch exclusively.

Summary: we do not know what the relevant code change in Postgres is that fixed this between 12.0 and 12.10, and remains fixed in 13.x and 14.x.  I would have to try each version to narrow down the timeframe, and I am not willing to spend that amount of time for this.  It would not surprise me if one of those two above commits (esp. the latter one) addressed it.  I'm glad it's fixed though.  I hope this ticket and investigation comes in handy for some other SA running (older) Postgres on FreeBSD and wonders why there's an allocated pty/tty.

We can keep this one closed.