Re: [GENERAL] [HACKERS] Who is pgFoundery administrator?

[Andrew Gierth]

> "Merlin" == Merlin Moncure  writes:

 >> Who is pgFoundery administrator or board member now? I would like
 >> to send e-mail them. At least, it does not have information and
 >> support page in pgFoundery homepage.

 Merlin> I have not been able to get in contact with Marc either to
 Merlin> help resolve a mailing list administration issue.  Github is
 Merlin> nice, but a lot of projects are still hosted on pgfoundry and
 Merlin> it kind of sucks to be forced to move on this basis.

Also, many pgfoundry projects had all their admins and members removed
leaving them empty; no attempt seems to have been made to restore this
(surely any project with no admins could have had them restored from a
backup, rather than expecting every individual user to complain about
it?)

The help-with-pgfoundry forum was also broken last I looked.

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] Updating column on row update

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 > Thom Brown  writes:
 >> As for having plpgsql installed by default, are there any security
 >> implications?

 Tom> Well, that's pretty much exactly the question --- are there?  It
 Tom> would certainly make it easier for someone to exploit any other
 Tom> security weakness they might find.  I believe plain SQL plus SQL
 Tom> functions is Turing-complete, but that doesn't mean it's easy or
 Tom> fast to write loops etc in it.

Now that we have recursive CTEs, plain SQL is turing-complete without
requiring functions.

(Yes, I did actually prove this a while back, by implementing one of
the known-Turing-complete tag system automata as a single recursive
query. This proof is pretty boring, though, because you wouldn't
actually _use_ that approach in practice.)

Loops in plain SQL are no problem: see generate_series. The last time
we discussed this I demonstrated reasonably straightforward SQL
examples of how to do things like password-cracking (and that was long
before we had CTEs, so it would be even easier now); my challenge to
anyone to produce examples of malicious plpgsql code that couldn't be
reproduced in plain SQL went unanswered.

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] Updating column on row update

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 >> Loops in plain SQL are no problem: see generate_series. The last
 >> time we discussed this I demonstrated reasonably straightforward
 >> SQL examples of how to do things like password-cracking (and that
 >> was long before we had CTEs, so it would be even easier now); my
 >> challenge to anyone to produce examples of malicious plpgsql code
 >> that couldn't be reproduced in plain SQL went unanswered.

 Tom> The fact remains though that the looping performance of anything
 Tom> you can cons up in straight SQL will be an order of magnitude
 Tom> worse than in plpgsql;

Well, let's see. How about generating all possible strings of 6 characters
from A-Z? We'll just count the results for now:

select count(chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26)
 ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26))
  from generate_series(0,17575) i, generate_series(0,17575) j;
   count   
---
 308915776
(1 row)

Time: 462570.563 ms

create function foo() returns bigint language plpgsql
 as $f$
  declare
c bigint := 0;
s text;
  begin
for i in 0..17575 loop
  for j in 0..17575 loop
s := chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26)
 ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26);
c := c + 1;
  end loop;
end loop;
return c;
  end;
$f$;

select foo();
foo
---
 308915776
(1 row)

Time: 624809.671 ms

plpgsql comes out 35% _slower_, not "an order of magnitude worse".

-- 
Andrew.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 > Andrew Dunstan  writes:
 >> Before we go too far with this, I'd like to know how we will handle the 
 >> problems outlined here: 
 >> 

 Tom> Hm, I think that's only a problem if we define it to be a
 Tom> problem, and I'm not sure it's necessary to do so.  Currently,
 Tom> access to PL languages is controlled by superusers.  You are
 Tom> suggesting that if plpgsql is installed by default, then access
 Tom> to it should be controlled by non-superuser DB owners instead.

Currently, a non-superuser db owner can install plpgsql, and having
installed it, can DROP it or grant/revoke access to it:

test=> create language plpgsql;
CREATE LANGUAGE
test=> revoke usage on language plpgsql from public;
REVOKE
test=> drop language plpgsql;
DROP LANGUAGE

The complaint is that if plpgsql is installed by default, then it will
be owned by postgres rather than by the db owner, who will then not be
able to drop it or use grant/revoke on it.

(This only became an issue since the ability for non-superuser DB owners
to install languages from pltemplate was added.)

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general