2

I am trying to install pgcrypto in pg_catalog schema. But this does not work with postgres 13 or higher since the function gen_random_uuid is globally available. How can I still create my extension?

I am trying:

CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA pg_catalog CASCADE

I get the error:

ERROR: function "gen_random_uuid" already exists with same argument types.

Fab C
  • 65
  • 1
  • 6
  • 2
    You will have to install it into a different schema. Installing an extension into `pg_catalog` is **really** bad idea to begin with. –  Jan 11 '22 at 12:50
  • @a_horse_with_no_name why is it a bad idea to insall extensions in `pg_catalog`? What would be the recommended way? Install it under `public` or some new schema? – Fab C Jan 11 '22 at 14:51
  • 3
    `pg_catalog` only contains system tables and the content in their should never contain any user related things. Whether you prefer `public` or any other schema is a matter of personal taste. I usually have one schema named `ext` where I put **all** extensions into. –  Jan 11 '22 at 14:57
  • 4
    Same here: I install all extensions to schema `ext`. Make sure to include the schema in the `search_path` for all users using extensions (typically all). Like: `public, ext`. – Erwin Brandstetter Jan 11 '22 at 15:40
  • @a_horse_with_no_name, actually on most cases is recommended to install common Postgres extensions into pg_catalog, so these are available within any schema. – D.A.H Nov 17 '22 at 10:03
  • @D.A.H: I highly doubt that. Where did you find that recommendation? –  Nov 17 '22 at 10:06
  • @a_horse_with_no_name, read manuals. In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. If you want make gen_random_uuid available within any schema, then - CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE; – D.A.H Nov 18 '22 at 13:52
  • @D.A.H: that's not in [the current manual](https://www.postgresql.org/docs/current/pgcrypto.html) –  Nov 18 '22 at 14:01
  • @a_horse_with_no_name, sadly I cannot give to anyone understanding. You can do on your own servers as you wish. But the concept and behaviour of pg_catalog does not depend at your personal taste. Actually you are trolling and this is not useful for anyone. – D.A.H Nov 20 '22 at 18:44
  • @D.A.H: I am not trolling. You stated the recommendation that extensions should be installed in the manual, I asked for the link to the manual that contains that recommendation. If you can't provide that, then it's not my fault. –  Nov 20 '22 at 19:36
  • @a_horse_with_no_name. Actually you stated, that "You will have to install it into a different schema. Installing an extension into pg_catalog is really bad idea to begin with." You have not provided any facts or arguments to support your claim. I said, that on most cases is recommended to install COMMON Postgres extensions into pg_catalog, so these are available within any schema. And I also explained it. Part of my explanation was: "pg_catalog is always effectively part of the search path". So I have provided arguments, wich are based on official documentation. – D.A.H Nov 21 '22 at 14:58
  • Additionally, @a_horse_with_no_name, you have not provided any argumentation. That's why I'm saying that you are trolling, as you are not providing any value. The extensions supplied with PostgreSQL are believed to be secure against installation-time attacks. Also pgcrypto documentation states: This module is considered trusted. So on this case rule, that extensions whose scripts have not been carefully vetted, should only installed into secure schemas, does not apply. – D.A.H Nov 21 '22 at 15:00
  • Tom Lane [once stated](https://www.postgresql.org/message-id/flat/2507.1339789795%40sss.pgh.pa.us) that moving an extension to `pg_catalog` should be prevented to begin with. Or you wind up with [such a problem](https://www.postgresql.org/message-id/flat/269666903b69c2b9548fe75826174c14e103b818.camel%40cybertec.at) or [this problem](https://www.postgresql.org/message-id/flat/046A362C-31BC-4C40-ACE5-B71B65EA541A%40cygnis.nl). So where is the link to the manual (or a thread on the mailing list) that says installing things into `pg_catalog` is a good thing? –  Nov 21 '22 at 15:19

1 Answers1

-1

All functions in pg_catalog are automatically available within other schemas.

Any function with schema pg_catalog can be called by using schema for example pg_catalog.gen_random_uuid(), but also without defining the schema, for example gen_random_uuid().

Function gen_random_uuid() is part of extension pgcrypto, so when you are trying to enable it, it notifies you, that this function is already installed.

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE;

On most cases is recommended to install common Postgres extensions into pg_catalog, so these are available within any schema. All objects in pg_catalog are automatically appended to any other schema. For example if you put pgcrypto into public schema, then you have to always use public.gen_random_uuid() within any other schema, which is annoying etc. But if you put it into pg_catalog, then you can call anywhere gen_random_uuid(), which is more useful.

D.A.H
  • 858
  • 2
  • 9
  • 19
  • If you do that, and then upgrade that database to Postgres 13 you will have a problem because Postgres 13 creates a `pg_catalog.gen_random_uuid()` as well. You can always add the "extension schema" to the search_path to avoid having to prefix the function with the schema name. –  Nov 21 '22 at 15:21
  • Yes, it depends at PostgreSQL version since v13. Which is even extremely odd. As pgcrypto is PSQL native module and using actually already kinda “reserved” name for built in function afterwards, isn’t friendly move. Which leads to another conclusion - do not use public scheme for your tables, routines etc at all since v13. – D.A.H Feb 20 '23 at 21:44