Re: Aren't regex_*() functions built-in?

On 11/11/23 17:20, pf(at)pfortin(dot)com wrote:
> On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote:
>

>
> Actually, it's more eusbtle... I can make it work as "postgres"; but not
> as a RO user (SELECT only):
> An error occurred when executing the SQL command:
> select * from a,b where regexp_replace(a.address,' ','','g') = regexp_replace(b.address,' ','','g')
>
> ERROR: permission denied for table a
> 1 statement failed.
>
> I had no idea functions need permissions... GRANT EXTENSION..?

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.

2) This is not the problem here

select * from a,b;

would result in the same error.

3) This has nothing to do with extensions.

>
>>> ERROR: function regex_replace(text, unknown, unknown, unknown) does not
>>> exist Hint: No function matches the given name and argument types. You
>>> might need to add explicit type casts. Position: 27
>>>
>>> Looks like rexexp_* need to be installed in each database I use...
>>>
>>> The question is how to use them from public where I get the above
>>> error...?
>>>
>>>
>>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com