Standby server does not start with base backup

Lists: pgsql-general
From: Manuel Gysin <manuel(dot)gysin(at)quantum-bytes(dot)com>
To: craig(at)postnewspapers(dot)com(dot)au, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Encryption For Specific Column- Where to store the key
Date: 2011-06-16 08:35:13
Message-ID: 11621495-6166-4d15-8b72-ae2db03dcd39@mail
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

>From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
>
>Hello
>
>try to use a security definer functions
>
>http://www.postgresql.org/docs/current/static/sql-createfunction.html
>
>inside this function you can access to resourcess thats are no
>available from outer for web user
>
>Regards
>
>Pavel Stehuke

I understand the idea behind it but it does not protect me when someone can dump the whole database.
He can simply change the user credentials and can access this function. But anyway thanks for the hint, it's useful to improve security!

>From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
>
>securing the backend from the dba is basically impossible. you can
>make the client pretty secure, but the only way your encryption can be
>reasonably enforced is for both the encryption and decryption to
>happen on the client side -- the key cannot and should not be
>possessed by anyone who is not trustworthy.
>
>merlin

I can trust the dba. But while someone gain access, he can control everything and could easily extend his rights to dba.
An other way with client side encryption/decryption should be possible with deployed certificates and keys, but so only the user has access to the data.

The case is that I store private information like address, telephone, e-mail and maybe credit card information.
For billing or control of the customer data I need to access to the data, with a client en/decryption this is *not possible.
(*I'm not sure but I read sometime ago something about a master key which could decrypt data encrypted with the child keys, this would be a way to solve the problem. An other way is to save the decryption keys to a safe place and only load it when access is needed. Else the decryption is made through the client. (With Java Script this should be easily possible))
Thanks for this hint, it seems a very elegant way to solve my problem! The deployment seems a problem while we are a "open to register project" and I fear we get much support calls about this, but security is it worth. The only big problem is when the user has lost his key, then it could be complicated.

>From: "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au>
>Make sure you use a salt value and store the salt as well as the
>password. If not salted, your passwords will be easily cracked with a
>rainbow table if someone manages to dump them.

Password it salted and hashed multiple times. Simple salt + hash is no longer secure since there so much cpu power in the cloud.
I used "crypt('12345', gen_salt('bf', 16))" for it.
Some nice information could be found under http://codahale.com/how-to-safely-store-a-password/ if anyone is interested in this topic.

>You won't have much luck with that. It only takes one "SELECT * FROM
>..." to bypass your query rate limiting. You could force everything
>through stored procedures, but that'll be slow and clumsy.
>
>Good intrusion detection and system monitoring so that you detect
>unusual events is probably a safter bet than trying to make your system
>fail when load spikes.

Yes, I know. This security measurements are being implemented.
I just try to protect from the worst case scenario, when intrusion detection fails.

>Yep. There you can do per-IP or per-range rate limiting, among other
>things, which will help. I'd probably want to do this using an intrusion
>detection/prevention system probably running as a reverse http proxy.

I need to read me deeper in this topic, thanks for the hint!

>Not much, no. The main benefit to encrypting some data in the database
>is to make it harder to use a stolen dump or extracted content. It can't
>make it impossible so long as your system can use the content too.
>
>I'd certainly avoid keeping the key in the database.
>
>If you don't need the capability for unattended web front-end re-start,
>you can have your web frontend store the key encrypted on disk and
>require interactive password entry to decrypt the key before it can
>start. It can then keep the key in RAM and forget the password. If your
>frontend is utterly compromised and someone has the time to do the
>analysis you're still busted, but it'll slow them down a bunch. If you
>want to be viewed as a frothing paranoid you can even store the
>encrypted key on the web frontend's HDD but on a separate SD card or
>thumb drive that requires physical insertion. I wouldn't.
>
>Personally, I'd probably just keep the key on the web frontend machine
>either unencrypted or encrypted with a passphrase coded into the
>frontend app. At least that way someone who manages to dump some of your
>tables or steal a database dump will need to steal - and know they need
>to steal - something completely different as well before they can use
>the stolen information.

I don't know, somehow I do not like the idea to save it on the frontend.
I think I try to implemented a Java Script based client en/decryption, with storing the information on creating to a drive which could only be written but not read.
(Or your idea mentioned some lines further down)

>It depends a lot on what the trade-off between convenience/performance
>and security is. How often is the data you want to encrypt accessed? Is
>it acceptable to require interactive authorization or input before
>encrypted data can be decrypted? Are the people who add sensitive data
>the same ones who need to be able to read it back out again, or can you
>restrict the group who can read it to a smaller group of users?

Data is only needed to the user who insert it. It's a simply database with personal information.
The person could only see his own data, editing should be possible.

>Use Stack Overflow instead ;-)
>

I did not post the question, found it while doing a google research. :)

>Anyway: Given the additional detail you provided in that post, where it
>becomes clear that you only need to be able to *read* the CCNs
>occasionally and only in batches, perhaps you should consider using
>public key crypto.
>
>Store the last 4 digits of the CCN unencrypted but not the CVV or expiry
>time. That way you can show a hint to the user about which card you're
>using without them (or anyone else) being able to extract the full details.
>
>Encrypt the full details using a public key when you store them in the
>database. The web front end only needs to know the public key to encrypt
>data. It doesn't need to know the private key, and without it it cannot
>*decrypt* the data again.
>
>Now your batch invoicing program can load the private key off a USB key
>or SD card - or just keep it on disk and decrypt it using a strong
>passphrase that is hand-entered by a user. When you're not doing
>invoicing and billing, your system doesn't have any access to the
>private key and cannot decrypt the stored data.
>
>If you switch to rolling billing later on, you'll need to adjust this
>process, but it still allows you to keep the part that can read the
>credit card numbers very separate from the part that interacts with
>untrusted users over the Internet.

It's not exactly my use case, but there are some nice ideas in your answer. (Sorry, should wrote it that the content of the posted link is not from me)
What I could do for the fields which needs protection is the store the first or last letter as plain text, so the user knows what it is but an attacker who gains access not.

While I need to control the data for billing or validity I can load the key from a usb stick or so like you mentioned above and else there is no access to the data. This sounds very good! (While the user knows what data he provided, he has no problems to read "Main****** 34" as his address, but an attacker can't do much with this data.)
Thanks a lot!

Kind Regards
Manuel Gysin


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Manuel Gysin <manuel(dot)gysin(at)quantum-bytes(dot)com>
Cc: craig(at)postnewspapers(dot)com(dot)au, Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Encryption For Specific Column- Where to store the key
Date: 2011-06-16 08:41:40
Message-ID: BANLkTik0ezHpbp3dNKsy_D6LLKdkgsdOOA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

2011/6/16 Manuel Gysin <manuel(dot)gysin(at)quantum-bytes(dot)com>:
>>From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
>>
>>Hello
>>
>>try to use a security definer functions
>>
>>http://www.postgresql.org/docs/current/static/sql-createfunction.html
>>
>>inside this function you can access to resourcess thats are no
>>available from outer for web user
>>
>>Regards
>>
>>Pavel Stehuke
>
> I understand the idea behind it but it does not protect me when someone can dump the whole database.
> He can simply change the user credentials and can access this function. But anyway thanks for the hint, it's useful to improve security!
>

if attacker can dump a database, then any protection is terrible hard
or impossible :(

if you store some very good salted hash instead password to database,
then access to dump isn't helpful for attacker.

Regards

Pavel Stehule

p.s. any security protections are thin without full control over server.


From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Manuel Gysin <manuel(dot)gysin(at)quantum-bytes(dot)com>
Cc: craig(at)postnewspapers(dot)com(dot)au, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Encryption For Specific Column- Where to store the key
Date: 2011-06-16 13:55:28
Message-ID: BANLkTik1m67PVJqU8VOFYUemmjNgDXHqmQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Thu, Jun 16, 2011 at 3:35 AM, Manuel Gysin
<manuel(dot)gysin(at)quantum-bytes(dot)com> wrote:
> I can trust the dba. But while someone gain access, he can control everything and could easily extend his rights to dba.
> An other way with client side encryption/decryption should be possible with deployed certificates and keys, but so only the user has access to the data.

With all due respect, I don't think you can trust the dba. People are
always the weakest link in any security chain.

Don't think of the specific person, but more the role involved and
what data that person should be allowed to see. Imagine explaining to
your customers that certain of your personnel will have access to
credit card numbers or other highly sensitive information. Imagine
explaining it to an auditor. OTOH, maybe your security expectations
are a little too high -- it's always a tradeoff between security and
usability (this is why we don't encrypt addresses).

If you are talking about the specific case of credit card number
processing, there are strict protocols that have to be followed in
order to be able to do that. You must submit to an audit in order to
be able to do that (google pci compliance).

Password hashing is another thing -- randomly chosen defenses (like
repeat hashing or combining algorithms) are, IMNSHO, useless. Better
to use salt and, if necessary, do not do the hashing or store the salt
on the server.

merlin


From: Sanjay Rao <srao(at)noida(dot)interrasystems(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Standby server does not start with base backup
Date: 2011-06-18 08:57:30
Message-ID: 4DFC687A.8080703@noida.interrasystems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Hi,

I am running a test setup for verifying the warm standby feature in
Postgres 9.0 release. Both my Master (Win 7 Professional, 64 bit) &
Standby (Win 7
Professional, 64 bit) machines are having Postgres 9.0
(postgresql-9.0.4-1-windows) installed & both are in our LAN environment.

After taking a base backup of the master database using
pg_start_backup() & copy of the data folder to Standby machine followed
by pg_stop_backup() in Master, the Standby server is started. The
Standby server logs show the following error and the standby Postgres
server shuts down. After this it never starts

2011-06-18 13:56:32 IST LOG: entering standby mode
2011-06-18 13:56:32 IST LOG: streaming replication successfully
connected to primary
2011-06-18 13:56:32 IST FATAL: the database system is starting up
2011-06-18 13:56:33 IST LOG: redo starts at 0/3000020
2011-06-18 13:56:33 IST FATAL: the database system is starting up
2011-06-18 13:56:34 IST LOG: consistent recovery state reached at 0/5000000
2011-06-18 13:56:34 IST FATAL: the database system is starting up
2011-06-18 13:56:35 IST FATAL: the database system is starting up
2011-06-18 13:56:36 IST FATAL: the database system is starting up
2011-06-18 13:56:37 IST FATAL: the database system is starting up
2011-06-18 13:56:38 IST FATAL: the database system is starting up
2011-06-18 13:56:39 IST FATAL: the database system is starting up
.
.
.
.and same message continues..

I have attached the configuration files of standby server. Please help
me if anybody have any such experience or know the exact cause of the issue.

Regards,
Sanjay

Attachment Content-Type Size
pg_hba.conf text/plain 3.7 KB
postgresql.conf text/plain 18.2 KB
recovery.conf text/plain 4.2 KB

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: srao(at)interrasystems(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Standby server does not start with base backup
Date: 2011-06-18 12:40:18
Message-ID: BANLkTim2M+Yrp1xAXLD+GBMnLPkeG1y-fA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Sat, Jun 18, 2011 at 10:57, Sanjay Rao <srao(at)noida(dot)interrasystems(dot)com> wrote:
> Hi,
>
> I am running a test setup for verifying the warm standby feature in Postgres
> 9.0 release. Both my Master (Win 7 Professional, 64 bit) & Standby (Win 7
> Professional, 64 bit) machines are having Postgres 9.0
> (postgresql-9.0.4-1-windows) installed & both are in our LAN environment.
>
> After taking a base backup of the master database using pg_start_backup() &
> copy of the data folder to Standby machine followed by pg_stop_backup() in
> Master, the Standby server is started.  The Standby server logs show the
> following error and the standby Postgres server shuts down. After this it
> never starts
>
> 2011-06-18 13:56:32 IST LOG:  entering standby mode
> 2011-06-18 13:56:32 IST LOG:  streaming replication successfully connected
> to primary
> 2011-06-18 13:56:32 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:33 IST LOG:  redo starts at 0/3000020
> 2011-06-18 13:56:33 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:34 IST LOG:  consistent recovery state reached at 0/5000000
> 2011-06-18 13:56:34 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:35 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:36 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:37 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:38 IST FATAL:  the database system is starting up
> 2011-06-18 13:56:39 IST FATAL:  the database system is starting up
> .
> .
> .
> .and same message continues..
>
> I have attached the configuration files of standby server. Please help me if
> anybody have any such experience or know the exact cause of the issue.

That seems perfectly normal. There's nothing in those logs indicating
that the standby shut down - if it did, there should be some other
messages when that happens.

If you want to be able to actually connect to the slave, you need to
run in hot standby.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/