restoring a single database from a pg_dumpall dump file
Hello, We're using pg_dumpall (from 14.1) to dump older clusters and restore them into a new 14.1 cluster. The dump contains some databases together with roles etc. Is there some easy way to restore only one database out of this dump file? Thanks in advance matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: md5 issues Postgres14 on OL7
On Mon, Dec 20, 2021 at 03:22:31PM +0100, Christoph Moench-Tegeder wrote: > Active FIPS mode (/proc/sys/crypto/fips_enabled => 1) on the server does > produce this behaviour. Most likely, this is a build linked with OpenSSL? The way MD5 hashes are computed in Postgres has largely changed in 14, and the code has been refactored so as we rely on the EVP APIs from OpenSSL when building with --with-ssl=openssl, having as direct consequence to allocate a bit more memory every time a hash is computed. My guess is that this comes from pg_cryptohash_create() in cryptohash_openssl.c, with a complain coming from OpenSSL's EVP_MD_CTX_create(), but there are other palloc() calls in this area as well. -- Michael signature.asc Description: PGP signature
Re: restoring a single database from a pg_dumpall dump file
Matthias Apitz writes: > We're using pg_dumpall (from 14.1) to dump older clusters and > restore them into a new 14.1 cluster. The dump contains some databases > together with roles etc. > Is there some easy way to restore only one database out of this dump > file? No; pg_dumpall's output is just a flat SQL script. You might consider using "pg_dumpall -g" to get just the roles (and tablespaces if any), and then dumping desired databases individually with pg_dump. If all you have is a previously-made dumpall script, you'd have to slice it up with an editor. regards, tom lane
Refresh ONE publication out of the Two Publications the Subscription has
Is it possible to specify the publication name to be refreshed in case I do not want to refresh all publications of the subscription? Something like: alter subscription subscription_name refresh publication publication_name Thanks IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
Re: md5 issues Postgres14 on OL7
Michael Paquier writes:
> On Mon, Dec 20, 2021 at 03:22:31PM +0100, Christoph Moench-Tegeder wrote:
>> Active FIPS mode (/proc/sys/crypto/fips_enabled => 1) on the server does
>> produce this behaviour.
> Most likely, this is a build linked with OpenSSL? The way MD5 hashes
> are computed in Postgres has largely changed in 14, and the code has
> been refactored so as we rely on the EVP APIs from OpenSSL when
> building with --with-ssl=openssl, having as direct consequence to
> allocate a bit more memory every time a hash is computed. My guess is
> that this comes from pg_cryptohash_create() in cryptohash_openssl.c,
> with a complain coming from OpenSSL's EVP_MD_CTX_create(), but there
> are other palloc() calls in this area as well.
I reproduced this on Fedora 35 with FIPS mode enabled. The problem
is that OpenSSL treats MD5 as a disallowed cipher type under FIPS
mode, so this call in pg_cryptohash_init fails:
status = EVP_DigestInit_ex(ctx->evpctx, EVP_md5(), NULL);
and then we come back to this in md5_text():
/* get the hash result */
if (pg_md5_hash(VARDATA_ANY(in_text), len, hexsum) == false)
ereport(ERROR,
(errcode(ERRCODE_OUT_OF_MEMORY),
errmsg("out of memory")));
So there's nothing actually misbehaving, but our error reportage sucks:
the hash functions have no way to report a specific failure code,
and the caller(s) think the only possible failure mode is OOM.
I suppose we could get around the error by using our own MD5 code
even in OpenSSL-enabled builds, but that'd violate both the spirit
and the letter of FIPS certification. I think the right response is
to upgrade the error-reporting API in this area, so that the message
could look more like "MD5 is disallowed in FIPS mode".
regards, tom lane
Re: Tab-completion error...?
I don't use pacman for PostgreSQL. I compile from source. Seems as though this error shouldn't happen. On Wed, Dec 22, 2021 at 5:24 PM Adrian Klaver wrote: > On 12/22/21 2:14 PM, Theodore M Rolle, Jr. wrote: > Please reply to list also. > Ccing list. > > From below, what did pacman -Syyuu do? > > > You are correct in guessing what I did... > > > > config.log: > > a bunch of > > #define HAVE_LIBREADLINE 1 > > then > > configure:13450: checking readline/readline.h usability > > configure:13450: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith > > -Wdeclaration-after-statement -Werror=vla -Wendif-labels > > -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type > > -Wformat-security -fno-strict-aliasing -fwrapv > > -fexcess-precision=standard -Wno-format-truncation > > -Wno-stringop-truncation -O2 -I/home/ted/hercules-helper/rexx/include > > -D_GNU_SOURCE conftest.c >&5 > > configure:13450: $? = 0 > > > > configure:13450: result: yes > > configure:13450: checking readline/readline.h presence > > configure:13450: gcc -E -I/home/ted/hercules-helper/rexx/include > > -D_GNU_SOURCE conftest.c > > configure:13450: $? = 0 > > configure:13450: result: yes > > configure:13450: checking for readline/readline.h > > configure:13450: result: yes > > configure:13480: checking readline/history.h usability > > configure:13480: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith > > -Wdeclaration-after-statement -Werror=vla -Wendif-labels > > -Wmissing-format-attribute -Wimplicit-fallthrough=3 > > -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv > > -fexcess-precision=standard -Wno-format-truncation > > -Wno-stringop-truncation -O2 > > -I/home/ted/hercules-helper/rexx/include -D_GNU_SOURCE conftest.c > >&5 > > configure:13480: $? = 0 > > configure:13480: result: yes > > configure:13480: checking readline/history.h presence > > configure:13480: gcc -E -I/home/ted/hercules-helper/rexx/include > > -D_GNU_SOURCE conftest.c > > configure:13480: $? = 0 > > configure:13480: result: yes > > configure:13480: checking for readline/history.h > > configure:13480: result: yes > > configure:13602: checking zlib.h usability > > configure:13602: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith > > -Wdeclaration-after-statement -Werror=vla -Wendif-labels > > -Wmissing-format-attribute -Wimplicit-fallthrough=3 > > -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv > > -fexcess-precision=standard -Wno-format-truncation > > -Wno-stringop-truncation -O2 > > -I/home/ted/hercules-helper/rexx/include -D_GNU_SOURCE conftest.c > >&5 > > configure:13602: $? = 0 > > > > It looks good, doesn't it? > > N.B. v14.1 is the first version to have this problem. Another thought: > > perhaps the pacman -Syyuu update did it. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- GnuPG/PGP key: 0xDD4276BA +-+ | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 | | 58209 74944[59230 78164]06286 20899 86280 +--| | 34825 34211 70679*82148 08651 32823 06647 |May the spirit | | 09384 46095 50582 23172 53594 08128 48111 | of π spread | | 74502 84102 70193 85211 05559 64462 29489 |around the world. | | 54930 38196 44288 10975 66593 34461 28475 | PI VOBISCUM! | | 38196 44288 10975 66593 34461 28475 64823 +-| | 37867 83165 27120 19091 45648 56692 34603 48610 45432 6648... | ++
Re: Refresh ONE publication out of the Two Publications the Subscription has
Στις 4/1/22 5:47 μ.μ., ο/η Avi Weinberg έγραψε: Is it possible to specify the publication name to be refreshed in case I do not want to refresh all publications of the subscription? Something like: alter subscription subscription_name refresh publication publication_name Here is the method: step a) find the pgsql version you use step b) find the documentation step c) read it to spare you the hassle : no Thanks IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
Re: Tab-completion error...?
"Theodore M Rolle, Jr." writes: > I don't use pacman for PostgreSQL. > I compile from source. The point is that your from-source build of psql might be linking to an out-of-date copy of libpq.so provided by the OS. Linux machines tend to do that (i.e., prefer libraries in /usr/lib[64]) unless you mess with the dynamic loader's configuration. Try "ldd /path/to/psql" and see what it says about which libpq is getting used. regards, tom lane
Re: As a Linux distro, how to package multiple postgres major versions?
Hello, I left process as described in previous message. That's after people on irc confirmed that using libpq across versions is fine, and realizing that libpq is just a client to talk to server, and doesn't implement other server functionality. W dniu 25.11.2021 o 21:17, Laurent FAILLIE pisze: Hello, Which distribution are you speaking about ? When you're upgrading your system, all supported version are rebuilt every time a dependencies change Void Linux, a binary distribution. Packages are rebuild here only on shlib changes, not after every dependency update, in trust to library developers keeping compatibility otherwise, so far with good outcome. Switching versions is not yet enabled for postgres, but planned on next update. Thanks for tips!
Re: md5 issues Postgres14 on OL7
## Michael Paquier (mich...@paquier.xyz):
> On Mon, Dec 20, 2021 at 03:22:31PM +0100, Christoph Moench-Tegeder wrote:
> > Active FIPS mode (/proc/sys/crypto/fips_enabled => 1) on the server does
> > produce this behaviour.
>
> Most likely, this is a build linked with OpenSSL? The way MD5 hashes
> are computed in Postgres has largely changed in 14, and the code has
> been refactored so as we rely on the EVP APIs from OpenSSL when
> building with --with-ssl=openssl, having as direct consequence to
> allocate a bit more memory every time a hash is computed.
You can reproduce that behaviour with the PGDG-RPMs on CentOS 7.
Enable FIPS-mode, reboot, and immediately md5() fails. The PGDG-RPMS
are built with openssl ("--with-openssl" in pg_config output),
as of course you need SSL today.
"Supports FIPS mode" is one of the selling points for your cryptohash
patches in the Release Notes, and that means no md5 when FIPS is
enforced (I think FIPS is a little too strict in this regard, as
people do invent horrid workarounds, which does not really improve
matters; but that's another can of worms).
Anyway, it's not the memory, but "out of memory" is all PostgreSQL
reports when anything in the hashing operations returns a failure.
Regards,
Christoph
--
Spare Space
Re: Tab-completion error...?
ldd /usr/local/pgsql/bin/psql linux-vdso.so.1 (0xa2bef000) libpq.so.5 => /USR/local/lib/libpq.so.5 (0xa2aaf000) libreadline.so.8 => /usr/lib/libreadline.so.8 (0xa2a1d000) libpthread.so.0 => /usr/lib/libpthread.so.0 (0xa29ed000) libm.so.6 => /usr/lib/libm.so.6 (0xa2941000) libc.so.6 => /usr/lib/libc.so.6 (0xa27cd000) /lib/ld-linux-aarch64.so.1 => /usr/lib/ld-linux-aarch64.so.1 (0xa2bbe000) libncursesw.so.6 => /usr/lib/libncursesw.so.6 (0xa2748000 I'm at a loss as to where the /USR came from. It's not in config.log (compile time) nor in the psql executable (run time). On Tue, Jan 4, 2022 at 1:32 PM Tom Lane wrote: > "Theodore M Rolle, Jr." writes: > > I don't use pacman for PostgreSQL. > > I compile from source. > > The point is that your from-source build of psql might be linking > to an out-of-date copy of libpq.so provided by the OS. Linux > machines tend to do that (i.e., prefer libraries in /usr/lib[64]) > unless you mess with the dynamic loader's configuration. > > Try "ldd /path/to/psql" and see what it says about which libpq > is getting used. > > regards, tom lane > -- GnuPG/PGP key: 0xDD4276BA +-+ | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 | | 58209 74944[59230 78164]06286 20899 86280 +--| | 34825 34211 70679*82148 08651 32823 06647 |May the spirit | | 09384 46095 50582 23172 53594 08128 48111 | of π spread | | 74502 84102 70193 85211 05559 64462 29489 |around the world. | | 54930 38196 44288 10975 66593 34461 28475 | PI VOBISCUM! | | 38196 44288 10975 66593 34461 28475 64823 +-| | 37867 83165 27120 19091 45648 56692 34603 48610 45432 6648... | ++
Re: Tab-completion error...?
"Theodore M Rolle, Jr." writes: > ldd /usr/local/pgsql/bin/psql > linux-vdso.so.1 (0xa2bef000) > libpq.so.5 => /USR/local/lib/libpq.so.5 (0xa2aaf000) > libreadline.so.8 => /usr/lib/libreadline.so.8 (0xa2a1d000) > libpthread.so.0 => /usr/lib/libpthread.so.0 (0xa29ed000) > libm.so.6 => /usr/lib/libm.so.6 (0xa2941000) > libc.so.6 => /usr/lib/libc.so.6 (0xa27cd000) > /lib/ld-linux-aarch64.so.1 => /usr/lib/ld-linux-aarch64.so.1 > (0xa2bbe000) > libncursesw.so.6 => /usr/lib/libncursesw.so.6 (0xa2748000 Hm, is /USR actually in caps, or did you change that for emphasis? > I'm at a loss as to where the /USR came from. > It's not in config.log (compile time) > nor in the psql executable (run time). I think it came out of /etc/ld.so.conf. BTW, by default PG would link psql using an rpath switch pointing at /usr/local/pgsql/lib, which I assume is where your manual build put its libpq.so. That's evidently not having success getting that libpq.so to be used. Did you tell configure to --disable-rpath? Or maybe move the installation after building it? regards, tom lane
Re: As a Linux distro, how to package multiple postgres major versions?
On 1/4/22 13:15, Chocimier wrote: Hello, I left process as described in previous message. That's after people on irc confirmed that using libpq across versions is fine, and realizing that libpq is just a client to talk to server, and doesn't implement other server functionality. As long as you are using the latest version of libpq. For a recent thread on where it may be causing an issue because of incompatibilities: https://www.postgresql.org/message-id/4042326.1641321158%40sss.pgh.pa.us W dniu 25.11.2021 o 21:17, Laurent FAILLIE pisze: Hello, Which distribution are you speaking about ? When you're upgrading your system, all supported version are rebuilt every time a dependencies change Void Linux, a binary distribution. Packages are rebuild here only on shlib changes, not after every dependency update, in trust to library developers keeping compatibility otherwise, so far with good outcome. Switching versions is not yet enabled for postgres, but planned on next update. Thanks for tips! -- Adrian Klaver adrian.kla...@aklaver.com
Re: md5 issues Postgres14 on OL7
On Tue, Jan 04, 2022 at 12:54:35PM -0500, Tom Lane wrote:
> I reproduced this on Fedora 35 with FIPS mode enabled. The problem
> is that OpenSSL treats MD5 as a disallowed cipher type under FIPS
> mode, so this call in pg_cryptohash_init fails:
Is that 3.0.0 or 1.1.1? I can see the following, telling that Fedora
35 uses OpenSSL 1.1.1:
https://packages.fedoraproject.org/pkgs/openssl/openssl/
And there is FIPS 2.0 for 1.0.1 and 1.0.2 (funnily, I recall that
1.0.2+FIPS allows MD5 to work with the EVP routines), but the module
of FIPS 3.0 does not work with 1.1.1 AFAIK, so I may be confused. Or
perhaps this is OpenSSL 1.1.1 with a separate module? The upstream
code does nothing special with EVP_DigestInit_ex() in 1.1.1 (see
digest.c), contrary to 3.0.0 where there is specific knowledge of
FIPS.
> status = EVP_DigestInit_ex(ctx->evpctx, EVP_md5(), NULL);
>
> and then we come back to this in md5_text():
>
> /* get the hash result */
> if (pg_md5_hash(VARDATA_ANY(in_text), len, hexsum) == false)
> ereport(ERROR,
> (errcode(ERRCODE_OUT_OF_MEMORY),
> errmsg("out of memory")));
>
> So there's nothing actually misbehaving, but our error reportage sucks:
> the hash functions have no way to report a specific failure code,
> and the caller(s) think the only possible failure mode is OOM.
Indeed, this error is a pilot error with the cryptohash integration of
14. In ~13, the custom MD5 implementation would only fail on OOM, but
more failure modes are possible now.
> I suppose we could get around the error by using our own MD5 code
> even in OpenSSL-enabled builds, but that'd violate both the spirit
> and the letter of FIPS certification.
I don't think we should go back to an integration of our custom MD5 if
we have external libraries that provide support for it. That's
against the set of FIPS policies.
> I think the right response is
> to upgrade the error-reporting API in this area, so that the message
> could look more like "MD5 is disallowed in FIPS mode".
Hmm. I am not sure how much we should try to make the backend, or
even the frontend, FIPS-aware (remember for example 0182438 where we
avoided this kind of complexity), and not all SSL libraries we would
potentially add support for may care about such error states. The
cleanest approach may be to extend the APIs to store an **errstr so
as implementations are free to assign the error string they want to
send back for the error reporting, rather than using more error codes.
If we want to improve things in this area with FIPS (aka allow
check-world to pass in this case), we would need more in terms of
alternate test output, and extra tweaks for the authentication tests,
as well. Perhaps the best thing to do in the long term would be to
drop MD5, but we are not there yet IMO even if password_encryption
default has changed, and upgrade scenarios get hairy.
At the end, I agree that we should improve the error message in these
two cases. However, I would stick to simplicity by not assuming that
those two code paths fail only on OOM, and reword things in md5_text()
and md5_bytea() with a simple "could not compute MD5 hash". Any code
paths calling the routines of md5_common.c just do that as well for
ages when the computation fails, and that's what we care about here.
--
Michael
signature.asc
Description: PGP signature
Re: md5 issues Postgres14 on OL7
Michael Paquier writes: > On Tue, Jan 04, 2022 at 12:54:35PM -0500, Tom Lane wrote: >> I reproduced this on Fedora 35 with FIPS mode enabled. The problem >> is that OpenSSL treats MD5 as a disallowed cipher type under FIPS >> mode, so this call in pg_cryptohash_init fails: > Is that 3.0.0 or 1.1.1? I can see the following, telling that Fedora > 35 uses OpenSSL 1.1.1: > https://packages.fedoraproject.org/pkgs/openssl/openssl/ I don't have the image booted up right at the moment, but it was a plain vanilla, fresh-out-of-the-box F35 install, so whatever the default openssl version is for that. That link does say that it should be 1.1.1l. > Indeed, this error is a pilot error with the cryptohash integration of > 14. In ~13, the custom MD5 implementation would only fail on OOM, but > more failure modes are possible now. Right, the code in md5_text() was fine when it was written ... but now, not so much. > At the end, I agree that we should improve the error message in these > two cases. However, I would stick to simplicity by not assuming that > those two code paths fail only on OOM, and reword things in md5_text() > and md5_bytea() with a simple "could not compute MD5 hash". Any code > paths calling the routines of md5_common.c just do that as well for > ages when the computation fails, and that's what we care about here. I think it's very important that the error message in this case mention "FIPS mode" explicitly. Otherwise, people will have no idea that that's where the problem originates, and they'll be frustrated and we'll get bug reports. (They may be frustrated anyway, but it was their choice, or their corporate policy's choice, to cut off their access to MD5. Not our place to dodge that decision.) regards, tom lane
Re: md5 issues Postgres14 on OL7
On Wed, Jan 05, 2022 at 01:08:53AM -0500, Tom Lane wrote: > I think it's very important that the error message in this case > mention "FIPS mode" explicitly. Otherwise, people will have no > idea that that's where the problem originates, and they'll be > frustrated and we'll get bug reports. (They may be frustrated > anyway, but it was their choice, or their corporate policy's > choice, to cut off their access to MD5. Not our place to dodge > that decision.) I am not completely sure how to detect that in 1.1.1 in the context of Fedora, and portability may become a tricky thing. FIPS_mode() and FIPS_mode_set() are legacy APIs that should not be used, and upstream just disables them in 1.1.1. [... digs a bit ...] Ugh. Fedora patches upstream's 1.1.1 to check and react on /proc/sys/crypto/fips_enabled. Their code is here, see particularly 0009-Add-Kernel-FIPS-mode-flag-support.patch: https://src.fedoraproject.org/rpms/openssl.git So that's why you are able to use it with 1.1.1. Well, we could do something similar to that, but in 3.0.0 things are done very differently: one has to set to alg_sect fips=yes with fips = fips_sect in the OpenSSL configuration to load the FIPS provider. Providing more error context is going to be hairy here.. In order to make things portable with 14 in cryptohash.c, we don't have any need to change the existing cryptohash APIs. We could just store in each implementation context a location to a static string, and add a new routine to extract it if there is an error, defaulting to OOM. -- Michael signature.asc Description: PGP signature
