Potential vuln in example for "F.25.1.1. digest()"
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/pgcrypto.html Description: Hi, in "F.25.1.1. digest()" you suggest: CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT IMMUTABLE; While this is a great example, it may expose a database app to vulnerabilities if the attacker succeeds in overriding the function sha1(...) in the app's user context (schema). This may or may not require administrative privileges. Explicitly putting it into the "postgres" schema and calling it using "postgres.sha1(...)" could mitigate the risk in such a way that administrative privileges are required. Do you have an even better solution to secure it? :-) Beat
Re: Potential vuln in example for "F.25.1.1. digest()"
On Tuesday, August 17, 2021, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/13/pgcrypto.html > Description: > > Hi, > in "F.25.1.1. digest()" you suggest: > > CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ > SELECT encode(digest($1, 'sha1'), 'hex') > $$ LANGUAGE SQL STRICT IMMUTABLE; > > While this is a great example, it may expose a database app to > vulnerabilities if the attacker succeeds in overriding the function > sha1(...) in the app's user context (schema) > You should read this: https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path David J.
Re: Potential vuln in example for "F.25.1.1. digest()"
"David G. Johnston" writes: > On Tuesday, August 17, 2021, PG Doc comments form > wrote: >> in "F.25.1.1. digest()" you suggest: >> >> CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ >> SELECT encode(digest($1, 'sha1'), 'hex') >> $$ LANGUAGE SQL STRICT IMMUTABLE; >> >> While this is a great example, it may expose a database app to >> vulnerabilities if the attacker succeeds in overriding the function >> sha1(...) in the app's user context (schema) > You should read this: > https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path Yeah. I can't get terribly excited about trying to make this one example unconditionally-secure; there are dozens if not hundreds of similar cases in our docs. Trying to make them all safe against insecure search paths would mostly result in unreadable examples. regards, tom lane
Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/transaction-iso.html
Description:
hello!
documentation for "read committed" says that:
"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ...
...If the first updater commits, the second updater will ignore the row if
the first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row. The search condition of the
command (the WHERE clause) is re-evaluated to see if the updated version of
the row still matches the search condition."
but this behaviour is not reproduced for UPDATE .. WHERE ..
update from second session don't takes into account updated data in row
committed by first session.
example:
session1:
begin;
create table t ( x numeric, y numeric );
insert into t values ( -1, 11 );
insert into t values ( 1, 12 );
commit; -- so table is now visible for another transactions
begin;
update t set x=-x, y=y+100; -- here rows is locked by session1 until
commit\rollback
session 2
begin;
update t set y=y+1000 where x<0; -- here session2 start waiting for
session1 commit\rollback
session1
commit;
session2
-- successfully completed. NO ROWS AFFECTED!
-- this means that update from session2 works definitely not like
described in documentation ("The search condition of the command (the WHERE
clause) is re-evaluated")
I would like to see in documentation description of REAL behaviour of
postgres for "second updater".
Or, maybe, it's a bug.. but it's a principal part of isolation mechanism, so
i think that it's just a documentation issue.
i take example from here:
https://franckpachot.medium.com/read-committed-anomalies-in-postgresql-f0d80330a0e0
it's reproduced on PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1)
many thanks!
Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
On Tue, Aug 17, 2021 at 3:56 PM PG Doc comments form
wrote:
>
> "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ...
> ...If the first updater commits, the second updater will ignore the row if
> the first updater deleted it, otherwise it will attempt to apply its
> operation to the updated version of the row. The search condition of the
> command (the WHERE clause) is re-evaluated to see if the updated version of
> the row still matches the search condition."
>
> described in documentation ("The search condition of the command (the WHERE
> clause) is re-evaluated")
>
>
Maybe the nuance was lost and the docs could be improved, but it clearly
says "...to see if the updated version of the rows STILL MATCHES the search
condition" - i.e., it will happily skip a row it thought, before it started
waiting, that it was going to have to update but it will not go looking for
new rows that now may match the criteria. It also won't handle any inserts
by the same reasoning. This is reinforced by the leading sentence:
"...they will only find target rows that were committed as of the command
start time."
David J.
Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
On Tue, Aug 17, 2021 at 4:17 PM radiodiversion wrote: > I still think it would be great if this doc point was worded a little > differently in new editions. > > Suggestions are welcome. Without some idea of why you seemed to miss the two seemingly obvious references that I pointed out (including a fragment you quoted) it's hard to decide what might be an improvement. It's unappealing to consider rewriting the documentation based upon the experiences of a sample size of one without a clear rationale. David J.
