4

Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS:

devdb=> SELECT digest('blah', 'sha1');
ERROR:  function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha1');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Even though pgcrypto exist:

devdb=> SHOW rds.extensions;

btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp

How can I enable the digest() function?

Updates

I have co-asked on the AWS RDS forum, will posts mutually updated.

digest() does not seem to exist on the db:

devdb=> \df digest
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
  • Output of \df digest in psql? – Craig Ringer Oct 28 '14 at 9:37
10

The question was answered at the AWS forum by Shawn@AWS - The rds.extension list shows available, rather than installed, modules.

Thank you for using RDS Postgres. The "rds.extension" parameter shows you what extensions are available in RDS Postgres. You still need to execute the "create extension" command.

Using your test case:

postgres=> show rds.extensions;
... ,pgcrypto, ...
postgres=> create extension pgcrypto;
CREATE EXTENSION

postgres=> SELECT digest('blah', 'sha1');
digest
\x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

Addendum

pg_available_extensions shows which extensions are available and which are already installed:

postgres=> SELECT * FROM pg_available_extensions;

          name          | default_version | installed_version |                               comment
------------------------+-----------------+-------------------+---------------------------------------------------------------------
 chkpass                | 1.0             |                   | data type for auto-encrypted passwords
 xml2                   | 1.0             |                   | XPath querying and XSLT
 plpgsql                | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto               | 1.0             |                   | cryptographic functions
 postgres_fdw           | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
...
  • Addendum Addendum: Extensions have to be created once per database. And the pgcrypto extension is special as it can only be created by the database superuser (normally postgres). With peer auth enabled as usual on Ubuntu etc., the following command would bring you to the correct prompt to create the extension: sudo -u postgres psql -U postgres -d database_name – tanius Dec 9 '16 at 1:55
0
  1. sudo apt-get install postgresql-contrib-9.1 or depending on your postgres version (Check debian pkg or depending on your distrib)

  2. in postgres: CREATE EXTENSION pgcrypto;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.