Re: Crash in pgCrypto?

Could someone using the pgcrypto extension please verify this?

SELECT encode(digest(null, 'md5'::text), 'hex');
or
SELECT digest(null, 'md5');

Takes a few seconds, and then crashes the server with a Signal 11. My
system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
location 0.

Best regards,
Mario Weilguni

On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
> Could someone using the pgcrypto extension please verify this?
>
> SELECT encode(digest(null, 'md5'::text), 'hex');
> or
> SELECT digest(null, 'md5');
>
> Takes a few seconds, and then crashes the server with a Signal 11. My
> system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
> location 0.

Seems you loaded pgcrypto function signatures from dump (from 8.0).

http://marc.info/?l=postgresql-general&m=118794006505296&w=2

Recreate the functions with pgcrypto.sql.

--
marko

Marko Kreen schrieb:
> On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
>
>> Could someone using the pgcrypto extension please verify this?
>>
>> SELECT encode(digest(null, 'md5'::text), 'hex');
>> or
>> SELECT digest(null, 'md5');
>>
>> Takes a few seconds, and then crashes the server with a Signal 11. My
>> system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
>> location 0.
>>
>
> Seems you loaded pgcrypto function signatures from dump (from 8.0).
>
> http://marc.info/?l=postgresql-general&m=118794006505296&w=2
>
> Recreate the functions with pgcrypto.sql.
>
>
Yes, this is what I did, and it happened during update from 8.0 --> 8.2.
But that brings me to another problem, what is the best way to create
backups with pgdump so that stuff from contrib is not dumped, but
recreated from the newer version?

Thanks!

On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
> Marko Kreen schrieb:
> > On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
> > > Could someone using the pgcrypto extension please verify this?
> > >
> > > SELECT encode(digest(null, 'md5'::text), 'hex');
> > > or
> > > SELECT digest(null, 'md5');
> > >
> > > Takes a few seconds, and then crashes the server with a Signal 11. My
> > > system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
> > > location 0.
> >
> > Seems you loaded pgcrypto function signatures from dump (from 8.0).
> >
> >
> http://marc.info/?l=postgresql-general&m=118794006505296&w=2
> >
> > Recreate the functions with pgcrypto.sql.
> >
> Yes, this is what I did, and it happened during update from 8.0 --> 8.2.
> But that brings me to another problem, what is the best way to create
> backups with pgdump so that stuff from contrib is not dumped, but recreated
> from the newer version?

Good question... Seems the proper support for modules will not
leave todo-list any time soon.

Only way that works now is to add any module .sql to template0, so
they would not be dumped out. So you are forced to recreate them
properly on newer version. (By adding them to template0 again.)

--
marko

Marko Kreen schrieb:
> On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
>
>> Marko Kreen schrieb:
>>
>>> On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
>>>
>>>> Could someone using the pgcrypto extension please verify this?
>>>>
>>>> SELECT encode(digest(null, 'md5'::text), 'hex');
>>>> or
>>>> SELECT digest(null, 'md5');
>>>>
>>>> Takes a few seconds, and then crashes the server with a Signal 11. My
>>>> system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
>>>> location 0.
>>>>
>>> Seems you loaded pgcrypto function signatures from dump (from 8.0).
>>>
>>>
>>>
>> http://marc.info/?l=postgresql-general&m=118794006505296&w=2
>>
>>> Recreate the functions with pgcrypto.sql.
>>>
>>>
>> Yes, this is what I did, and it happened during update from 8.0 --> 8.2.
>> But that brings me to another problem, what is the best way to create
>> backups with pgdump so that stuff from contrib is not dumped, but recreated
>> from the newer version?
>>
>
> Good question... Seems the proper support for modules will not
> leave todo-list any time soon.
>
> Only way that works now is to add any module .sql to template0, so
> they would not be dumped out. So you are forced to recreate them
> properly on newer version. (By adding them to template0 again.)
>
>
Is this todo-list something I can find online?

Best regards
Mario Weilguni

On 6/16/08, Mario Weilguni <mweilguni(at)sime(dot)com> wrote:
> Is this todo-list something I can find online?

postgresql.org -> Developers -> TODO list:

http://www.postgresql.org/docs/faqs.TODO.html

--
marko

Marko Kreen wrote:
>
> Good question... Seems the proper support for modules will not
> leave todo-list any time soon.
>
> Only way that works now is to add any module .sql to template0, so
> they would not be dumped out. So you are forced to recreate them
> properly on newer version. (By adding them to template0 again.)
>
>

Module install/uninstall is being worked on. In fact this case shows
that it's something we really need, rather than just something that
would be nice to have, IMNSHO.

cheers

andrew

Am Montag, 16. Juni 2008 schrieb Mario Weilguni:
> Is this todo-list something I can find online?

Yes, google for postgresql+todo+list.

Andrew Dunstan schrieb:
>
>
> Marko Kreen wrote:
>>
>> Good question... Seems the proper support for modules will not
>> leave todo-list any time soon.
>>
>> Only way that works now is to add any module .sql to template0, so
>> they would not be dumped out. So you are forced to recreate them
>> properly on newer version. (By adding them to template0 again.)
>>
>>
>
> Module install/uninstall is being worked on. In fact this case shows
> that it's something we really need, rather than just something that
> would be nice to have, IMNSHO.
>
> cheers
>
> andrew
>
Yes, this is really a pitfall when doing security related updates.

Best regards,
Mario Weilguni

Mario Weilguni wrote:
> Could someone using the pgcrypto extension please verify this?
>
> SELECT encode(digest(null, 'md5'::text), 'hex');
> or
> SELECT digest(null, 'md5');
>
> Takes a few seconds, and then crashes the server with a Signal 11. My
> system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
> location 0.

I think the functions were made STRICT recently, and the NULL checks
were removed, but people with the old definitions of the functions could
see the crashes. Try removing pgcrypto and recompiling it from a fresh
release.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Monday 16 June 2008 09:54:16 Alvaro Herrera wrote:
> Mario Weilguni wrote:
> > Could someone using the pgcrypto extension please verify this?
> >
> > SELECT encode(digest(null, 'md5'::text), 'hex');
> > or
> > SELECT digest(null, 'md5');
> >
> > Takes a few seconds, and then crashes the server with a Signal 11. My
> > system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
> > location 0.
>
> I think the functions were made STRICT recently, and the NULL checks
> were removed, but people with the old definitions of the functions could
> see the crashes. Try removing pgcrypto and recompiling it from a fresh
> release.
>

<broken record>
I still advocate to folks to try to put contrib modules into thier own schemas
whenever possible, so that you have the option of doing pg_dump -N
contribmodule, makes things like this much easier to work around. (And yes,
I've volunteered to patch the contribs with this if we ever decide to make it
the default setup)
</broken record>

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Mon, Jun 16, 2008 at 02:48:42PM -0400, Robert Treat wrote:
> On Monday 16 June 2008 09:54:16 Alvaro Herrera wrote:
> > Mario Weilguni wrote:
> > > Could someone using the pgcrypto extension please verify this?
> > >
> > > SELECT encode(digest(null, 'md5'::text), 'hex');
> > > or
> > > SELECT digest(null, 'md5');
> > >
> > > Takes a few seconds, and then crashes the server with a Signal 11. My
> > > system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
> > > location 0.
> >
> > I think the functions were made STRICT recently, and the NULL checks
> > were removed, but people with the old definitions of the functions could
> > see the crashes. Try removing pgcrypto and recompiling it from a fresh
> > release.
>
> <broken record>
> I still advocate to folks to try to put contrib modules into thier
> own schemas whenever possible, so that you have the option of doing
> pg_dump -N contribmodule, makes things like this much easier to work
> around. (And yes, I've volunteered to patch the contribs with this
> if we ever decide to make it the default setup)
> </broken record>

I, too, would be happy to do the legwork on this one. I believe we'd
want to have both per-db and per-role settings for search_path.
What's involved with creating that latter?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

> I, too, would be happy to do the legwork on this one. I believe we'd
> want to have both per-db and per-role settings for search_path.
> What's involved with creating that latter?

I'm not sure what's your point here, but you can already use
ALTER ROLE foo SET search_path=...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

David Fetter wrote:
> On Mon, Jun 16, 2008 at 02:48:42PM -0400, Robert Treat wrote:
>
>> On Monday 16 June 2008 09:54:16 Alvaro Herrera wrote:
>>
>>> Mario Weilguni wrote:
>>>
>>>> Could someone using the pgcrypto extension please verify this?
>>>>
>>>> SELECT encode(digest(null, 'md5'::text), 'hex');
>>>> or
>>>> SELECT digest(null, 'md5');
>>>>
>>>> Takes a few seconds, and then crashes the server with a Signal 11. My
>>>> system is PostgreSQL 8.2.7. Seems to be an unchecked access to memory
>>>> location 0.
>>>>
>>> I think the functions were made STRICT recently, and the NULL checks
>>> were removed, but people with the old definitions of the functions could
>>> see the crashes. Try removing pgcrypto and recompiling it from a fresh
>>> release.
>>>
>> <broken record>
>> I still advocate to folks to try to put contrib modules into thier
>> own schemas whenever possible, so that you have the option of doing
>> pg_dump -N contribmodule, makes things like this much easier to work
>> around. (And yes, I've volunteered to patch the contribs with this
>> if we ever decide to make it the default setup)
>> </broken record>
>>
>
> I, too, would be happy to do the legwork on this one. I believe we'd
> want to have both per-db and per-role settings for search_path.
> What's involved with creating that latter?
>
>
>

Proper support for module install / uninstall will be a far better
solution. Why would you wast your time on something that will be at best
half-baked?

cheers

andrew

On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote:
>> I, too, would be happy to do the legwork on this one. I believe
>> we'd want to have both per-db and per-role settings for
>> search_path. What's involved with creating that latter?
>
> Proper support for module install / uninstall will be a far better
> solution. Why would you wast your time on something that will be at
> best half-baked?

Maybe I'm missing something big, but I don't quite see what
constitutes "proper" that doesn't involve the module's having at least
one schema to itself. Does this mean we'd be freezing modules in
their first-deployed form? It seems to me that DROP SCHEMA ...
CASCADE is just the right level of modularity combined with
flexibility post-installation.

The way I've structured DBI-Link, for example, involves one schema for
DBI-Link itself, modifiable by the DB superuser, and ancillary schemas
for each link. Come to think of it, it would be nice if it were
possible to tell pg_depend about such relationships between schemas,
so that when somebody drops a schema with CASCADE, all schemas marked
as depending on it also disappear...

As to why you'd want per-role, per-DB search_paths, right now, you can
set them only per-role, which results in an annoying number of "path
not found" warnings should a user switch to a DB in the cluster which
doesn't contain all the schemas in its default search_path. Another
way would be for people to be able to set <flame-proof_suit>some
kind(s) of configurable action(s) on CONNECT</>.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote:
>
>>> I, too, would be happy to do the legwork on this one. I believe
>>> we'd want to have both per-db and per-role settings for
>>> search_path. What's involved with creating that latter?
>>>
>> Proper support for module install / uninstall will be a far better
>> solution. Why would you wast your time on something that will be at
>> best half-baked?
>>
>
> Maybe I'm missing something big, but I don't quite see what
> constitutes "proper" that doesn't involve the module's having at least
> one schema to itself. Does this mean we'd be freezing modules in
> their first-deployed form? It seems to me that DROP SCHEMA ...
> CASCADE is just the right level of modularity combined with
> flexibility post-installation.
>

ISTM that "uninstall foomodule" will be a whole lot nicer.

If we record all the objects that the module contains, then we would
just drop them.

The module could involve one schema, or several schemas, or none.

Maybe that's the "something big".

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> David Fetter wrote:
>> Maybe I'm missing something big, but I don't quite see what
>> constitutes "proper" that doesn't involve the module's having at least
>> one schema to itself.

> ISTM that "uninstall foomodule" will be a whole lot nicer.

Right. We have all the mechanism we need in the form of the dependency
stuff: you just make everything in the module auto-depend on the module
object. People who want to put their modules into private schemas can
do it, but they won't be forced to.

In any case, trying to define a module as a schema doesn't help at all
to solve the hard problem, which is how to get this stuff to play nice
with pg_dump. I think that the agreed-on solution was that pg_dump
should emit some kind of "LOAD MODULE foo" command, and *not* dump any
of the individual objects in the module. We can't have that if we try
to equate modules with schemas instead of making them a new kind of
object.

regards, tom lane

On Monday 16 June 2008 21:12:23 Andrew Dunstan wrote:
> David Fetter wrote:
> > On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote:
> >>> I, too, would be happy to do the legwork on this one. I believe
> >>> we'd want to have both per-db and per-role settings for
> >>> search_path. What's involved with creating that latter?
> >>
> >> Proper support for module install / uninstall will be a far better
> >> solution. Why would you wast your time on something that will be at
> >> best half-baked?
> >
> > Maybe I'm missing something big, but I don't quite see what
> > constitutes "proper" that doesn't involve the module's having at least
> > one schema to itself. Does this mean we'd be freezing modules in
> > their first-deployed form? It seems to me that DROP SCHEMA ...
> > CASCADE is just the right level of modularity combined with
> > flexibility post-installation.
>
> ISTM that "uninstall foomodule" will be a whole lot nicer.
>
> If we record all the objects that the module contains, then we would
> just drop them.
>
> The module could involve one schema, or several schemas, or none.
>
> Maybe that's the "something big".
>

I think individual schemas is nicer, since it has helped me getting around
these problems for years now, while module support is still vaporware.
However, I am looking forward to your patch. :-)

BTW, I am suspecting part of your support will be giving pg_dump -m and -M
flags to control dumping or ignoring of specific modules?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> On Monday 16 June 2008 21:12:23 Andrew Dunstan wrote:
>
>> David Fetter wrote:
>>
>>> On Mon, Jun 16, 2008 at 06:00:33PM -0400, Andrew Dunstan wrote:
>>>
>>>>> I, too, would be happy to do the legwork on this one. I believe
>>>>> we'd want to have both per-db and per-role settings for
>>>>> search_path. What's involved with creating that latter?
>>>>>
>>>> Proper support for module install / uninstall will be a far better
>>>> solution. Why would you wast your time on something that will be at
>>>> best half-baked?
>>>>
>>> Maybe I'm missing something big, but I don't quite see what
>>> constitutes "proper" that doesn't involve the module's having at least
>>> one schema to itself. Does this mean we'd be freezing modules in
>>> their first-deployed form? It seems to me that DROP SCHEMA ...
>>> CASCADE is just the right level of modularity combined with
>>> flexibility post-installation.
>>>
>> ISTM that "uninstall foomodule" will be a whole lot nicer.
>>
>> If we record all the objects that the module contains, then we would
>> just drop them.
>>
>> The module could involve one schema, or several schemas, or none.
>>
>> Maybe that's the "something big".
>>
>>
>
> I think individual schemas is nicer, since it has helped me getting around
> these problems for years now, while module support is still vaporware.
> However, I am looking forward to your patch. :-)
>

Perhaps you have missed the WIP patch for module install/uninstall that
has already been submitted (not by me, by Tom Dunstan).

Tom Lane has already pointed out why the schema idea is bad. I agree
with every word he wrote.

> BTW, I am suspecting part of your support will be giving pg_dump -m and -M
> flags to control dumping or ignoring of specific modules?
>
>
s/your/his/. Possibly.

cheers

andrew

On Mon, Jun 16, 2008 at 09:43:37PM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > David Fetter wrote:
> >> Maybe I'm missing something big, but I don't quite see what
> >> constitutes "proper" that doesn't involve the module's having at
> >> least one schema to itself.
>
> > ISTM that "uninstall foomodule" will be a whole lot nicer.
>
> Right. We have all the mechanism we need in the form of the
> dependency stuff: you just make everything in the module auto-depend
> on the module object. People who want to put their modules into
> private schemas can do it, but they won't be forced to.

It's not quite that simple. Let's say you're *developing* a module.
I don't see any way to play with it in the separate module proposal,
where I *do* see a whole extra non-orthogonal feature where none is
needed. No way to do optional submodules, either, and I'm sure there
are plenty of other nasty limitations.

Here's how what I'm proposing would work:

1. Create a way for schemas themselves to depend on other schemas,
*not* on the stuff inside. This would make dependency an extremely
simple problem, which is to say that DROP SCHEMA my_app CASCADE would
cause anything depending on it, all the way down to the leaves in the
DAG, to get dropped. Without CASCADE, it would Do The Right
Thing™, i.e. throw an error.

2. Create a way to readjust search_paths per-db and per-role, as
previously proposed.

3. Create wrappers like (UN)INSTALL MODULE using the two tools above.
No, they would not necessarily appear in pg_dump.

Tom, please don't paint us into a corner.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

> It's not quite that simple. Let's say you're *developing* a module.
> I don't see any way to play with it in the separate module proposal,
> where I *do* see a whole extra non-orthogonal feature where none is
> needed. No way to do optional submodules, either, and I'm sure there
> are plenty of other nasty limitations.

Maybe what's needed here is just some more additional commands (i.e.
"add this function to the module", "this module is dependent on this
other module").

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Maybe what's needed here is just some more additional commands (i.e.
> "add this function to the module", "this module is dependent on this
> other module").

Yeah. Didn't we have this discussion already?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Maybe what's needed here is just some more additional commands (i.e.
> > "add this function to the module", "this module is dependent on this
> > other module").
>
> Yeah. Didn't we have this discussion already?

I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, Jun 17, 2008 at 11:00:31AM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > Maybe what's needed here is just some more additional commands
> > > (i.e. "add this function to the module", "this module is
> > > dependent on this other module").
> >
> > Yeah. Didn't we have this discussion already?
>
> I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P

Everything is my fault :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

>>> Yeah. Didn't we have this discussion already?
>> I don't know -- I skipped it. Sorry. Blame it on Dave Fetter :-P
>
> Everything is my fault :)

You finally understand!

Joshua D. Drake

>
> Cheers,
> David.

David Fetter <david(at)fetter(dot)org> writes:
> It's not quite that simple. Let's say you're *developing* a module.
> I don't see any way to play with it in the separate module proposal,
> where I *do* see a whole extra non-orthogonal feature where none is
> needed.

The claim that no new feature is needed is complete rubbish. The
*main* thing that we need to get out of a module concept is to have
pg_dump know that it should not dump objects that are part of a
module (at least in the default case). That can't be the behavior
for schemas.

You could imagine implementing modules as specially marked schemas,
perhaps, but I don't see any particular advantage to that. In
particular, I don't want to force people to play around with
search_path in order to use modules.

> Here's how what I'm proposing would work:

> 1. Create a way for schemas themselves to depend on other schemas,
> *not* on the stuff inside.

That does not actually solve any problem we need solved.

regards, tom lane

Coming to this thread a bit late as I've been out of email
connectivity for the past week...

On Tue, Jun 17, 2008 at 2:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> In any case, trying to define a module as a schema doesn't help at all
> to solve the hard problem, which is how to get this stuff to play nice
> with pg_dump. I think that the agreed-on solution was that pg_dump
> should emit some kind of "LOAD MODULE foo" command, and *not* dump any
> of the individual objects in the module. We can't have that if we try
> to equate modules with schemas instead of making them a new kind of
> object.

This is certainly the end result that I'd like, and intend to work
towards. My main concern has been cases where a module-owned table
gets updated with data that would not be recreated/updated by the LOAD
MODULE in the dump. PostGIS support tables are one example of this,
PL/Java classpath / function information is another. There are
probably many more.

I see two potential solutions:

a) explicitly mark such tables as requiring data to be dumped somehow,
and have pg_dump emit "upsert" statements for all rows in the table.

b) allow modules to define a function that can pg_dump can call to
emit appropriate extra restore commands, above whatever LOAD MODULE
foo does. This has the downside of requiring more work from module
owners (though perhaps a default function that effectively does option
a) could be provided), with a potential upside of allowing module
dumps to become upgrade-friendly by not being tied to a particular
version's table layout.

Thoughts?

Tom