In PostgreSQL, using the pgcrypto module, I can generate a **slow** hash with blowfish (or any supported algorithm) like this:

crypt('SomeTextHere', gen_salt('bf'));

However, it auto-generates a salt (with gen_salt) and requires it. I don't want to use any salt. I am also not interested in using a normal hashing function (like sha256). How can I generate a **slow** hash without a salt?

This is not technically a database question, but about the innards of Postgres's pgcrypto extension .The documentation says > The algorithms in crypt() differ from the usual MD5 or SHA1 hashing > algorithms in the following respects: I would like to know how/where might I be able to find the details of the hashing algorithms. Ideally, I would like to compare the algorithms of pgcrypto with the regular versions of the algorithms.

I am in a need of joining 2 tables with stringed identifiers, the key in first table is encrypted with AES ECB and the one in the second table is a raw form of that field in first table. I am trying to achieve this by doing something like

CREATE EXTENSION pgcrypto;

select * from table1 
inner join table2 on 
( table2.rawid = convert_from(decrypt(decode(table2.encid,'BASE64'),'passwordshouldbe','AES'), 'UTF-8'));

This should work and it does, until the encrypted text of length > 15. Take for example: 1. select encode(encrypt(cast('0123456789101112' as bytea),cast('passwordshouldbe' as bytea),'aes'),'BASE64'); (length=15) produces pqWvs6RxsAqPRVUK7VFy5w== and 2. select encode(encrypt(cast('0123456789101112' as bytea),cast('passwordshouldbe' as bytea),'aes'),'BASE64'); (length = 16) produces +p3iTMN7zmb0wh1lk2Wk+Hbfj6WbqP1ECgtPci4nbW8= My java code produces the encryption as pqWvs6RxsAqPRVUK7VFy5w== and +p3iTMN7zmb0wh1lk2Wk+I64/ZdIsIaXiPkdDpkCzgY= respectively for each of the cases. The encrypted form of the string with `length 15 in length) from the postgresql only. Can anyone guide me somewhere here regarding what pgcrypto is doing for encrypting longer texts than length 15. If it is of any help, I am including the java code I used for encryption of the data. public class Utilities { private static final String ALGO = "AES"; private static final byte[] keyValue = new byte[]{0x70, 0x61, 0x73, 0x73, 0x77, 0x6f, 0x72, 0x64, 0x73, 0x68, 0x6f, 0x75, 0x6c, 0x64, 0x62, 0x65}; //passwordshouldbe public static String encrypt(String data) { try { Key key = generateKey(); Cipher c = Cipher.getInstance(ALGO); c.init(Cipher.ENCRYPT_MODE, key); byte[] encVal = c.doFinal(data.getBytes()); return Base64.getEncoder().encodeToString(encVal); } catch (Exception ex) { logger.error(ex.toString()); return null; } } public static String decrypt(String encryptedData) { try { Key key = generateKey(); Cipher c = Cipher.getInstance(ALGO); c.init(Cipher.DECRYPT_MODE, key); byte[] decordedValue = Base64.getDecoder().decode(encryptedData); byte[] decValue = c.doFinal(decordedValue); return new String(decValue); } catch (Exception ex) { logger.error(ex.toString()); return null; } } private static Key generateKey() { Key key = new SecretKeySpec(keyValue, ALGO); return key; } }

I'm using pgcrypto module for PostgresSQL, and in the document it was indicated that this line:

CREATE EXTENSION pgcrypto;

should be executed before creating the table which stores encrypted data. My question is, if the table has already been created without the above mentioned line being executed, can I modify the table later by running this line to give it the encryption ability? My goal is to use *pgp_sym_encrypt()* for INSERT operations, *pgp_sym_decrypt()* for SELECT operations and both functions for UPDATE operations. For now I can get prepare statement to work, but exec would fail, I guess because the CREATE EXTENSION line hasn't been run.

I'm in the process of creating a postgres database for production in CentOS 7. So I already installed (yum install postgresql10-server postgresql10 after adding the repos of course) and configured postgres 10. However, in my scripts I need to install pgcrypto extension and I haven't successfully install it. This is what I've done so far: 1. the first error I got was saying that the /usr/pgsql-10/share/extension/pgcrypto.control files does not exist. Googling I realized that I have to install postgres-contrib package, which I did and then restarted postgres service, but the error continues due to the fact that the extensions were installed into /usr/share/pgsql/extension, so I copied the extension files from they were installed to they were expected and then 2. appears this message > "ERROR: could not access file "$libdir/pgcrypto": No such file or directory" Googling again I found that maybe I need to give another option, so I ran CREATE EXTENSION pgcrypto FROM unpackaged; then 3. the error message now is > ERROR: function digest(text, text) does not exist And I'm stuck and without any idea what to do next. Is anybody else using this extension in postgres 10 on Linux?, if so, how did you create the extension? Version info: PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart. It showed message like functions gen_salt() / crypt() is unknown or not defined. I executed create extension pgcrypto; once and it started working. How to handle this in prod environment? Is there a way to make this module permanently available in db even if db is restarted? Or do I have to instruct server manager to run this command in case they required to restart db server? Or **can** I write this statement in functions itself so that it will create the extension every time before using cryptographic functions? Moreover, we are using a read-replica of db, where it did not allowed to execute this "create extension" statement as there is read permission only. So how to make this cryptographic functions available in such read-only environment?

On an AWS RDS instance, we have run CREATE EXTENSION pgcrypto; The extension gets created. Both queries: SELECT digest('a', 'sha256') FROM table_name; SELECT public.digest('a', 'sha256') FROM table_name; Gives the error **function public.digest(unknown, unknown) does not exist**

We've got a web app that needs to store sensitive data entered by the user. Currently we're exploring PostgreSQL in AWS. I'm aware of pgcryto and that we can hash contents of certain columns (as not everything needs to be encrypted). However, we need to also be able to search through these columns and perform sorting. These two seem to be limitations once we encrypt the data. What are my choices at the moment if we must also support sorting and searching? Keeping in mind that the solution must also be performant.

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?

I was wondering if there would be any way to do the following in PostgreSQL: UPDATE cryptotable SET work = work + 'some big hexadecimal number' where work is an unsigned 256 bit number. Right now my column is a character varying(64) column (hexadecimal representation) but I would be happy to switch to another data type if it lets me do the operation above. If it's not possible with vanilla PostgreSQL, are there extensions that could help me? (I also [posted this to pgsql-hackers](http://www.postgresql.org/message-id/CALwxDuETY1_jzBSQC+nR-uBY0eZHhH5poTMo0KCCi14Z4RtCtQ@mail.gmail.com) so check out the thread there for more ideas.)

I am working on encrypting a column in a postgre table using pgcrypto. The postgre version is 9.5.1. I have figured out how to encrypt using pgp_sym_encrypt() and decrypt using pgp_sym_decrypt(). My problem is figuring out how to keep the key hidden. I am trying to avoid having the application decrypt the data that is returned. The data in this table is only sensitive if someone can view the entire table (450,000 rows) and put the data together, so its ok for the data to return to the application decrypted. In pgAdmin, I have this in my query SELECT pgp_sym_decrypt(column1,'password') Obvisouly, I don't want the query template our application uses to have the password stored in it and then pass it in everytime a query is ran. Any ideas would be appreciated?

The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation. >/* > > * Generate random bits. pg_backend_random() will do here, we don't promis > > * UUIDs to be cryptographically random, when built with > > * --disable-strong-random. > > */ Discussed further in this thread . In the particular build I may be using, how can I **detect if the strong random feature is enable or disabled**?

SELECT gen_random_uuid() produces output ERROR: function gen_random_uuid() does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. I ran CREATE EXTENSION pgcrypto; on selected database and SELECT gen_random_bytes(1) works perfectly (gen_random_bytes doesn't work on other databases where pgcrypto extension was not manually created). % psql --version psql (PostgreSQL) 9.5.3 Ubuntu version is 16.04.

I want to keep user records in Postgres database and faced one issue which block me. It is password encryption. For example, this is correcetly encrypted password 12345 via default Ubuntu crypt function:

$6$P5jfk4Ufh33f$.Dc8H9jsWAl/igt4QPbYI/El28SyUEAoJPPHsLIGMKOymhC7AIaiizlW5W9hm7kU7PYMCySEhYtMqVPFsVTMK/

As I understand first 6 means that it is encrypted via sha384 (or sha256) algorithm. Ok, i created an extension pgcrypto in desired database, but following code: SELECT digest('12345', 'sha384') as pwhash; returns absolutely different result - without algorithm mark and salt:

\x0fa76955abfa9dafd83facca8343a92aa09497f98101086611b0bfa95dbc0dcc661d62e9568a5a032ba81960f3e55d4a

I want to get exactly specified format because it is used by PAM to check user credetials and it does not understand password hashed another way.

I want to install the pgcrypto extension on my postgresql installation on Windows. I found that I just have to call CREATE EXTENSION pgcrypto; but I've got an error : Syntax error on « CREATE » LINE 1: SELECT COUNT(*) AS total FROM (CREATE EXTENSION pgcrypto) AS... (I'm on **PostgreSQL 9.2.4** and I executed the sql line from phpPgAdmin with *postgres* user)

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve this?