Re: default_text_search_config and expression indexes

On Mon, 30 Jul 2007, Bruce Momjian wrote:

> Oleg Bartunov wrote:
>>> OK, here is what I am thinking. If we make default_text_search_config
>>> super-user-only, then the user can't do SET (using "zero_damaged_pages"
>>> as a superuser-only example):
>>>
>>> test=> set zero_damaged_pages = on;
>>> ERROR: permission denied to set parameter "zero_damaged_pages"
>>>
>>> test=> alter user guest set zero_damaged_pages = on;
>>> ERROR: permission denied to set parameter "zero_damaged_pages"
>>>
>>> but the super-user can set it in postgresql.conf, or:
>>>
>>> test=# alter user guest set zero_damaged_pages = on;
>>> ALTER ROLE
>>>
>>> or
>>>
>>> test=# alter database vendor3 set zero_damaged_pages = on;
>>> ALTER ROLE
>>>
>>> meaning while it will be super-user-only, the administrator can set the
>>> default for specific databases and users. Is that the best approach?
>>>
>>> A user can still over-ride the default by specifying the configuration
>>> in the function call.
>>
>> This is ok, but it will not work in hosting environment and still
>> doesn't prevent errors.
>
> Agreed. super-user-only now seems strange to me because it isn't a
> security issue, but rather an attempt to avoid people causing errors.
>
> The fundamental issue is that if you do a query using tsvector and
> tsquery everything will work find because default_text_search_config
> will be the same for both queries. The problem is if do an expression
> index lookup that doesn't specify the configuration name and your
> default_text_search_config doesn't match the index, or you INSERT or
> UPDATE into an expression index with a mismatched
> default_text_search_config.
>
> If we do make default_text_search_config super-user-only it prevents a
> database owner from doing ALTER DATABASE db1 SET
> default_text_search_config = 'english', which seems like a pretty big
> limitation because I think per-database default_text_search_config makes
> the most sense.
>
> And, again, if you specify the configuration in the expression index you
> have to specify it in the WHERE clause and then
> default_text_search_config is pretty useless.

agree. Notice, this is very limited usage case.

>
> If we required the configuration to always be specified, you could still
> store multiple configurations in the same column by having a secondary
> column hold the configuration name:

I don't understand this. Let's don't discuss indexes at all, since indexes
doesn't know about configuratons at all

>
> CREATE INDEX i on x USING gist (to_tsvector(config_col, body));

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83