7

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)
Community
  • 1
Adam Matan
  • 12,079
  • 30
  • 82
  • 96

2 Answers2

18

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
...
Community
  • 1
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
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;

eloone
  • 109
  • 1