pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres encryption
4+ messages / 3 participants
[nested] [flat]
* postgres encryption
@ 2018-03-22 07:11 dbx postgres <dbx2postgres@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: dbx postgres @ 2018-03-22 07:11 UTC (permalink / raw)
To: pgsql-admin
Hello Admins,
I have a table in which one of the password column is encrypted. Is there a
way I can decrypt or know what encryption type was used on the column to
decrypt this password. I tried overriding this password with a new value
but that value is being stored as plain text ad not encrypted. So my
authentication is failing. How can i override this password ?
Thanks in advance !
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres encryption
@ 2018-03-22 09:53 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: dbx postgres <dbx2postgres@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2018-03-22 09:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On Thursday 22 March 2018 07:11:13 dbx postgres wrote:
> Hello Admins,
> I have a table in which one of the password column is encrypted. Is there a
> way I can decrypt or know what encryption type was used on the column to
> decrypt this password. I tried overriding this password with a new value
> but that value is being stored as plain text ad not encrypted. So my
> authentication is failing. How can i override this password ?
>
> Thanks in advance !
Hi
Purely a guess, but if you store a plain text and it appears in the table as
plain text, then the encryption is done at application level.
In order to work out what type of encryption is used, and *if* it can be
decrypted you would have to look at that program.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres encryption
@ 2018-03-22 13:19 Bear Giles <bgiles@coyotesong.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Bear Giles @ 2018-03-22 13:19 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-admin@lists.postgresql.org
So you got into your target's database and want to know how to extract the
passwords?... :-)
First, nobody should be encrypting passwords. They should be hashed. (The
sole exception is if your site is a proxy to a third site and it's now much
more common to have a different type of authentication method than a stored
password.) A single layer of hashing without salt isn't very strong anymore
- I don't care what your hashing algorithm is - and a single layer of
hashing with salt isn't particularly strong either since it's often
misapplied.
Going forward with some 'best practices'
1. Use one of the standard algorithms that will perform an iterative hash
with salt for 1000 times. I think I put something up on my blog (
https://invariantproperties.com) some time ago with sample code, or you can
use the bcrypt2 library. Reminder that doing a hash with salt correctly is
easy to screw up, e.g., H(salt|password) is far weaker than
H(password|salt).
2. Add generation information to your salt and hash values. It can be
encoded into a single column, e.g., 1$salt$hash, or in a UDT RECORD (gen,
salt, hash). You'll need to keep track of what each generation means, e.g.,
'1' might mean the legacy SHA-1 hash but 2 means salted SHA-512 or BCRYPT
or something else.
3. If you want to prevent an attacker from creating an account and copying
the salt + hash into a targeted account's entry you can add a few more
columns or UDT RECORD: (uid, gen, salt, hash, hmac). The last item is a
message digest (with password known to your app) on the other four entries.
You would verify that before checking the hashed password - if it doesn't
match someone screwed with your database and you should raise an alarm.
4. You check the generation when checking the password. If it's an older
generation you can either regenerate the password entry with newer gen
information. Or you could force the user to change their password before
proceeding.
5. Alternately you could force the user to reauthenticate (security
questions, SMS codes, etc.) if it's an older generation of the password.
You might do that if you changed your algorithm 6 months ago because you
think it's become too weak, or immediately if you learn that your database
has been compromised.
Advanced 'best practices'
This isn't widely done but you can borrow ideas from other services.
Specifically the database should never reveal any information about the
password or salt. Instead you use an oracle approach - you call a UDF or
stored procedure with the uid and (hashed) password and it returns a
boolean value. True if it's acceptable, false otherwise. (Or it could
return a small enum if you wish to communicate additional details, e.g.,
that the password is old and should be updated, that the password is too
old and the user needs to reauthenticate via security methods or SMS code,
etc.). The initial hash can be pretty basic, you're just trying to prevent
someone from sniffing it off a network tap.
You also need a UDF or stored procedure that takes the uid and (hashed)
password and updates its own tables.
Those UDF or stored procedures handle the salt generation, hashing, and
hash generation management itself. It can write to a different schema that
the application user can't see. (Use 'DEFINER' security.) It can write to
an entirely separate database. With FDW it could write to an entirely
separate service, e.g., an LDAP server. The key point is that someone will
illicit access to the database as the application user can't ever see the
authentication information. With a different database or service they won't
even be able to find the authentication information even if they have
unlimited access to the database. They can't see entries, they can't insert
additional records, they can't copy the authentication information with a
known password into the columns for the target account.
FWIW I first saw this technique with LDAP servers and it's one reason why
I've become a fan of using LDAP to store the user authentication
information. It's a standard protocol, there are good servers, and as long
as you don't use the same database for your LDAP server the information is
totally inaccessible to someone with full access to your live database or
backups.
Bear
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres encryption
@ 2018-03-22 14:03 Bear Giles <bgiles@coyotesong.com>
parent: Bear Giles <bgiles@coyotesong.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bear Giles @ 2018-03-22 14:03 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Mar 22, 2018 at 7:19 AM, Bear Giles <bgiles@coyotesong.com> wrote:
> So you got into your target's database and want to know how to extract the
> passwords?... :-)
>
> First, nobody should be encrypting passwords. They should be hashed. (The
> sole exception is if your site is a proxy to a third site and it's now much
> more common to have a different type of authentication method than a stored
> password.) A single layer of hashing without salt isn't very strong anymore
> - I don't care what your hashing algorithm is - and a single layer of
> hashing with salt isn't particularly strong either since it's often
> misapplied.
>
> Going forward with some 'best practices'
>
> 1. Use one of the standard algorithms that will perform an iterative hash
> with salt for 1000 times. I think I put something up on my blog (
> https://invariantproperties.com) some time ago with sample code, or you
> can use the bcrypt2 library. Reminder that doing a hash with salt correctly
> is easy to screw up, e.g., H(salt|password) is far weaker than
> H(password|salt).
>
> 2. Add generation information to your salt and hash values. It can be
> encoded into a single column, e.g., 1$salt$hash, or in a UDT RECORD (gen,
> salt, hash). You'll need to keep track of what each generation means, e.g.,
> '1' might mean the legacy SHA-1 hash but 2 means salted SHA-512 or BCRYPT
> or something else.
>
> 3. If you want to prevent an attacker from creating an account and copying
> the salt + hash into a targeted account's entry you can add a few more
> columns or UDT RECORD: (uid, gen, salt, hash, hmac). The last item is a
> message digest (with password known to your app) on the other four entries.
> You would verify that before checking the hashed password - if it doesn't
> match someone screwed with your database and you should raise an alarm.
>
> 4. You check the generation when checking the password. If it's an older
> generation you can either regenerate the password entry with newer gen
> information. Or you could force the user to change their password before
> proceeding.
>
> 5. Alternately you could force the user to reauthenticate (security
> questions, SMS codes, etc.) if it's an older generation of the password.
> You might do that if you changed your algorithm 6 months ago because you
> think it's become too weak, or immediately if you learn that your database
> has been compromised.
>
>
> Advanced 'best practices'
>
> This isn't widely done but you can borrow ideas from other services.
> Specifically the database should never reveal any information about the
> password or salt. Instead you use an oracle approach - you call a UDF or
> stored procedure with the uid and (hashed) password and it returns a
> boolean value. True if it's acceptable, false otherwise. (Or it could
> return a small enum if you wish to communicate additional details, e.g.,
> that the password is old and should be updated, that the password is too
> old and the user needs to reauthenticate via security methods or SMS code,
> etc.). The initial hash can be pretty basic, you're just trying to prevent
> someone from sniffing it off a network tap.
>
> You also need a UDF or stored procedure that takes the uid and (hashed)
> password and updates its own tables.
>
> Those UDF or stored procedures handle the salt generation, hashing, and
> hash generation management itself. It can write to a different schema that
> the application user can't see. (Use 'DEFINER' security.) It can write to
> an entirely separate database. With FDW it could write to an entirely
> separate service, e.g., an LDAP server. The key point is that someone will
> illicit access to the database as the application user can't ever see the
> authentication information. With a different database or service they won't
> even be able to find the authentication information even if they have
> unlimited access to the database. They can't see entries, they can't insert
> additional records, they can't copy the authentication information with a
> known password into the columns for the target account.
>
> FWIW I first saw this technique with LDAP servers and it's one reason why
> I've become a fan of using LDAP to store the user authentication
> information. It's a standard protocol, there are good servers, and as long
> as you don't use the same database for your LDAP server the information is
> totally inaccessible to someone with full access to your live database or
> backups.
>
> Bear
>
I should add that you don't need three (or more) columns. You can use one
column with a separator, e.g., gen$base64-encoded salt$base64-encoded hash,
or even one of the 'wallets' and the like that provide (encrypted)
key-value pairs in a single base64-encoded string. I know the latter is
recommended for credit card information since it makes sure everything is
kept together and strongly encrypted. The only requirement is that the
separator not be one of the base-64 encoding characters: a-z, 0-9, +, and
/.
You definitely want a random per-user salt though, not a single
application-wide salt or something based on the uid.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-03-22 14:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-22 07:11 postgres encryption dbx postgres <dbx2postgres@gmail.com>
2018-03-22 09:53 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-03-22 13:19 ` Bear Giles <bgiles@coyotesong.com>
2018-03-22 14:03 ` Bear Giles <bgiles@coyotesong.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox