in most applications you have some variant of this table:
CREATE TABLE users ( id serial PRIMARY KEY, username TEXT NOT NULL, passwd TEXT );
and, usually, the passwd stores user password in clear text way.
this is usually not a problem, but in case you'd like to add password encryption in database, there are some ways to do it – and i'll show you which way i like most.
first solution is a no-brainer. make the app crypt the password and do whatever is neccessary.
now, this looks like a fine solution until you'll have more than 1 application that will be checking/setting passwords. and – usually – you will.
after all – even if you do not plan to put another website on the same database, odds are one day you'll want to change user password from psql. and what then?
so, it is better to leave the encryption job to postgres itself.
to make it so, we'll do some “magic".
first, let's make our users table in a way that it will automatically convert entered password to encrypted.
to do it – we will need pgcrypto module from contrib directory. if you dont know what i'm talking about – that's really bad, as contrib modules are extremly useful.
if you're using pre-packaged postgresql, there should be package named postgresql-contrib-your-version or similarly. just install it.
then, find pgcrypto.sql file. usually you can find it in places like /usr/share/postgresql/contrib/pgcrypto.sql, /usr/local/share/postgresql/contrib/pgcrypto.sql, /usr/local/pgsql/share/postgresql/contrib/pgcrypto.sql or similar.
when you have the file, just connect to your database of choice (using superuser account) and issue (from psql):
\i /home/pgdba/work/share/postgresql/contrib/pgcrypto.sql
which will load the pgcrypto module to your database.
now, for some more interesting fun.
for our users table, we'll add a simple trigger:
CREATE OR REPLACE FUNCTION trg_crypt_users_pass() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN IF substr(NEW.passwd, 1, 3) <> '$1$' THEN NEW.passwd := crypt( NEW.passwd, gen_salt('md5') ); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_crypt_users_pass BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE trg_crypt_users_pass();
you might wander why there is this if-with-substr.
it's simple – we want to encrypt only the password that do not start with ‘$1$'. reason? crypted password will start with ‘$1$', and if we didn't put the “if" there, the first update to users table (even if it wouldn't touch passwd field) would scramble the password, thus rendering account unusable.
now, let's test if it works:
INSERT INTO users (username, passwd) VALUES ('depesz', 'depesz'); INSERT INTO users (username, passwd) VALUES ('NULL-user', NULL); INSERT INTO users (username, passwd) VALUES ('test', ' '); INSERT INTO users (username, passwd) VALUES ('foo', '$1$');
and what is in the table?
# SELECT * FROM users; id | username | passwd ----+-----------+------------------------------------ 1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0 2 | NULL-USER | [NULL] 3 | test | $1$ik3jkoki$UO4MSNsHSb5SQdq7GeZRS/ 4 | foo | $1$ (4 ROWS)
ok, works as expected. the case with passwd = ‘$1$' is dubious, and we could “fix" the issue with adding length-check to trigger, but it doesn't really bother
me, so i'll leave it as it is – after all, to make a full check i would have to use a regexp, which is not really nice.
so, now our table has encrypted passwords. and i can easily search for users:
# SELECT * FROM users WHERE username = 'depesz' AND crypt('depesz', passwd) = passwd; id | username | passwd ----+----------+------------------------------------ 1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0 (1 ROW)
bad password check:
# SELECT * FROM users WHERE username = 'depesz' AND crypt('bad-password', passwd) = passwd; id | username | passwd ----+----------+-------- (0 ROWS)
now. it's not really “easily". i could definitely do better than that.
so, let's introduce another datatype: “password":
CREATE DOMAIN password AS TEXT;
now, let's convert data:
ALTER TABLE users ALTER COLUMN passwd TYPE password;
ok, but having another datatype doesn't give me anything good. yet.
i'd like to be able to do things like:
SELECT * FROM users WHERE username = 'depesz' AND passwd = 'depesz';
without all this “crypt()" mess. so, let's write some small, custom operators.
because passwords can only “match" or “not match" we will need only 2 operators: “=" and “<>“. so, there goes the code:
CREATE FUNCTION password_leq(password, TEXT) RETURNS bool AS $BODY$ SELECT crypt($2, $1) = $1::text; $BODY$ LANGUAGE SQL immutable; CREATE OPERATOR = ( leftarg = password, rightarg = text, negator = <>, PROCEDURE = password_leq );
CREATE FUNCTION password_lne(password, TEXT) RETURNS bool AS $BODY$ SELECT crypt($2, $1) <> $1::text; $BODY$ LANGUAGE SQL immutable; CREATE OPERATOR <> ( leftarg = password, rightarg = text, negator = =, PROCEDURE = password_lne );
CREATE FUNCTION password_req(TEXT, password) RETURNS bool AS $BODY$ SELECT crypt($1, $2) = $2::text; $BODY$ LANGUAGE SQL immutable; CREATE OPERATOR = ( leftarg = text, rightarg = password, negator = <>, PROCEDURE = password_req );
CREATE FUNCTION password_rne(TEXT, password) RETURNS bool AS $BODY$ SELECT crypt($1, $2) <> $2::text; $BODY$ LANGUAGE SQL immutable; CREATE OPERATOR <> ( leftarg = text, rightarg = password, negator = =, PROCEDURE = password_rne );
now, thanks to this we can:
# SELECT * FROM users WHERE passwd = 'depesz'::text; id | username | passwd ----+----------+------------------------------------ 1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0 (1 ROW)
but, unfortunatelly, this will fail:
# SELECT * FROM users WHERE passwd = 'depesz'; id | username | passwd ----+----------+-------- (0 ROWS)
reason is very simple – postgresql, when running this query will implicitly cast ‘depesz' to ‘password', so the “=" operator will be called for (password = password) and not for (password = text)!
to make it working we'll need 2 more operators:
CREATE FUNCTION password_beq(LEFT password, RIGHT password) RETURNS bool AS $BODY$ DECLARE left_crypted bool; right_crypted bool; BEGIN left_crypted := ( substr(LEFT, 1, 3) = '$1$' ); right_crypted := ( substr(RIGHT, 1, 3) = '$1$' ); IF (left_crypted) AND (NOT right_crypted) THEN RETURN crypt(RIGHT, LEFT)::TEXT = LEFT::TEXT; END IF; IF (NOT left_crypted) AND (right_crypted) THEN RETURN crypt(LEFT, RIGHT)::TEXT = RIGHT::TEXT; END IF; RETURN LEFT::TEXT = RIGHT::TEXT; END; $BODY$ LANGUAGE plpgsql immutable; CREATE OPERATOR = ( leftarg = password, rightarg = password, negator = <>, PROCEDURE = password_beq );
CREATE FUNCTION password_bne(password, password) RETURNS bool AS $BODY$ SELECT NOT password_beq($1, $2); $BODY$ LANGUAGE SQL immutable; CREATE OPERATOR <> ( leftarg = password, rightarg = password, negator = =, PROCEDURE = password_bne );
now, the password_beq function is quite complex. what it does? it tries to guess which side of comparison is encrypted, and which is not.
when only one side of comparison has ‘$1$' at the beginning, it crypts the other argument, and then compares. if both, or none of arguments have ‘$1$' – it just compares them as simple strings.
now, i can:
# SELECT * FROM users WHERE passwd = 'depesz'; id | username | passwd ----+----------+------------------------------------ 1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0 (1 ROW)
so, without modifying client code i modified storage of password to make them crypted. which is good, at the very least for me.
this solution has one slight “issue" which can be perceived both as a drawback, or as a bonus benefit:
# SELECT * FROM users WHERE passwd = '$1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0'; id | username | passwd ----+----------+------------------------------------ 1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0 (1 ROW)
that is – instead of using standard password i can also authenticate using its hash. whether it's good i leave for you to decide – for me it's definitely a benefit.
The implication of using a table for storing user/password information is that you are bypassing the database authorization for something homegrown. There are two basic problems with this
1) Security: the application itself must be given authorization of the most powerful users, meaning that when someone compromises your application server (eg: apache) they have full access to your data.
2) Scalability: anyone wanting access to the data must either be given that same all powerful authorization, or additional authorization schemes must be implemented (say someone wants to use ODBC/JDBC to access the data for some reporting tool). And of course if you add new features to the application the old parts must be reviewed to ensure that the security model is not being compromised.
A solution which avoids these pitfalls is to use the database connection as the authorization function in your application. The downsize for that is that each user must use a separate connection which for HTTP means each GET/POST will create a new connection (connection pooling tends not to be useful as it is unlikely that a user will be serviced by the same process).
Nevertheless, in many cases, especially for intranet applications for small and medium sized offices, the performance hit is negligible and the improved security and data access is well worth using the database authorization system.
Tom, I think you have a good approach there for small business solutions, sacrificing pooling simply isn’t acceptable for larger scale soltions. In those cases, it makes good sense to have a separate user for each application or administrator who’s going to be connecting to the database, and then do application level identification in a system such as depsez is discussing.
My worry about the solution discussed above is that it’s using md5 instead of the more robust / security-droid compliant sha512. Fortunately that’s easily fixed:
SELECT encode(digest(decode(TEXT ‘swordfish’, TEXT ‘escape’), TEXT ‘sha512’),
TEXT ‘base64’);
Or you can skip that outer layer of encoding and handle passwords as bytea. Annoyingly, this requires having built against openssl 0.9.8+.
Just one, minor issue: You are talking about hashing, not encryption. Encryption is reversible, hashing is not (which is the whole point of hashing).
And for the paranoid, you should probably throw in a random salt, different for each user, to protect against table-lookups of the hashes , should they somehow be compromised.
@Tom Davis:
hmm .. i wouldn’t use db-authen in any website scenario. 1.5 million users?!
@Andrew Hammond:
i really think md5 is sufficient for passwords. using sha* is good for cryptographically secure document signatures, but for password – i don’t see real need to use anything > md5.
@smk:
sure, you’re right.
@Ron:
take a look at trigger code – salt *is* random.
gen_salt() function generates random salt.
there is other contrib package chkpass, which does mostly the same thing
@Tzvetan Tzankov:
kind of. chkpass uses old algorithm, which has one very important drawback – handles correctly only password up to 8 characters long.
also – there are ready databases (web-accessible) which “decode” those password.
If you use a trigger to encrypt the password, keep in mind that there are still many situations in which the password might be revealed to the DBA.
For instance:
* anything that causes the statement to be logged, such as log_min_duration_statement
* anyone who has access to pg_stat_activity. You might have monitoring scripts that show you some query worth looking at, and that query might end up being the one to insert the password.
And probably some other stuff. It is certainly worth considering to keep all encryption _outside_ of the database. You can’t keep information secure from the DBA if it’s encrypted by the database. And if not keeping it secure from the DBA, why not just revoke privileges from everyone else?
@Jeff Davis:
actually i think that protecting “against” dba is futile anyway. dba can do whatever he want, so with some level of knowledge he can circumvent any kind of solution (as long as unencrypted/unhashed data shows to db server at least once).
@depesz
If you’re not protecting against the DBA, then why not just REVOKE and forget encryption all together?
If the goal is to avoid accidentally storing the plaintext password (as DBA), I showed two examples where it’s easy to accidentally store the password in plaintext if you’re the DBA.
@Jeff Davis:
usually against “hacks”.
somebody hacks, gets dump of database.
somebody steals backups.
this kind of things.
of course hacker that will hack, get root access will be able to circumvent it, but it will take longer time, thus it makes the hack easier to be revealed.
also – sometimes password encryption/hashing in database is external requirement (think: business, local law, common practice)
@depesz:
Oh, I see. Makes sense, particularly the point about backups.
@depesz:
However, I would like to add that if there is a legal requirement that you not store plaintext passwords, be very careful that no SQL statements get logged, and that no monitoring scripts watch pg_stat_activity. Otherwise a password might get saved in plaintext in a log or notification.
# select * from users where passwd = ‘$1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0’;
id | username | passwd
—-+———-+————————————
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
(1 row)
Doesn’t this negate the whole thing? I thought the purpose was to make the hashed values useless to a bad guy. This makes them identical in function to the real passwords.
@Ian Harding:
Well yes and no.
To get hashes bad guy would have to have database privileges, and in this situation we’re screwed anyway. The point is that he/she will not be able to get the passwords to use them someplace else.
I was thinking of backups. If I get your backups, I have everyone’s password. If the system doesn’t allow passing the hash to log in, I just have a bunch of hashes that I have to crack. THEN I have everyone’s password. But that takes a while.
On the other point, if I crack your web server, I have database privileges as the web user account. That should leave you only partially screwed since the web server connects as an unprivileged user. It takes a whole other level of kung-fu to get to super user from there. And it takes a while.
Anyway, not throwing rocks, this is an excellent intro to pgcrypto and custom operators. Thanks!
This doesn’t work on PostgreSQL 9.0 beta 2, function password_beq fails with error
ERROR: syntax error at or near “,”
LINE 58: left_crypted := ( substr(left, 1, 3) = ‘$1$’ );
^
********** Error **********
ERROR: syntax error at or near “,”
SQL state: 42601
Character: 1404
@kpy3:
that’s because left is now keyword. just prefix variable names, and you’ll be good.
Oh! I’ve missed it, thanx.
someone can help me!!
use what encryption this one
$1$$YT09lUh5xTp9kWFx8G2bV0
can someone encrypt for me??
thanks before
@need help:
it looks like crypt() with md5 algorithm, but with empty (or removed) salt.
General question about performance – how are indexes used for such encrypted field? I am writing about situation when we store sensitive data
Are they used when searching for the given value or the whole table is scanned when I search with unencrypted value from e.g. code ?
Thanks !
@Joanna:
not sure I understand. Why would you search using encrypted field?
What’s the usecase?
The case is that I have some fields encrypted but I need to be able to search on this field. Your solution seems to fit my needs however I am not sure how it affects performance, let’s say such query
select * from “table” where encrypted = “my_unencrypted_value”
would return what I need.
The question is how index will be build, will postgres use my overloaded comparison operator or the standard one?
Thank you !
J.
@Joanna:
don’t know frankly. Most likely it will not be indexed.
What does the ‘:=’ (colon equals) operator do?
@Paul:
variable assignment in plpgsql.
I have the same table (users) with one more column (“Name”). When I insert a new user with his password the sql works like a charm but when I change the value of the column “Name” and add a new column “Surname” it doesn’t work anymore. i mean the code:
[select * from users where passwd = ‘depesz’] doesn’t find the user. Why is that happening?
Thanks
@Dure:
adding columns doesn’t matter anything to the trigger. And I can’t comment on what’s happening without specific case, with full schema, and a way for me to replicate the error.
@DEPESZ:
Yeah sorry, it was a stupid error from my place. Your
code works like a charm.
For the records I was trying to use the Blowfish scheme (http://en.wikipedia.org/wiki/Crypt_(Unix)#Blowfish-based_scheme) instead of the md5.
To do that you just need to replace the trigger trg_crypt_users_pass():
IF substr(NEW.passwd, 1, 4) ‘$2a$’ THEN
NEW.passwd := crypt( NEW.passwd, gen_salt(‘bf’) );
END IF;
and the function password_beq :
left_crypted := ( substr(left, 1, 4) = ‘$2a$’ );
right_crypted := ( substr(right, 1, 4) = ‘$2a$’ );
It works fine for me.
Hi your ENCRYPTED PASSWORDS IN DATABASE articles is very use fully for me thanks of lot sir and hats off to you. and one more detail i need how to upload(Store) and retrieve image in postgres sql data base using vb.net..
Thanks & Advance
Bandhalaraja
@DEPESZ:
I know this is obvious! Hashing passwords prior to storage is only useful in situations where the clear is presented by the user (or application). So that the hash can be re-applied before comparison.
In the situation where the clear is required by the application in the absence of the user, for example, keeping passwords for auth protocols which never transmit the password over the wire. SMTP auth CRAM-MD5, DIGEST-MD5. The application has to recover the clear password from the database!
In these circumstances a possible solution is to use a symetric encryption, AES for example.
The problem now is we need a method of securing the AES key which, by definition, must be available to DB!
Any ideas?
Well, I can imagine some ways to handle it – for example, make a compiled c function, callable from pg, that returns the password, and set proper privileges to the function.
Or do the AES in application, and make providing password a one-time on application start, interactive thing.
I will immediately seize your rss as I can not find your e-mail subscription hyperlink or e-newsletter service.
Do you have any? Kindly allow me understand so that I may subscribe.
Thanks.
Rally car braking systems have larger master cylinders to transmit a greater force through the calipers,
since rally events require instant and powerful braking.
The standard consumer loan car finance option is actually the most common type of car
financing in Australia. Visit our website at and avail of our free case evaluation.