PostgreSQL-10中跨库使用pgcrypto扩展失效问题求助

Hey there, let's work through this pgcrypto problem together—this is a common gotcha, so we'll get it sorted out. Here are the key checks and fixes to try:

• Verify pgcrypto is actually installed in your target database
  First, make sure you're connected to the right database, then run this query to confirm the extension exists there:

  SELECT * FROM pg_extension WHERE extname = 'pgcrypto';
  

  If you don't see a row for pgcrypto, your initial CREATE EXTENSION pgcrypto; either failed silently or was executed in the wrong database. Double-check your connection, ensure your user has CREATE EXTENSION permissions, and re-run the command while watching for error messages.

• Check your database's search_path configuration
  pgcrypto functions are typically installed in the public schema, but if your database's search_path doesn't include this schema, PostgreSQL won't locate the functions. Run this to view your current search path:

  SHOW search_path;
  

  If public isn't listed, test a temporary fix first:

  SET search_path = public, pg_catalog;
  

  Try using crypt() or gen_salt() again. To make this change permanent for the target database, use:

  ALTER DATABASE your_target_db_name SET search_path = public, pg_catalog;
  

• Ensure your user has proper execution permissions
  Even if the extension is installed, your database user might lack rights to run pgcrypto's functions. Grant the necessary permissions with:

  GRANT EXECUTE ON FUNCTION crypt(text, text) TO your_db_user;
  GRANT EXECUTE ON FUNCTION gen_salt(text) TO your_db_user;
  

  Alternatively, if all pgcrypto objects live in public, you can grant schema usage to cover all related functions at once:

  GRANT USAGE ON SCHEMA public TO your_db_user;
  

• Confirm you're connected to the correct database
  It's easy to accidentally stay connected to the default postgres database without noticing. Run this to double-check your current connection:

  SELECT current_database();
  

  If it's not your target database, switch to it (use \c your_target_db in psql, or adjust your client's connection settings).

• Reinstall the extension if needed
  If none of the above works, try dropping and re-creating the extension to rule out a corrupted installation:

  DROP EXTENSION pgcrypto;
  CREATE EXTENSION pgcrypto;
  

  Make sure you run these commands in your target database, not the default postgres one.

内容的提问来源于stack exchange，提问作者Alberto