From: pf@pfortin.com
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Aren't regex_*() functions built-in?
Date: Sat, 11 Nov 2023 21:05:23 -0500
Message-ID: <20231111210523.3cdc3a2e.pfortin@pfortin.com> (raw)
In-Reply-To: <20231111202051.5f1f1acc@pfortin.com>
References: <20231111191214.60d182b2.pfortin@pfortin.com>
<03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com>
<20231111192548.55392ea9@pfortin.com>
<a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com>
<20231111200417.539bebb0@pfortin.com>
<152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com>
<20231111202051.5f1f1acc@pfortin.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: pf@pfortin.com
Subject: Re: Aren't regex_*() functions built-in?
In-Reply-To: <20231111210523.3cdc3a2e.pfortin@pfortin.com>
References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> <a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com> <20231111200417.539bebb0@pfortin.com> <152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com> <20231111202051.5f1f1acc@pfortin.com> <20231111210523.3cdc3a2e.pfortin@pfortin.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox