title: On AWS RDS Postgres, how to have dictionaries and unaccented full-text search?
body: <p>I want to use PostgreSQL 9.5.4 on AWS RDS, leveraging the full-text search, dictionaries with stop words, unaccented full-text search.</p>
  
  <p><strong>The context:</strong></p>
  
  <p>With 'unaccent' on, this full-text (Json) query will find 'F(e-acute)vrier' even if I did not enter the accent properly</p>
  
  <pre><code>psql&gt;select * from proto_model.product where to_tsvector((body -&gt;&gt; 'description')) @@ to_tsquery('Fevrier');
  </code></pre>
  
  <p>With an English dictionary, the same search for 'the', 'any', 'you'... will find nothing as they are 'stop words' defined in the English dictionary and ignored.</p>
  
  <p><strong>The issue:</strong> </p>
  
  <p>On my local Postgres, this is not an issue at all. On the managed AWS this is one.
  EC2+Docker on AWS would not be an issue of course but I focus on RDS Postgres for now.</p>
  
  <p>Locally, the default value of default_text_search_config (get it with <code>psql&gt;show all</code>) is 'pg_catalog.english' which uses the English dictionary and stop words. On RDS this is 'pg_catalog.simple'.</p>
  
  <p>1) In AWS, I cannot add a dictionary or modify one as you need file system access that you don't have. There is no programatical solution to create/update a dictionary AFAIK.</p>
  
  <p>2) In AWS, as the 'postgres' user or even as the '<a href="http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.rds_superuser" rel="noreferrer">rds_superuser</a>' that you can <a href="http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html" rel="noreferrer">create</a>, I cannot alter the global configuration </p>
  
  <pre><code>psql&gt;ALTER SYSTEM SET default_text_search_config = 'pg_catalog.english';
  ERROR:  must be superuser to execute ALTER SYSTEM command
  </code></pre>
  
  <p>In addition, it is absent of the RDS Postgres parameter groups that you can associate to a new Postgres instance and you cannot add missing values!
  Granting more to 'rds_superuser' (<code>psql&gt;grant all on schema public to ...</code>) does not help.</p>
  
  <p>3) In AWS, as 'postgres' or 'rds_superuser', I can however set for my session, the current text configuration </p>
  
  <pre><code>psql&gt;set default_text_search_config = 'pg_catalog.english'; 
  SET
  </code></pre>
  
  <p>4) Sadly in AWS, as 'postgres' or 'rds_superuser', I cannot alter the search configuration (global) to ignore the accents. This works fine locally. </p>
  
  <pre><code>psql&gt;ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;
  ERROR:  must be owner of text search configuration english
  </code></pre>
  
  <p>5) In AWS, as 'postgres' or 'rds_superuser', I can create a new search configuration (English + Unaccent) but I cannot set it as the default even in my session!</p>
  
  <pre><code>psql&gt;CREATE TEXT SEARCH CONFIGURATION english2 (copy=english);
  CREATE...
  psql&gt;ALTER TEXT SEARCH CONFIGURATION english2 ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;
  ALTER...
  psql&gt;set default_text_search_config = 'pg_catalog.english2';
  ERROR:  invalid value for parameter "default_text_search_config": "pg_catalog.english2"
  </code></pre>
  
  <p>So it seems that I am cooked.</p>
  
  <p>The best that I can see could be to associate automatically without a need for individual <code>psql&gt;set default_text_search_config = ...</code> my user connection to a set of configuration options</p>
  
  <pre><code>psql&gt;alter role somerole set default_text_search_config = 'pg_catalog.english';
  psql&gt;select * from pg_user; (the option is present by default for all my connections under this role)
  </code></pre>
  
  <p><strong>Outside of moving out of AWS RDS to EC2+Docker, do you know any solution to (4) or (5) providing me dictionary+unaccent?</strong> </p>