citext, actually probably using extensions

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 to add 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".
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.