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?
<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/>docker pull ubuntu:18.04 && docker run -it --rm ubuntu:18.04 bash<br/>## or<br/>docker pull 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 curl ca-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>&#39;<br/>apt-get update<br/>apt-get install -y postgresql-12<br/><br/>pg_ctlcluster 12 main start<br/><br/>sudo -u postgres -i<br/>psql<br/>CREATE DATABASE database_name;<br/>CREATE USER 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/>chmod 0600 .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 (foo char);"<br/>```<br/><br/>We have narrowed it down to three packages which have been upgraded on ubuntu 18.04 between working 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 packages have 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/>Diese Nachricht wurde von meinem Android Mobiltelefon mit GMX Mail gesendet.</div><div class="mail_android_quote" style="line-height: 1; padding: 0.3em"><html><body>Am 17.10.22, 16:19 schrieb PG 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;">
The following bug has been logged on the website:
<br>
<br> Bug reference: 17647
<br> Logged by: Mike Fröhner
<br> Email address: mikefroehner@gmx.de
<br> PostgreSQL version: 12.12
<br> Operating system: Ubuntu 18.04
<br> Description:
<br>
<br> Hello,
<br>
<br> today our CI noticed the upgrade of PostgreSQL to 12.12. We have a CI/CD
<br> which tests our Puppet Module against Ubuntu 16./18./20./22.04. We run
<br> different tests (kitchen-ci) including the following:
<br> ```
<br> describe command('/usr/bin/sudo -u postgres psql -U myuser1 -h 127.0.0.1
<br> mydb1 -c "CREATE TABLE foo (foo char);"') do
<br> its('exit_status') { should eq 0 }
<br> its('stdout') { should eq "CREATE TABLE\n" }
<br> its('stderr') { should eq '' }
<br> end
<br> ```
<br> Therefore we rollout a DB and a user via:
<br> ```
<br> postgresql::databases:
<br> 'mydb1':
<br> user: myuser1
<br> password: mypassword1
<br> host: 127.0.0.1/32
<br> ```
<br> We also rollout a file:
<br> ```
<br> '/var/lib/postgresql/.pgpass':
<br> owner: postgres
<br> group: postgres
<br> mode: '0600'
<br> content: '*:*:*:myuser1:mypassword1'
<br> require_class: postgresql
<br> ```
<br> This test only fails/times out on Ubuntu 18.04 because it is waiting for a
<br> password input. With all other Ubuntu (16./20./22.04) this test succedds. Is
<br> there anything wrong with my test or is the package for Ubuntu 18.04 broken?
<br>
<br>
</blockquote></div></body>
</html>
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/>docker pull ubuntu:18.04 && docker run -it --rm ubuntu:18.04 bash<br/>## or<br/>docker pull 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 curl ca-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>&#39;<br/>apt-get update<br/>apt-get install -y postgresql-12<br/><br/>pg_ctlcluster 12 main start<br/><br/>sudo -u postgres -i<br/>psql<br/>CREATE DATABASE database_name;<br/>CREATE USER 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/>chmod 0600 .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 (foo char);"<br/>```<br/><br/>We have narrowed it down to three packages which have been upgraded on ubuntu 18.04 between working 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 packages have 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/>Diese Nachricht wurde von meinem Android Mobiltelefon mit GMX Mail gesendet.</div><div class="mail_android_quote" style="line-height: 1; padding: 0.3em"><html><body>Am 17.10.22, 16:19 schrieb PG 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/>docker pull ubuntu:18.04 && docker run -it --rm ubuntu:18.04 bash<br/>## or<br/>docker pull 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 curl ca-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>&#39;<br/>apt-get update<br/>apt-get install -y postgresql-12<br/><br/>pg_ctlcluster 12 main start<br/><br/>sudo -u postgres -i<br/>psql<br/>CREATE DATABASE database_name;<br/>CREATE USER 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/>chmod 0600 .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 (foo char);"<br/>```<br/><br/>We have narrowed it down to three packages which have been upgraded on ubuntu 18.04 between working 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 packages have 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/>Diese Nachricht wurde von meinem Android Mobiltelefon mit GMX Mail gesendet.</div><div class="mail_android_quote" style="line-height: 1; padding: 0.3em"><html><body>Am 17.10.22, 16:19 schrieb PG 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/postgresqlDo 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
/messages/by-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/postgresqlDo 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
/messages/by-id/1634252654444.90107@mit.edu%5Bhttps://www.postgresql.org/message-id/1634252654444.90107@mit.edu%5D:
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 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. With the previous versions of the packages, the mentioned command (without -i flag) works on all tested Ubuntu (16-22.04). With the upgraded packages 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+1 for 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 different states). The diff only showed the 3 mentioned upgraded packages, even packages from Ubuntu repository haven't been upgraded.
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. I am not sure if there are differences between the compiled packages of 18.04 and 2[02].04 (different flags for compiling, ...) or if they just have different dependencies (which would be reasonable for 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