PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

On Thu, Jul 21, 2016 at 10:27 AM, David Fetter <david@fetter.org> wrote:

Folks,

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

The use case for this functionality that comes to mind is to avoid
deleting/updating all the data, if user has accidentally missed the
WHERE clause. Do you have other use case for this functionality?
With this functionality, if user needs to actually delete or update
all the rows, then he has to artificially add where clause which seems
slightly inconvenient, but may be such cases are less.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

On Thu, Jul 21, 2016 at 12:57 AM, David Fetter <david@fetter.org> wrote:

Folks,

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

Can't you implement this as a extension? The SQL Firewall project is
already doing some similar concepts by catching prohibiting SQL and
preventing it from executing.
https://github.com/uptimejp/sql_firewall

On 21 July 2016 at 15:49, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Jul 21, 2016 at 10:27 AM, David Fetter <david@fetter.org> wrote:

Folks,

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

The use case for this functionality that comes to mind is to avoid
deleting/updating all the data, if user has accidentally missed the
WHERE clause. Do you have other use case for this functionality?
With this functionality, if user needs to actually delete or update
all the rows, then he has to artificially add where clause which seems
slightly inconvenient, but may be such cases are less.

It's a commonly requested feature. Personally I think it's kind of silly,
but I've had multiple people ask me for it or how to do it too. So whether
or not it's really effective/useful, it's in demand.

Personally I'd rather see it as part of an extension that does other
filtering, I don't find it compelling for core. But I don't really object
either.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

David Fetter <david@fetter.org> writes:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

-1. This is an express violation of the SQL standard, and at least the
UPDATE case has reasonable use-cases. Moreover, if your desire is to have
training wheels for SQL, there are any number of other well-known gotchas
that are just as dangerous, for example ye olde unintentionally-correlated
subselect:
/messages/by-id/20160714135233.1410.92538@wrigleys.postgresql.org

I wouldn't have any objection to an extension that enforces rules like
these, but I don't think it belongs in core.

regards, tom lane

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

On 07/21/2016 06:49 AM, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

-1

-1. This is an express violation of the SQL standard, and at least the
UPDATE case has reasonable use-cases. Moreover, if your desire is to have
training wheels for SQL, there are any number of other well-known gotchas
that are just as dangerous, for example ye olde unintentionally-correlated
subselect:
/messages/by-id/20160714135233.1410.92538@wrigleys.postgresql.org

Yes but I used to teach a weak long class on relational databases using
PostgreSQL. The entire week I would iterate over and over and over that
you never use an UPDATE or DELETE without a transaction. Toward the end
of the class we would being do problem sets that included UPDATE and
DELETE. Guess how many would trash their data because they didn't use a
WHERE clause AND didn't use a transaction? 50%

These weren't kids, these weren't neophytes to technology. These were
professionals, many of them programmers (PICK).

I wouldn't have any objection to an extension that enforces rules like
these, but I don't think it belongs in core.

I agree it doesn't need to be in core.

JD

regards, tom lane

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

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

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

DELETE FROM tbl WHERE true; ?

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

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

On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

DELETE FROM tbl WHERE true; ?

I specifically left this possible so the feature when turned on allows
people to do updates with an always-true qualifier if that's what they
actually mean to do.

In case it wasn't clear, unqualified updates and deletes are permitted
by default. This patch allows people to set it so they're disallowed.

Best,
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

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

On Thu, Jul 21, 2016 at 12:39 PM, David Fetter <david@fetter.org> wrote:

On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

DELETE FROM tbl WHERE true; ?

I specifically left this possible so the feature when turned on allows
people to do updates with an always-true qualifier if that's what they
actually mean to do.

In case it wasn't clear, unqualified updates and deletes are permitted
by default. This patch allows people to set it so they're disallowed.

I join with others in thinking it's a reasonable contrib module. In
fact, I already wrote it for my 2015 PGCon tutorial. Well, the
"delete" part, anyway.

https://github.com/robertmhaas/introduction-to-postgresql-hacking/compare/master...robertmhaas:delete_needs_where

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On Thu, Jul 21, 2016 at 09:21:55AM -0400, Jim Mlodgenski wrote:

On Thu, Jul 21, 2016 at 12:57 AM, David Fetter <david@fetter.org> wrote:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes
query behavior, I've made the new GUCs PGC_SUSET.

What say?

Can't you implement this as a extension?

Yes. In that case, I'd want to make it a contrib extension, as it is
at least in theory attached to specific major versions of the backend.

Also, if it's not in contrib, we can basically forget about having
most people even know about it, let alone get specific separate
permission to use it in production. That's reality, much as I would
like it not to be.

The SQL Firewall project is already doing some similar concepts by
catching prohibiting SQL and preventing it from executing.
https://github.com/uptimejp/sql_firewall

That's very nice, but it illustrates my point perfectly. The
extension is from a current respected and prolific contributor to the
community, but I had no idea that it was there, and by dint of writing
the PostgreSQL Weekly News, I keep closer tabs on external things
PostgreSQL than easily 99.9% of people who deploy it.

Best,
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

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

At 2016-07-21 12:46:29 -0400, robertmhaas@gmail.com wrote:

I join with others in thinking it's a reasonable contrib module.

I don't like the use of the term "empty" to describe an UPDATE or DELETE
without a WHERE clause.

-- Abhijit

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

On Thu, Jul 21, 2016 at 12:49 PM, Abhijit Menon-Sen <ams@2ndquadrant.com> wrote:

At 2016-07-21 12:46:29 -0400, robertmhaas@gmail.com wrote:

I join with others in thinking it's a reasonable contrib module.

I don't like the use of the term "empty" to describe an UPDATE or DELETE
without a WHERE clause.

/me scratches head.

Who used that term?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On Thu, Jul 21, 2016 at 12:46:29PM -0400, Robert Haas wrote:

On Thu, Jul 21, 2016 at 12:39 PM, David Fetter <david@fetter.org> wrote:

On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

DELETE FROM tbl WHERE true; ?

I specifically left this possible so the feature when turned on allows
people to do updates with an always-true qualifier if that's what they
actually mean to do.

In case it wasn't clear, unqualified updates and deletes are permitted
by default. This patch allows people to set it so they're disallowed.

I join with others in thinking it's a reasonable contrib module. In
fact, I already wrote it for my 2015 PGCon tutorial. Well, the
"delete" part, anyway.

https://github.com/robertmhaas/introduction-to-postgresql-hacking/compare/master...robertmhaas:delete_needs_where

I'm happy to write the rest of this as a contrib module. I hope to
get to that this evening.

Best,
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

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

On Thu, Jul 21, 2016 at 12:51:50PM -0400, Robert Haas wrote:

On Thu, Jul 21, 2016 at 12:49 PM, Abhijit Menon-Sen <ams@2ndquadrant.com> wrote:

At 2016-07-21 12:46:29 -0400, robertmhaas@gmail.com wrote:

I join with others in thinking it's a reasonable contrib module.

I don't like the use of the term "empty" to describe an UPDATE or DELETE
without a WHERE clause.

/me scratches head.

Who used that term?

I did out of failure to imagine another short way to describe the
situation as I was writing it up. I'd be delighted to change it to
something else.

Best,
David.

Oh, and the bike shed should definitely be puce with blaze orange
polka dots.
--
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

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

On 7/21/16 11:46 AM, David Fetter wrote:

Can't you implement this as a extension?

Yes. In that case, I'd want to make it a contrib extension, as it is
at least in theory attached to specific major versions of the backend.

Howso?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461

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

On Thu, Jul 21, 2016 at 04:48:37PM -0500, Jim Nasby wrote:

On 7/21/16 11:46 AM, David Fetter wrote:

Can't you implement this as a extension?

Yes. In that case, I'd want to make it a contrib extension, as it is
at least in theory attached to specific major versions of the backend.

Howso?

At least one of the structures it references isn't in a public API.

Best,
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

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

On Thu, Jul 21, 2016 at 09:52:26AM -0700, David Fetter wrote:

On Thu, Jul 21, 2016 at 12:46:29PM -0400, Robert Haas wrote:

On Thu, Jul 21, 2016 at 12:39 PM, David Fetter <david@fetter.org> wrote:

On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote:

Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.

What say?

DELETE FROM tbl WHERE true; ?

I specifically left this possible so the feature when turned on allows
people to do updates with an always-true qualifier if that's what they
actually mean to do.

In case it wasn't clear, unqualified updates and deletes are permitted
by default. This patch allows people to set it so they're disallowed.

I join with others in thinking it's a reasonable contrib module. In
fact, I already wrote it for my 2015 PGCon tutorial. Well, the
"delete" part, anyway.

https://github.com/robertmhaas/introduction-to-postgresql-hacking/compare/master...robertmhaas:delete_needs_where

I'm happy to write the rest of this as a contrib module. I hope to
get to that this evening.

I've renamed it to require_where and contrib-ified.

Best,
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

On Fri, Jul 22, 2016 at 2:38 AM, David Fetter <david@fetter.org> wrote:

I've renamed it to require_where and contrib-ified.

I'm not sure that the Authors section is entirely complete.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On Mon, Jul 25, 2016 at 11:12:24PM -0400, Robert Haas wrote:

On Fri, Jul 22, 2016 at 2:38 AM, David Fetter <david@fetter.org> wrote:

I've renamed it to require_where and contrib-ified.

I'm not sure that the Authors section is entirely complete.

Does this suit?

Best,
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

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

On Mon, Jul 25, 2016 at 11:38 PM, David Fetter <david@fetter.org> wrote:

On Mon, Jul 25, 2016 at 11:12:24PM -0400, Robert Haas wrote:

On Fri, Jul 22, 2016 at 2:38 AM, David Fetter <david@fetter.org> wrote:

I've renamed it to require_where and contrib-ified.

I'm not sure that the Authors section is entirely complete.

Does this suit?

YFTATP.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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