Thread: BUG #17647: 12.12 package has difference on ubuntu 18.04
The following bug has been logged on the website:
Bug reference: 17647
Logged by: Mike Fröhner
Email address: mikefroehner@gmx.de
PostgreSQL version: 12.12
Operating system: Ubuntu 18.04
Description:
Hello,
today our CI noticed the upgrade of PostgreSQL to 12.12. We have a CI/CD
which tests our Puppet Module against Ubuntu 16./18./20./22.04. We run
different tests (kitchen-ci) including the following:
```
describe command('/usr/bin/sudo -u postgres psql -U myuser1 -h 127.0.0.1
mydb1 -c "CREATE TABLE foo (foo char);"') do
its('exit_status') { should eq 0 }
its('stdout') { should eq "CREATE TABLE\n" }
its('stderr') { should eq '' }
end
```
Therefore we rollout a DB and a user via:
```
postgresql::databases:
'mydb1':
user: myuser1
password: mypassword1
host: 127.0.0.1/32
```
We also rollout a file:
```
'/var/lib/postgresql/.pgpass':
owner: postgres
group: postgres
mode: '0600'
content: '*:*:*:myuser1:mypassword1'
require_class: postgresql
```
This test only fails/times out on Ubuntu 18.04 because it is waiting for a
password input. With all other Ubuntu (16./20./22.04) this test succedds. Is
there anything wrong with my test or is the package for Ubuntu 18.04 broken?
Hi, On Tue, Oct 18, 2022 at 10:19:20AM +0200, Mike Fröhner wrote: > > <html> > <head> > <meta name="viewport" content="width=device-width"> > <meta http-equiv="Content-Type" content="text/vnd.ui.insecure+html;charset=utf-8"> > </head> > <body style="overflow-wrap:break-word; word-break: break-word;"><div class="mail_android_message" style="line-height:1; padding: 0.5em">Hello<br/><br/>I have made some deeper investigation:<br/><br/>Steps to reproduce:<br/><br/>```<br/>dockerpull ubuntu:18.04 && docker run -it --rm ubuntu:18.04 bash<br/>## or<br/>dockerpull ubuntu:20.04 && docker run -it --rm ubuntu:20.04 bash<br/>## or<br/>docker pull ubuntu:22.04 &&docker run -it --rm ubuntu:22.04 bash<br/><br/><br/>apt-get update<br/>apt-get install -y vim strace less curlca-certificates gnupg sudo lsb-release<br/><br/>curl <a href="https://www.postgresql.org/media/keys/ACCC4CF8.asc">https://www.postgresql.org/media/keys/ACCC4CF8.asc</a>| gpg --dearmor| sudo tee /etc/apt/<a href="http://trusted.gpg">trusted.gpg</a>.d/<a href="http://apt.postgresql.org.gpg">apt.postgresql.org.gpg</a>>/dev/null<br/><br/>sh -c 'echo "deb <a href="http://apt.postgresql.org/pub/repos/apt">http://apt.postgresql.org/pub/repos/apt</a>$(lsb_release -cs)-pgdg main"> /etc/apt/<a href="http://sources.list">sources.list</a>.d/<a href="http://pgdg.list">pgdg.list</a>'<br/>apt-getupdate<br/>apt-get install -y postgresql-12<br/><br/>pg_ctlcluster12 main start<br/><br/>sudo -u postgres -i<br/>psql<br/>CREATE DATABASE database_name;<br/>CREATEUSER my_username WITH PASSWORD 'my_password';<br/>GRANT ALL PRIVILEGES ON DATABASE "database_name"to my_username;<br/>exit<br/><br/>echo '*:*:*:my_username:my_password' > .pgpass<br/>chmod0600 .pgpass<br/>exit<br/><br/>##<br/>## this does not work for ubuntu 18.04 but for ubuntu 20.04/22.04<br/>##<br/>/usr/bin/sudo-u postgres psql -U my_username -h <a href="http://127.0.0.1">127.0.0.1</a> database_name-c "CREATE TABLE foo (foo char);"<br/><br/>##<br/>## this works for all<br/>##<br/>sudo -u postgres-i<br/>psql -U my_username -h <a href="http://127.0.0.1">127.0.0.1</a> database_name -c "CREATE TABLE foo (foochar);"<br/>```<br/><br/>We have narrowed it down to three packages which have been upgraded on ubuntu 18.04 betweenworking and broken state:<br/>- `libpq5` upgraded from `14.5-1.pgdg18.04+1` to `15.0-1.pgdg18.04+1`<br/>- `postgresql-client-common`and `postgresql-common` from `243.pgdg18.04+1` to `244.pgdg18.04+1`<br/><br/>None other packageshave been upgraded (incl. ubuntu repo packages)!<br/><br/>For me it does look like a bug with in psql client package.<br/><br/>regards<br/>Mike<br/><br/>--<br/>DieseNachricht wurde von meinem Android Mobiltelefon mit GMX Mail gesendet.</div><divclass="mail_android_quote" style="line-height: 1; padding: 0.3em"><html><body>Am 17.10.22, 16:19 schriebPG Bug reporting form <noreply@postgresql.org>:</body></html><blockquote class="gmail_quote" style="margin:0.8ex 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> You should configure your MUA to send plaintext too and not only html, as some people can't (and don't want to) read html-only emails. After reading it on the html archives, I'm surprised to see that this would work at all on other ubuntu versions, and it doesn't look like a bug in our code. sudo without -i will look for the .pgpass in the wrong place: # sudo -u postgres bash -c "echo \$HOME" /root # sudo -iu postgres bash -c "echo \$HOME" /var/lib/postgresql Do you get a different behavior for those commands on other ubuntu versions?
On Tue, Oct 18, 2022 at 08:44:15PM +0800, Julien Rouhaud wrote: > Hi, > > On Tue, Oct 18, 2022 at 10:19:20AM +0200, Mike Fröhner wrote: > > > > <html> > > <head> > > <meta name="viewport" content="width=device-width"> > > <meta http-equiv="Content-Type" content="text/vnd.ui.insecure+html;charset=utf-8"> > > </head> > > <body style="overflow-wrap:break-word; word-break: break-word;"><div class="mail_android_message" style="line-height:1; padding: 0.5em">Hello<br/><br/>I have made some deeper investigation:<br/><br/>Steps to reproduce:<br/><br/>```<br/>dockerpull ubuntu:18.04 && docker run -it --rm ubuntu:18.04 bash<br/>## or<br/>dockerpull ubuntu:20.04 && docker run -it --rm ubuntu:20.04 bash<br/>## or<br/>docker pull ubuntu:22.04 &&docker run -it --rm ubuntu:22.04 bash<br/><br/><br/>apt-get update<br/>apt-get install -y vim strace less curlca-certificates gnupg sudo lsb-release<br/><br/>curl <a href="https://www.postgresql.org/media/keys/ACCC4CF8.asc">https://www.postgresql.org/media/keys/ACCC4CF8.asc</a>| gpg --dearmor| sudo tee /etc/apt/<a href="http://trusted.gpg">trusted.gpg</a>.d/<a href="http://apt.postgresql.org.gpg">apt.postgresql.org.gpg</a>>/dev/null<br/><br/>sh -c 'echo "deb <a href="http://apt.postgresql.org/pub/repos/apt">http://apt.postgresql.org/pub/repos/apt</a>$(lsb_release -cs)-pgdg main"> /etc/apt/<a href="http://sources.list">sources.list</a>.d/<a href="http://pgdg.list">pgdg.list</a>'<br/>apt-getupdate<br/>apt-get install -y postgresql-12<br/><br/>pg_ctlcluster12 main start<br/><br/>sudo -u postgres -i<br/>psql<br/>CREATE DATABASE database_name;<br/>CREATEUSER my_username WITH PASSWORD 'my_password';<br/>GRANT ALL PRIVILEGES ON DATABASE "database_name"to my_username;<br/>exit<br/><br/>echo '*:*:*:my_username:my_password' > .pgpass<br/>chmod0600 .pgpass<br/>exit<br/><br/>##<br/>## this does not work for ubuntu 18.04 but for ubuntu 20.04/22.04<br/>##<br/>/usr/bin/sudo-u postgres psql -U my_username -h <a href="http://127.0.0.1">127.0.0.1</a> database_name-c "CREATE TABLE foo (foo char);"<br/><br/>##<br/>## this works for all<br/>##<br/>sudo -u postgres-i<br/>psql -U my_username -h <a href="http://127.0.0.1">127.0.0.1</a> database_name -c "CREATE TABLE foo (foochar);"<br/>```<br/><br/>We have narrowed it down to three packages which have been upgraded on ubuntu 18.04 betweenworking and broken state:<br/>- `libpq5` upgraded from `14.5-1.pgdg18.04+1` to `15.0-1.pgdg18.04+1`<br/>- `postgresql-client-common`and `postgresql-common` from `243.pgdg18.04+1` to `244.pgdg18.04+1`<br/><br/>None other packageshave been upgraded (incl. ubuntu repo packages)!<br/><br/>For me it does look like a bug with in psql client package.<br/><br/>regards<br/>Mike<br/><br/>--<br/>DieseNachricht wurde von meinem Android Mobiltelefon mit GMX Mail gesendet.</div><divclass="mail_android_quote" style="line-height: 1; padding: 0.3em"><html><body>Am 17.10.22, 16:19 schriebPG Bug reporting form <noreply@postgresql.org>:</body></html><blockquote class="gmail_quote" style="margin:0.8ex 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> > > You should configure your MUA to send plaintext too and not only html, as some > people can't (and don't want to) read html-only emails. After reading it on > the html archives, I'm surprised to see that this would work at all on other > ubuntu versions, and it doesn't look like a bug in our code. sudo without -i > will look for the .pgpass in the wrong place: > > # sudo -u postgres bash -c "echo \$HOME" > /root > > # sudo -iu postgres bash -c "echo \$HOME" > /var/lib/postgresql > > Do you get a different behavior for those commands on other ubuntu versions? Scratch that, after checking the code I can see that up to version 14 we were only relying on getpwuid or getpwuid_r (which would work with or without -i), but starting with pg15 we first look at the $HOME variable which indeed is problematic. This was introduced in 376ce3e404b, per request at https://www.postgresql.org/message-id/1634252654444.90107@mit.edu: > According to getpwnam(3): > > An application that wants to determine its user's home directory > should inspect the value of HOME (rather than the value > getpwuid(getuid())->pw_dir) since this allows the user to modify > their notion of "the home directory" during a login session. So there is indeed a behavior change, but it's wanted and documented in the release notes.
Gesendet: Dienstag, 18. Oktober 2022 um 15:25 Uhr Von: "Julien Rouhaud" <rjuju123@gmail.com> An: "Mike Fröhner" <mikefroehner@gmx.de> Cc: pgsql-bugs@lists.postgresql.org Betreff: Re: BUG #17647: 12.12 package has difference on ubuntu 18.04 On Tue, Oct 18, 2022 at 08:44:15PM +0800, Julien Rouhaud wrote: >> You should configure your MUA to send plaintext too and not only html, as some >> people can't (and don't want to) read html-only emails. After reading it on >> the html archives, I'm surprised to see that this would work at all on other >> ubuntu versions, and it doesn't look like a bug in our code. sudo without -i >> will look for the .pgpass in the wrong place: >> >> # sudo -u postgres bash -c "echo \$HOME" >> /root >> >> # sudo -iu postgres bash -c "echo \$HOME" >> /var/lib/postgresql >> >> Do you get a different behavior for those commands on other ubuntu versions? > Scratch that, after checking the code I can see that up to version 14 we were > only relying on getpwuid or getpwuid_r (which would work with or without -i), > but starting with pg15 we first look at the $HOME variable which indeed is > problematic. > This was introduced in 376ce3e404b, per request at > https://www.postgresql.org/message-id/1634252654444.90107@mit.edu[https://www.postgresql.org/message-id/1634252654444.90107@mit.edu]: >> According to getpwnam(3): >> >> An application that wants to determine its user's home directory >> should inspect the value of HOME (rather than the value >> getpwuid(getuid())->pw_dir) since this allows the user to modify >> their notion of "the home directory" during a login session. > So there is indeed a behavior change, but it's wanted and documented in the > release notes. Sorry about HTML, I am not used to mailing-lists anymore. :( I have verified that your suggestion works (with added -i flag for sudo): # /usr/bin/sudo -iu postgres psql -U my_username -h 127.0.0.1 database_name -c "CREATE TABLE foo1 (foo1 char);" CREATE TABLE I have also tested the following command successfully (using su instead of sudo): # su postgres -c 'psql -U my_username -h 127.0.0.1 database_name -c "CREATE TABLE foo2 (foo2 char);"' CREATE TABLE But to ensure we understand correctly - I am not talking about pg14 or pg15. We have noticed the changed behavior ONLY whileusing postgresql-12 package on Ubuntu 18.04 with libpq5 upgraded from 14.5-1.pgdg18.04+1 to 15.0-1.pgdg18.04+1 and postgresql-client-common+ postgresql-common upgraded from 243.pgdg18.04+1 to 244.pgdg18.04+1. With the previous versionsof the packages, the mentioned command (without -i flag) works on all tested Ubuntu (16-22.04). With the upgradedpackages we have to add the -i flag to make it work. The used version of postgresql-12 is always the same: 12.12-1.pgdg18.04+1for Ubuntu 18.04 and 12.12-1.pgdg20.04+1 for Ubuntu 20.04. We also made a diff of `dpkg -l`-output (We have daily snapshots of the mirrored repository, so we can switch between differentstates). The diff only showed the 3 mentioned upgraded packages, even packages from Ubuntu repository haven't beenupgraded. For now I am fine with adjusted command in our testing suite. But it is still strange the same command works on all versionsof Ubuntu LTS but not on Ubuntu 18.04. I am not sure if there are differences between the compiled packages of 18.04and 2[02].04 (different flags for compiling, ...) or if they just have different dependencies (which would be reasonablefor me). Their versions indicate they should be compiled from the same code. If you don't want to investigate this strange behavior anymore I am fine. regards Mike
=?UTF-8?Q?Mike_Fr=C3=B6hner?= <mikefroehner@gmx.de> writes:
> But to ensure we understand correctly - I am not talking about pg14 or
> pg15. We have noticed the changed behavior ONLY while using
> postgresql-12 package on Ubuntu 18.04 with libpq5 upgraded from
> 14.5-1.pgdg18.04+1 to 15.0-1.pgdg18.04+1 and postgresql-client-common +
> postgresql-common upgraded from 243.pgdg18.04+1 to 244.pgdg18.04+1.
It's libpq that determines this behavior, not the server, so I find
nothing whatever surprising in your statement.
> For now I am fine with adjusted command in our testing suite. But it is
> still strange the same command works on all versions of Ubuntu LTS but
> not on Ubuntu 18.04.
Probably they've not updated libpq so quickly in the LTS distros.
regards, tom lane