Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1zqz-00EcKE-SQ for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 02:05:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r1zqx-009Pjy-ID for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 02:05:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1zqx-009Pjq-7C for pgsql-general@lists.postgresql.org; Sun, 12 Nov 2023 02:05:35 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1zqp-005z7M-Ka for pgsql-general@postgresql.org; Sun, 12 Nov 2023 02:05:34 +0000 Received: from pfortin.com (h241.116.117.75.dynamic.ip.windstream.net [75.117.116.241]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 3AC25NON090343 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Sun, 12 Nov 2023 02:05:24 GMT Date: Sat, 11 Nov 2023 21:05:23 -0500 From: pf@pfortin.com Cc: pgsql-general Subject: Re: Aren't regex_*() functions built-in? Message-ID: <20231111210523.3cdc3a2e.pfortin@pfortin.com> In-Reply-To: <20231111202051.5f1f1acc@pfortin.com> References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> <20231111200417.539bebb0@pfortin.com> <152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com> <20231111202051.5f1f1acc@pfortin.com> X-Mailer: Claws Mail 4.1.1git67 (GTK 3.24.38; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Lux-Comment: Message 3AC25NON090343 sent by user #22427 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:cc:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=zmZtmMVt1nSE7X5yamYfH0N0kU59QMbdSsFUtMFIWaY=; b=tZxkzOdg8Qab9j3v5cLVWnxLVhx5LWeXciQzddY8pz9hUB0tO1D610QBB1wX8tWmm936H7BVsMUw3P/VOqNgAqCwjBPjbJRKSkgVsFpirPhk01hQc0OpPg4gu0aA00qnlGrt8mYMNfWgrMr4pjYUo2sOvN+cNq/LO0wE+8nfmGE= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I need to follow my own advice: slow-down and you'll go faster... Logged in as "postgres" (superuser). I see regexp_replace(); but not if logged in as a read-only user... So this is a permissions issue... I just discovered that a RO user with only SELECT permision can run a query using regexp_replace... Your other message just arrived: >1) Functions do have permissions: > >https://www.postgresql.org/docs/current/ddl-priv.html > >EXECUTE > > Allows calling a function or procedure, including use of any >operators that are implemented on top of the function. This is the only >type of privilege that is applicable to functions and procedures. Interesting... I'm connected as a user which is part of the "ro_users" group which has only SELECT permission, nothing else; yet, I can run this query (finally :) now that the tables are granted "ro_users" SELECT... my bad on that... but: select * from a,b where regexp_replace(a.address,' ','','g') = regexp_replace(b.address,' ','','g'); works. Apparently, EXECUTE is not required: to be sure, I ran this on both tables: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='a'; Which respond with: ro_users SELECT If EXECUTE is really supposed to be required; is it a bug that I finally got regexp_replace() to work...? So I was failing due to permissions (there were none on the tables); but applying only SELECT allowed it to work without EXECUTE which should be required according to the manual...? This is beyond my current skills which greatly advance tonight... THANKS!! Pierre