Password reset link / 'less' does not exit in psql version 13.4
Hello, I have two very simple questions: 1) I have an account at postgresql.org, but a link to a 'forgot password' seems to be missing on the login page. I have my password stored only on an old Fedora 32 computer. To change the password when logged in, you need to supply the old password. In short, I have no way to migrate this postgresql.org account to my new Fedora 35 and Fedora 36 computers. What can be done about this? 2) I have three psql clients running, a version 12.6, a version 13.4 and a version 14.3. Until now a 'select * from table;' showed the output in 'less' or something alike and exited from 'less' when the output was complete. Both version 12.6 and version 13.4 work that way. Version 14.3 does not exit from 'less' when the output is complete. Did anyone notice this already? Best regards, Mischa Baars.
Re: SSL compression
On Mon, 2021-11-08 at 10:20 +0100, Magnus Hagander wrote: > On Mon, Nov 8, 2021 at 10:11 AM Michael J. Baars > wrote: > > On Mon, 2021-11-08 at 13:30 +0530, Abhijit Menon-Sen wrote: > > > > > At 2021-11-08 08:41:42 +0100, mjbaars1977.pgsql.hack...@gmail.com wrote: > > > > > > Could someone please explain to me, why compression is being > > > > > > considered unsafe / insecure? > > > > > > > > > > https://en.wikipedia.org/wiki/CRIME > > > > > > > > > > > > > Well Abhijit, personally I don't see any connection between crime and > > compression. I do see however, that some people might feel safer > > communicating over an > > SSL > > > > ENCRYPTED line doing their daily business, unjustified as that is, but they > > shouldn't be feeling safer communicating over a compressed line, that would > > be > > > > utterly stupid. > > This is talking about the CRIME attack which *explicitly* is about using the > fact that it's the *combination* of encryption and compression that causes a > problem. There are other similar attacks as well. If you read the link > posted, it will give you a pretty decent overview. > > > > The sole purpose of compression is to reduce the size of a particular > > amount of data. > > This part is of course correct. The problem lies in the *interaction* of > compression and encryption. This is why doing compression as part of the > encryption > layer is fundamentally wrong. > > The problem is that to make it secure, you have to encrypt first and then > compress. But encryption makes the compression a *lot* less efficient, so > what most > solutions did was compress first and then encrypt, which led to > vulnerabilities. Yes, I am aware of the fact that it is pretty much useless to compress an already encrypted data source, the other way around indeed makes more sense. After reading the link, it looks to me that this attacker has for some reason, knowledge of the original unencrypted and uncompressed data or even uses its own data (The attacker then observes the change in size of the compressed request payload, which contains both the secret cookie that is sent by the browser only to the target site, and variable content created by the attacker, as the variable content is altered), which is certainly never the case in a PostgreSQL data transmission. Actually, I don't really see this happening. I've checked all the references, and in my opinion they're all pretty much non-believers. I have more faith in huffman encoding and encryption than I have in these guys, that's for sure. > Would it be good to have the ability to do compression, independent of the > encryption? Yes, definitely. But this is not a feature that PostgreSQL has, > or ever > had. That's too bad! Perhaps an idea for future development? > > > > Might the underlying reason be, that certain people have shown > > > > > > interest in my libpq/PQblockwrite algorithms ( > > > > > > https://www.postgresql.org/message-id/c7cccd0777f39c53b9514e3824badf276759fa87.camel%40cyberfiber.eu) > > > > > > but felt turned down and are now persuading me to trade the algorithms > > > > > > against SSL compression, than just say so please. I'll see what I can > > > > > > do. > > > > > > > > > > The whole world is trying to move away from TLS compression (which has > > > > > been removed from TLS 1.3). It has nothing to do with you. > > > > > > > > As I understand it, TLS is a predecessor of SSL. People are trying to move > > away from TLS, not from compression. > > Then you don't understand it. > > SSL is the predecessor to TLS. TLS has more or less entirely replaced SSL -- > only extremely outdated systems use SSL. TLSv1 followed after SSLv3. > > People are moving *to* TLS. And in particular, TLS v1.3 made significant and > important changes. Compression was dropped from TLS v1.3 because it's > considered > obsolete. > > Again, compression is a perfectly valid usecase. But it shouldn't be, and is > no longer, a part of the encryption layer. Unfortunately, PostgreSQL used to > rely > on it being that, so once it was removed from there, there is no built-in > support for compression. > > One way to handle it, which is a bit ugly, is to tunnel the data through a > VPN or something like that which can enable compression. >
Re: SSL compression
On Mon, 2021-11-08 at 13:30 +0530, Abhijit Menon-Sen wrote: > At 2021-11-08 08:41:42 +0100, mjbaars1977.pgsql.hack...@gmail.com wrote: > > Could someone please explain to me, why compression is being > > considered unsafe / insecure? > > https://en.wikipedia.org/wiki/CRIME > Well Abhijit, personally I don't see any connection between crime and compression. I do see however, that some people might feel safer communicating over an SSL ENCRYPTED line doing their daily business, unjustified as that is, but they shouldn't be feeling safer communicating over a compressed line, that would be utterly stupid. The sole purpose of compression is to reduce the size of a particular amount of data. > > Might the underlying reason be, that certain people have shown > > interest in my libpq/PQblockwrite algorithms ( > > https://www.postgresql.org/message-id/c7cccd0777f39c53b9514e3824badf276759fa87.camel%40cyberfiber.eu) > > but felt turned down and are now persuading me to trade the algorithms > > against SSL compression, than just say so please. I'll see what I can > > do. > > The whole world is trying to move away from TLS compression (which has > been removed from TLS 1.3). It has nothing to do with you. As I understand it, TLS is a predecessor of SSL. People are trying to move away from TLS, not from compression. > > -- Abhijit
SSL compression
Hi All, While I was searching for methods to send the result of a query to the other side of the world, because it might be nice to go there once in a while, I noticed my best option, SSL compression, has been disabled as of version 14. Could someone please explain to me, why compression is being considered unsafe / insecure? Transmissions to areas outside of Europe cost €5/mb at the moment and that makes SSL compression, or compression in general a vital component of d ata transmissions. Might the underlying reason be, that certain people have shown interest in my libpq/PQblockwrite algorithms ( https://www.postgresql.org/message-id/c7cccd0777f39c53b9514e3824badf276759fa87.camel%40cyberfiber.eu) but felt turned down and are now persuading me to trade the algorithms against SSL compression, than just say so please. I'll see what I can do. Best regards, Mischa Baars.
Re: Postgresql network transmission overhead
On Wed, 2021-02-24 at 19:18 -0600, Justin Pryzby wrote:
> On Wed, Feb 24, 2021 at 09:14:19AM +0100, Michael J. Baars wrote:
> > I've written this function to insert several rows at once, and noticed a
> > certain postgresql overhead as you can see from the log file. A lot more
> > data than
> > the
> > user data is actually sent over the net. This has a certain noticeable
> > impact on the user transmission speed.
> >
> > I noticed that a libpq query always has a number of arguments of the
> > following form:
> >
> > Oid paramt[cols]= { 1082, 701, 701, 701, 701, 701, 20,
> > 701 };
> > int paraml[cols]= { 4, 8, 8, 8, 8, 8, 8, 8 };
> > int paramf[cols]= { 1, 1, 1, 1, 1, 1, 1, 1 };
> >
> > result = PQexecParams(psql_cnn, (char* ) , 1, paramt, (const
> > char** ) paramv, paraml, paramf, 1);
> >
> > I think the 'paramf' is completely redundant. The data mode, text or
> > binary, is already specified in the last argument to 'PQexecParams' and
> > does not have
> > to be
> > repeated for every value. Am I correct?
>
> The last argument is the *result* format.
> The array is for the format of the *input* bind parameters.
>
Yes, but we are reading from and writing to the same table here. Why specify
different formats for the input and the output exactly?
Is this vector being sent over the network?
In the logfile you can see that the effective user data being written is only
913kb, while the actual being transmitted over the network is 7946kb when
writing
one row at a time. That is an overhead of 770%!
> Regarding the redundancy:
>
> https://www.postgresql.org/docs/current/libpq-exec.html
> > nParams
> >The number of parameters supplied; it is the length of the arrays
> > paramTypes[], paramValues[], paramLengths[], and paramFormats[]. (The array
> > pointers
> > can be NULL when nParams is zero.)
> > paramTypes[]
> >Specifies, by OID, the data types to be assigned to the parameter
> > symbols. If paramTypes is NULL, or any particular element in the array is
> > zero, the
> > server infers a data type for the parameter symbol in the same way it would
> > do for an untyped literal string.
> > paramValues[]
> >...
> > paramLengths[]
> >Specifies the actual data lengths of binary-format parameters. It is
> > ignored for null parameters and text-format parameters. The array pointer
> > can be
> > null when there are no binary parameters.
> > paramFormats[]
> >Specifies whether parameters are text (put a zero in the array entry for
> > the corresponding parameter) or binary (put a one in the array entry for the
> > corresponding parameter). If the array pointer is null then all parameters
> > are presumed to be text strings.
>
> nParams specifies the length of the arrays: if you pass an array of length
> greater than nParams, then the rest of the array is being ignored.
>
> You don't *have* to specify Types, and Lengths and Formats can be specified as
> NULL for text format params.
>
> > semi-prepared
>
> What does semi-prepared mean ?
>
I'm writing a total of 4096+ rows of each n columns to this table. Some
preparation is in order such that the timer can be placed around the actual
network
transmission only, preparing the statement, i.e. the string of input arguments
and the input data structures, is being done before the timer is started.
Also I noticed that when more columns are used, prepared statements with too
many rows * columns cannot be loaded into postgresql, probably because the size
of
the prepared statements are limited to a certain size in memory. It does not
return an error of the sorts from PQprepare, only during execution of the
statement.
Thanks,
Mischa.
Postgresql network transmission overhead
Hi,
I've written this function to insert several rows at once, and noticed a
certain postgresql overhead as you can see from the log file. A lot more data
than the
user data is actually sent over the net. This has a certain noticeable impact
on the user transmission speed.
I noticed that a libpq query always has a number of arguments of the following
form:
Oid paramt[cols]= { 1082, 701, 701, 701, 701, 701, 20,
701 };
int paraml[cols]= { 4, 8, 8, 8, 8, 8, 8, 8 };
int paramf[cols]= { 1, 1, 1, 1, 1, 1, 1, 1 };
result = PQexecParams(psql_cnn, (char* ) , 1, paramt, (const char**
) paramv, paraml, paramf, 1);
I think the 'paramf' is completely redundant. The data mode, text or binary, is
already specified in the last argument to 'PQexecParams' and does not have to be
repeated for every value. Am I correct?
Thanks,
Mischa Baars.
semi-prepared
rows_s_max:1 ⥂ user transmission:913 kb in 0.3769 s at 2.3670 mb/s psql transmission: 7946 kb in 0.3769 s at 20.5932 mb/s
rows_s_max:2 ⥂ user transmission:913 kb in 0.2454 s at 3.6351 mb/s psql transmission: 6531 kb in 0.2454 s at 25.9928 mb/s
rows_s_max:4 ⥂ user transmission:913 kb in 0.1433 s at 6.2249 mb/s psql transmission: 5824 kb in 0.1433 s at 39.6920 mb/s
rows_s_max:8 ⥂ user transmission:913 kb in 0.1109 s at 8.0434 mb/s psql transmission: 5470 kb in 0.1109 s at 48.1737 mb/s
rows_s_max: 16 ⥂ user transmission:913 kb in 0.0919 s at 9.7027 mb/s psql transmission: 5295 kb in 0.0919 s at 56.2540 mb/s
rows_s_max: 32 ⥂ user transmission:913 kb in 0.0723 s at 12.3401 mb/s psql transmission: 5207 kb in 0.0723 s at 70.3444 mb/s
rows_s_max: 64 ⥂ user transmission:913 kb in 0.0414 s at 21.5674 mb/s psql transmission: 5163 kb in 0.0414 s at 121.9069 mb/s
rows_s_max: 128 ⥂ user transmission:913 kb in 0.0257 s at 34.7756 mb/s psql transmission: 5151 kb in 0.0257 s at 196.1400 mb/s
rows_s_max: 256 ⥂ user transmission:913 kb in 0.0231 s at 38.6210 mb/s psql transmission: 5130 kb in 0.0231 s at 216.9408 mb/s
rows_s_max: 512 ⥂ user transmission:913 kb in 0.0221 s at 40.3377 mb/s psql transmission: 5126 kb in 0.0221 s at 226.3668 mb/s
rows_s_max: 1024 ⥂ user transmission:913 kb in 0.0196 s at 45.4305 mb/s psql transmission: 5124 kb in 0.0196 s at 254.8494 mb/s
rows_s_max: 2048 ⥂ user transmission:913 kb in 0.0228 s at 39.0846 mb/s psql transmission: 5123 kb in 0.0228 s at 219.2123 mb/s
rows_s_max: 4096 ⥂ user transmission:913 kb in 0.0208 s at 42.7914 mb/s psql transmission: 5123 kb in 0.0208 s at 239.9980 mb/s
prepared
rows_s_max:1 ⥂ user transmission:913 kb in 0.5783 s at 1.5425 mb/s psql transmission: 3904 kb in 0.5783 s at 6.5944 mb/s
rows_s_max:2 ⥂ user transmission:913 kb in 0.1869 s at 4.7733 mb/s psql transmission: 3094 kb in 0.1869 s at 16.1709 mb/s
rows_s_max:4 ⥂ user transmission:913 kb in 0.0945 s at 9.4376 mb/s psql transmission: 2689 kb in 0.0945 s at 27.7890 mb/s
rows_s_max:8 ⥂ user transmission:913 kb in 0.0628 s at 14.2077 mb/s psql transmission: 2487 kb in 0.0628 s at 38.6855 mb/s
rows_s_max: 16 ⥂ user transmission:913 kb in 0.0312 s at 28.6356 mb/s psql transmission: 2387 kb in 0.0312 s at 74.8507 mb/s
rows_s_max: 32 ⥂ user transmission:913 kb in 0.0211 s at 42.2622 mb/s psql transmission: 2336 kb in 0.0211 s at 108.1009 mb/s
rows_s_max: 64 ⥂ user transmission:913 kb in 0.0174 s at 51.1602 mb/s psql transmission: 2311 kb in 0.0174 s at 129.4429 mb/s
rows_s_max: 128 ⥂ user transmission:913 kb in 0.0178 s at 50.2382 mb/s psql transmission: 2298 kb in 0.0178 s at 126.4389 mb/s
rows_s_max: 256 ⥂ user transmission:913 kb in 0.0150 s at 59.6196 mb/s psql transmission: 2292 kb in 0.0150 s at 149.6406 mb/s
rows_s_max: 512 ⥂ user transmission:913 kb in 0.0161 s at 55.4055 mb/s psql transmission: 2289 kb in 0.0161 s at 138.8905 mb/s
rows_s_max: 1024 ⥂ user transmission:913 kb in 0.0135 s at 66.0811 mb/s psql transmission: 2288 kb in 0.0135 s at 165.5707 mb/s
rows_s_max: 2048 ⥂ user transmission:913 kb in 0.0133 s at 67.0951 mb/s psql transmission: 2288 kb in 0.0133 s at 168.0688 mb/s
rows_s_max: 4096 ⥂ user transmission:913 kb in 0.0135 s at 66.0615 mb/s psql transmission: 2288 kb in 0.0135 s at 165.4798 mb/s
Re: computing dT from an interval
On Mon, 2021-02-22 at 10:52 -0500, Tom Lane wrote: > "Michael J. Baars" writes: > > So how do you compute the number of seconds in 8 years? > > IMO, that's a meaningless computation, because the answer is not fixed. > Before you claim otherwise, think about the every-four-hundred-years > leap year exception in the Gregorian rules. Besides, what if the > question is "how many seconds in 7 years"? Then it definitely varies > depending on the number of leap days included. > > What does make sense is timestamp subtraction, where the actual > endpoints of the interval are known. > > regards, tom lane > > There you have a point. Strange then that you get an answer other than 'undefined' when subtracting x - y, where y is undefined until x is defined, but you are completely right. An interval of 8 years doesn't count a fixed number of seconds. Thanks, Mischa.
Re: computing dT from an interval
On Sat, 2021-02-20 at 11:20 -0500, Tom Lane wrote: > "Michael J. Baars" writes: > > Can someone please tell me which of these two queries gives the correct > > result and which one the incorrect? > > // 2.922 (&) > > with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from > > interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from > > "00" ) + > > extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) > > / 24 as dT from A1; > > // 2.88 (X) > > with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( > > days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes > > from "00" ) > > / > > 1440 + extract ( seconds from "00" ) / 86400 as dT from A1; > > They'e both "incorrect", for some value of "incorrect". Quantities like > years, days, and seconds don't interconvert freely, which is why the > interval datatype tries to keep them separate. > > In the first case, the main approximation is introduced when you do > > select extract ( epoch from interval '8 years' ); > date_part > --- > 252460800 > (1 row) > > If you do the math, you'll soon see that that corresponds to assuming > 365.25 days (of 86400 seconds each) per year. So that's already wrong; > no year contains fractional days. I don't see the problem in this, we have 6 years of 365 days and 2 years of 366 days. Using this dt, we can compute a set of equidistant time stamps with their corresponding values. Only the first and last row need to be on certain specific points in time, n * dt for certain n does not need to end up on january 1st for each year in the interval. Actually I only need to know for the moment, if dt spans more or less than one week and more or less than one day :) > > In the second case, the trouble starts with > > select interval '8 years' / 1000; > ?column? > - > 2 days 21:07:12 > (1 row) > > Internally, '8 years' is really 96 months, but to divide by 1000 we > have to down-convert that into the lesser units of days and seconds. > The approximation that's used for that is that months have 30 days, > so we initially get 2.88 days, and then the 0.88 days part is > converted to 76032 seconds. > > So yeah, you can poke a lot of holes in these choices, but different > choices would just be differently inconsistent. The Gregorian calendar > is not very rational. > > Personally I stay away from applying interval multiplication/division > to anything except intervals expressed in seconds. As soon as you > get into the larger units, you're forced to make unsupportable > assumptions. So how do you compute the number of seconds in 8 years? > I really think the first one does give the correct answer. The only thing is that the second one, the most trivial one of the two, does not give the same answer as the first. They should have returned exactly the same number if you ask me. > regards, tom lane > Regards, Mischa Baars.
computing dT from an interval
Hi, Can someone please tell me which of these two queries gives the correct result and which one the incorrect? /* * * * * dT in days for 1000 samples */ // 2.922 (&) with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from "00" ) + extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) / 24 as dT from A1; // 2.88 (X) with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes from "00" ) / 1440 + extract ( seconds from "00" ) / 86400 as dT from A1; Personally I think only the first one gives the correct answer. Best regards, Mischa.
