Re: default_text_search_config and expression indexes

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paesold <mpaesold(at)gmx(dot)at>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, Teodor Sigaev <teodor(at)sigaev(dot)ru>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: default_text_search_config and expression indexes
Date: 2007-07-31 08:06:50
Message-ID: Pine.LNX.4.64.0707311202560.18739@sn.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-advocacy pgsql-hackers

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

In response to

Browse pgsql-advocacy by date

  From Date Subject
Next Message Robert Bernier 2007-07-31 11:57:04 Re: Fwd: [cbp] New MySQL Journal
Previous Message Dave Page 2007-07-31 07:55:38 Re: Projects wanted for the .ORG Village at LinuxExpo 2007 - Exhibit for FREE!

Browse pgsql-hackers by date

  From Date Subject
Next Message Dawid Kuroczko 2007-07-31 09:44:04 Re: Machine available for community use
Previous Message Devrim GÜNDÜZ 2007-07-31 07:04:11 Re: Machine available for community use