Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux