Обсуждение: citext, actually probably using extensions
I was pointed to the citext type to help solve an issue for a project I am working on. I looked at this page: https://nandovieira.com/using-insensitive-case-columns-in-postgresql-with-citext But I must be doing something wrong. As the PG superuser, I did this: postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; CREATE EXTENSION and as the superuser it shows up in the \dx display BUT as a normal user: stan=> \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row) It does not. What am I doing incorrectly? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
stan <stanb@panix.com> writes:
> But I must be doing something wrong. As the PG superuser, I did this:
> postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
> CREATE EXTENSION
> and as the superuser it shows up in the \dx display
> BUT as a normal user:
> stan=> \dx
> List of installed extensions
> Name | Version | Schema | Description
> ---------+---------+------------+------------------------------
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
> language
> (1 row)
> It does not.
Judging from the prompts you show, you installed the extension
in the "postgres" database, but you need it in the "stan" database.
regards, tom lane
> On Sep 20, 2019, at 1:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > stan <stanb@panix.com> writes: >> But I must be doing something wrong. As the PG superuser, I did this: >> postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; >> CREATE EXTENSION > >> and as the superuser it shows up in the \dx display > >> BUT as a normal user: > >> stan=> \dx >> List of installed extensions >> Name | Version | Schema | Description > >> ---------+---------+------------+------------------------------ >> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural >> language >> (1 row) > >> It does not. > > Judging from the prompts you show, you installed the extension > in the "postgres" database, but you need it in the "stan" database. > > regards, tom lane > > And if you install the extension in the template1 db it will be included in ‘create database’ calls. Rather than having toadd it by hand each time
Not sure about best practices, but what I'm going is like this:
* Create a schema named extensions.
* Install extensions in this special schema only. I don't put anything else in there.
* Put the extensions schema early (left) in the search_path for each role.
* Grant execute access permissively on the functions in that schema.
If there's something deeply flawed about this strategy, I'd be keen to hear about it. On the positive side, I find it simple to understand, maintain, and explain to other people. YMMV
> From: Morris de Oryx <morrisdeoryx@gmail.com>
>
> Not sure about best practices, but what I'm going is like this:
>
> * Create a schema named extensions.
> * Install extensions in this special schema only. I don't put anything else in there.
> * Put the extensions schema early (left) in the search_path for each role.
> * Grant execute access permissively on the functions in that schema.
>
> If there's something deeply flawed about this strategy, I'd be keen to hear about it. On the positive side, I find it simple to understand, maintain, and explain to other people. YMMV
>
We do approximately the same thing if it makes you feel better, although we call our schema "common".