Is it generally true that any PL can be both set up to be trusted and untrusted?
No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted, it'll either notice that and ERROR
or it'll silently run without sandboxing as if it were untrusted.
Don't modify catalogs directly.
If not, what are the specific languages that can be set up to be trusted or are trusted.
Just these,
- PL/PgSQL runs as trusted.
- PL/Perl can run as trusted or untrusted.
- PL/TCL can run as trusted or untrusted.
That's it for the built-in PLs. Notably, PL/Python cannot run as trusted due to fundamental design limitations with the CPython interpreter that make creation of a restricted execution environment impossible. It's possible to create a "trusted-ish" Python runtime where an attacker is protected from simple mistakes but can break out of the sandbox with effort, but PL/Python does not support this, nor is any patch to enable it likely to be accepted.
There are also a bunch of out-of-tree procedural languages, some of which support trusted mode. Notably, PL/V8 (JavaScript), PL/Lua and PL/Java support trusted mode. There are probably others.
(On a side note, I really would love to see adoption of in-core a trusted-enabled PL/Javascript as a default in-core PL, or even better, PL/C# for .NET via Mono or .NET Core).
Short answer
You are actually using (at least) two databases. You are installing CITEXT
in the wrong one.
Longer answer
In the first database (whose name is not shown), you install CITEXT
. This database is probably the postgres
database, which is created by default when you install PostgreSQL.
Then you use the \c
command and you switch to another database (sensordata
). You need to create your extension in this database.
That is, you should do:
\c sensordata
CREATE EXTENSION citext;
\dx
If you need to use the citext
module in more databases, you need to install it in each one. The extensions on the other databases don't matter. Databases do not interfere with each other.
Every PostgreSQL cluster (=database installation) has (by default) one database named postgres
. If you use psql
, it is the one to which you connect "by default". You don't actually need to have a postgres
database. I guess the postgres
database is where you installed the CITEXT
extension.
Within every PostgreSQL database, there is, by default, one public schema.
PostgreSQL does not work like (for instance) MySQL, where you can acces several databases at once through a single connection (provided you have a user with the right permissions).
In PostgreSQL you access one database through a single connection. Within a database, you can access as many schemas as needed. The role that schemas play in PostgreSQL is nearly the same as the one played by a database in MySQL.
See also:
Best Answer
This is a frequently asked question.
CREATE EXTENSION
.postgresql-contrib
or the like.You can find the list of PostgreSQL extensions officially supported here.
PGXN.org is a great resource for finding third party modules/extensions/contrib-ie stuff too.