CODEWITHSUNDEEP
postgresqlrolespsql
Aidan Rosswood
Aidan Rosswood

Reputation: 1212

PSQL User Cannot See Extensions Created By Superuser

So I have created two extensions with my superuser but when I try to use these extensions as a (non-super) user, it cannot find them.

As superuser:

postgres=# SELECT e.extname
postgres-#      , n.nspname      AS home_schema_of_extension
postgres-#      , extrelocatable AS extension_can_be_relocated
postgres-# FROM   pg_catalog.pg_extension e
postgres-# JOIN   pg_catalog.pg_namespace n ON n.oid = e.extnamespace;
    extname    | home_schema_of_extension | extension_can_be_relocated
---------------+--------------------------+----------------------------
 adminpack     | pg_catalog               | f
 plpgsql       | pg_catalog               | f
 fuzzystrmatch | public                   | t
 btree_gist    | public                   | t
(4 rows)

And as another user:

toggleme=> SELECT e.extname
toggleme->      , n.nspname      AS home_schema_of_extension
toggleme->      , extrelocatable AS extension_can_be_relocated
toggleme-> FROM   pg_catalog.pg_extension e
toggleme-> JOIN   pg_catalog.pg_namespace n ON n.oid = e.extnamespace;
 extname | home_schema_of_extension | extension_can_be_relocated
---------+--------------------------+----------------------------
 plpgsql | pg_catalog               | f
(1 row)

Why is the non-super-user unable to use these extensions? Non-super-user has public in his search path but still can't even see that these extensions exist.

Upvotes: 3

Views: 1064

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324731

The superuser and normal user are connected to different databases in the same PostgreSQL instance.

CREATE EXTENSION is database-local.

Upvotes: 9

Related Questions