I’m new to PostgreSQL. I’ve read from the tutorial. I can successfully
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.
encrypt function you refer to is presumably
encrypt(bytea, bytea, text) from the bundled
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.
Answered By – Craig Ringer