PostgreSQL encrypt function not found

Question

I'm new to PostgreSQL. I've read from the tutorial. I can successfully SELECT, INSERT, DELETE and UPDATE the data(I know this is just a basic part).

My problem now is to encrypt the data. I'm searching now for tutorial, but I failed to find the answer of my question. I try this code in "execute arbitrary SQL" (edit: in PgAdmin-III? Some other tool?).

INSERT INTO "admin" ("email_address", "password", "role_type", "status") 
VALUES ('encrypt(''sdf'', digest(''secret2'', ''sha256''), ''aes'') ', 'sd', 'admin', 'Active')

but the result I get is this

ERROR: function encrypt(unknown, unknown) does not exist

How can I get this function?

So far, as what I have read, they only show code how to use it.

Solution

The encrypt function you refer to is presumably encrypt(bytea, bytea, text) from the bundled pgcrypto extension.

See the documentation linked above for usage details.

To load the extension:

CREATE EXTENSION pgcrypto;

as a superuser.

Really, though, useful crypto isn't as simple as calling an encrypt function. Where do you store the key? Is the database server secure (and if so, why are you encrypting the data)? Could an attacker wrap or replace the encrypt function to steal keys? Could they enable query logging and steal the key from the server log? Trigger an error that caused the statement to be logged and steal the key from the error message? etc.

Proper cryptography isn't just a matter of sprinking some encryption secret sauce on the code. You need to profile the possible attackers you want to defend against, what you don't want to try to defend against, and what their capabilities might be. Then set up suitable systems based on the attacker's assumed capabilities and the cost/inconvenience/downtime/risk you're willing to assume.