Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cHqS3-0006VM-AQ for pgsql-sql@arkaria.postgresql.org; Fri, 16 Dec 2016 11:13:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cHqS2-0003iS-Ot for pgsql-sql@arkaria.postgresql.org; Fri, 16 Dec 2016 11:13:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cHqR5-0002eC-QN for pgsql-sql@postgresql.org; Fri, 16 Dec 2016 11:12:23 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84_2) (envelope-from ) id 1cHqQz-00085p-Cy for pgsql-sql@postgresql.org; Fri, 16 Dec 2016 11:12:22 +0000 Received: from hermes ([80.139.209.28]) by mail.gmx.com (mrgmx102 [212.227.17.168]) with ESMTPSA (Nemesis) id 0Lg6op-1cwj6U492x-00pbhQ for ; Fri, 16 Dec 2016 12:12:14 +0100 Received: from ncq by hermes with local (Exim 4.88) (envelope-from ) id 1cHqQv-0007jN-7Z for pgsql-sql@postgresql.org; Fri, 16 Dec 2016 12:12:13 +0100 Date: Fri, 16 Dec 2016 12:12:13 +0100 From: Karsten Hilbert To: pgsql-sql@postgresql.org Subject: Re: simple "select / if found" isn't Message-ID: <20161216111212.edknff56d52bgrjl@hermes.hilbert.loc> References: <201612161102.37559.gary.stainburn@ringways.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <201612161102.37559.gary.stainburn@ringways.co.uk> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) User-Agent: NeoMutt/20161126 (1.7.1) X-Provags-ID: V03:K0:6JkoNGeoTvK34PtJGA7IiI23XVe0/t61snGWc3xnnimxJlWU+se cgSffHZQPSYtQcTcL3+xY6e7TmNmZDQruuVq7LkRCNEIV+KSIQbNjvOhvnQ/RVd4HXjmEOU Oi6D8g2W5wfohAWF9OFYOsgdiPlJkZ+xoeBVP+cXklA9G8aamH/F5XBHNx/11v+w1uRwcO+ BiDvoQHEadVXP/y8bzY1g== X-UI-Out-Filterresults: notjunk:1;V01:K0:naZ1N/Yecrg=:b18/Zad8paJX0Dms83tMQp eoAmyIGBN3AabzThbTjSgbmvisB4ha2T2IFfpe5Eo1JUhrrhmYNFeBH2jclAN8fTeFtGNjlbS ZcS4BBkVRtC5dVu9iKGJk1OHd4BL96ojlE4FYWC54bW5ncU8iuDNQTFFQhQI6N9001WlBLI4T /U2O6XhDTmyBFhQSYRd1j61yk/iE3GfJnD30TgNbFNV8hiPSaGHtjWVGXoDo+aOvRBU1wOIzn tldXCCUh46IsAJtTwW8mudQoIkH/wgepGS6GlbgMwuUrcqrD8khY8hn+t5zluf+vwTHI//l4E juGk8QvYXkPfBc3c145USUizkmP2MPoy+fxLo1ImU0iPzbfzBYyw2lsSBkRBe3nqv4XhbuMUC tQeWwIYgyLF4QGQOPcwVuxfj0wymap77vJZMq/8gMcOVde8kgaeMen77qDoovS+LRM/ezVzF/ TJkl1EvEXGR4XywoXNBURLLaArv2iMak89SNvwc5eSgNDxpANZLzD8DMQN71noFKkg+1g89xW Pb7RRgtvYV3BFBsh9gHM2/ebc2vWpKuUOVF5g6vbtvW2Do9OVrJvthJB+W56DrOMM0J0IUAMV GvJmtc8zAlRxqxSR1QyjGUVcVw0BmjqxJaPVO2SiCQVQTqJe3IH4LUjznUFxqcgchPMkvOk9n 6x20Siuax4tfmbQ+7/H0BS5OgNyNAIZO1bee5PBsxD+edOAXc7qm9Y/fpzwm6EswMA6R34tMP UNWOZCTKuGCkxCcQHnyc+tmEx9E81Uydc7AcVEyyIRmVE6WYJEQINN9qnY4= X-Pg-Spam-Score: -5.7 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On Fri, Dec 16, 2016 at 11:02:37AM +0000, Gary Stainburn wrote: > All I want to do is return 'found' based on the select but I can't get it to > work. > > If I run > > select 1 from user_previous_passwords > where u_id=25 and > crypt('MyPaSSword',u_previous_password) = u_previous_password; > > then it returns the matching row(s) > > If I run my function > > create or replace function check_previous_passwords (ID int4, PASS varchar) > returns boolean as $$ It just _might_ have to do with permissions to user_previous_passwords. If the function somehow got installed as "security definer" and definer does not have RLS-based (!) permissions on user_previous_passwords then it won't find rows. A shot in the dark... Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql