24

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it.

There is no pgcrypto if I try to install it using apt-get install pgcrypto and I don't find any files starting with pgcrypto in my system (I tried find / -name "pgcrypto*").

How do I install pgcrypto so I can use the digest('word-to-hash','sha1') function in my database queries?


Update: I'm struggling to install pgcrypto on another Ubuntu machine. After installing the package using sudo apt-get install postgresql-contrib-8.4 how do I install it to my current PostgreSQL database?

2
  • Hi @Jonas, I'm not sure if you're having issues installing pgcrypto or if you're having issues getting it to work with your db install. It sounds like you have a repo issue. Verify this file exists on this path /usr/local/pgsql/share/contrib/pgcrypto.sql and let us know.
    – jcolebrand
    Mar 25, 2011 at 14:46
  • @jcolebrand: No I don't have a /usr/local/pgsql directory. The only place where I have *.sql files are in /usr/share/postgresql/8.4/ but no crypto-related.
    – Jonas
    Mar 25, 2011 at 15:00

3 Answers 3

18

For newer version of PG, check out the answer below by Dustin Kirkland

It's an external module for Postgres. You should install the postgresql-contrib-8.4 (or your pg version) package via apt:

apt-get install postgresql-contrib-8.4

Then you find the sql install file somewhere in the /usr/share/postgresql folder, and you'll need to run pgcryto.sql on the database.

psql -d <database> -f /usr/share/postgresql/8.4/contrib/pgcrypto.sql

Or,

$ cd /usr/share/postgresql/8.4/contrib
$ psql -d <database>
    psql (8.4.8)
    Type "help" for help.

    database=# \i pgcrypto.sql
6
  • When I run sudo apt-get install postgres-contrib I get E: Unable to locate package postgres-contrib
    – Jonas
    Mar 25, 2011 at 14:54
  • I googled and now I have installed it using sudo apt-get install postgresql-contrib-8.4 and then run \i <path-to-pgcrypto> using psql.
    – Jonas
    Mar 25, 2011 at 15:59
  • That's the way to do it. I slightly updated the answer to include the note on the version for the package
    – DrColossos
    Mar 25, 2011 at 17:19
  • 1
    Ah, the command was \i /usr/share/postgresql/8.4/contrib/pgcypto.sql
    – Jonas
    May 24, 2011 at 16:24
  • 9
    Under postgresql 9.1 you type in command line: CREATE EXTENSION pgcrypto
    – user5423
    Dec 30, 2011 at 14:08
21

PostgreSQL 9.1+

Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1.

There, I needed to:

sudo apt-get install postgresql-contrib

And then in my database:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

And now I can use pgcrypto functionality, gen_random_bytes():

test=# create table test ( 
  id 
    text 
    not null 
    default encode( gen_random_bytes( 32 ), 'hex' ) 
    primary key, 
  value 
    text 
); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers                          
--------+------+------------------------------------------------------------
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value   
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo
1
  • I really like this answer, though you should have self-answered a question for 9.1+ (when this feature was added) As the OP clearly asked for 8.4 and his version doesn't support CREATE EXTENSION (which makes the other answer strictly speaking correct. Jan 9, 2017 at 4:32
2

For the latest version, there is no file path end with pgcrypto.sql .

Create an extension pgcrypto under the required user.

$ psql -U <username> -d mydb

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

mydb=> CREATE EXTENSION pgcrypto;

CREATE EXTENSION
mydb=> 

If in case, the user does not have permission to create an extension, give superuser permission by login as postgres(default) user and try again.

$ psql --u postgres

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# ALTER USER <username> WITH SUPERUSER;

ALTER ROLE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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