Skip site navigation (1) Skip section navigation (2)

Re: default_text_search_config and expression indexes

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
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-30 12:47:46
Message-ID: 200707301247.l6UClku23716@momjian.us (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
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.

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:

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

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-07-30 19:20:48
Subject: Reducing Transaction Start/End Contention
Previous:From: Magnus HaganderDate: 2007-07-30 12:13:56
Subject: Re: Export convert_to_scalar

pgsql-advocacy by date

Next:From: Greg Sabino MullaneDate: 2007-07-30 14:25:38
Subject: Weekly News links
Previous:From: Dave PageDate: 2007-07-30 07:47:38
Subject: Re: European users mailing list

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group