Thread: [GENERAL] Doubts regarding postgres Security

On Sun, Jan 22, 2017 at 12:32:21AM +0530, PAWAN SHARMA wrote:

> I have few question regarding Postgres security.
>
> 1.  How can we set user account block feature after max number of
> invalid password entries?
> 2.  How can we use SSL encryption in Postgres on Linux environment?
> 3.  How can we transparent data encryption in Postgres?

The information you need to answer your questions is found here:

    https://www.postgresql.org/docs/devel/static/index.html

Regards,
Karsten Hilbert
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Pawan,

* PAWAN SHARMA (er.pawanshr0963@gmail.com) wrote:
> 1.  How can we set user account block feature after max number of
> invalid password entries?

There are ways to accomplish this, but they're unfortunately
complicated.  In the past, I've implemented these kinds of requirments
by using the 'PAM' auth method and configuring saslauthd and PAM with
various PAM modules.  Unfortunately, most PAM modules are not very well
suited to running as non-root or with alternate directories, so it can
be a bit tricky.

> 2.  How can we use SSL encryption in Postgres on Linux environment?

This can be a bit tricky too, depending on your PKI, but generally
speaking, you simply need to configure the SSL-relevant options in
postgresql.conf and then set SSLMODE=verify-ca (or verify-full,
depending on what you want to do).

Specific documentiaton is available here:

https://www.postgresql.org/docs/current/static/ssl-tcp.html

> 3.  How can we transparent data encryption in Postgres?

If you mean between PG and the filesystem, you could either use full
disk encryption provided by the OS, or like pgcrypto.

Documentation for pgcrypto is here:

https://www.postgresql.org/docs/current/static/pgcrypto.html

Thanks!

Stephen

Pawan,

Please be sure to include the mailing list on replies, so others can
benefit from the discussion.  Also, please reply in-line, as I do below,
instead of top-posting.

* PAWAN SHARMA (er.pawanshr0963@gmail.com) wrote:
> So, there is no solution for my first question, we need if users enter the
> wrong password more than 5 times than their account gets locked and then
> only DBA will unlock this account.

I understood the question and there is an answer- use PAM.

The other option is to not use password-based authentication with PG and
instead use a better method such as certificate-based auth or GSSAPI.

Thanks!

Stephen

Stephen Frost schrieb am 21.01.2017 um 22:37:
>> So, there is no solution for my first question, we need if users enter the
>> wrong password more than 5 times than their account gets locked and then
>> only DBA will unlock this account.
>
> I understood the question and there is an answer- use PAM.
>

What about LDAP?

* Thomas Kellerer (spam_eater@gmx.net) wrote:
> Stephen Frost schrieb am 21.01.2017 um 22:37:
> >>So, there is no solution for my first question, we need if users enter the
> >>wrong password more than 5 times than their account gets locked and then
> >>only DBA will unlock this account.
> >
> >I understood the question and there is an answer- use PAM.
>
> What about LDAP?

Perhaps, but not what I'd recommend.  If you've got LDAP then you
probably have Active Directory and you should just be using GSSAPI.

If you've actually *only* got LDAP, then perhaps, but all the LDAP
methods require the user's password to be sent, in one form or another,
to the PG server, which isn't ideal, and usually there's a better option
in such environments.

Of course, PAM requires the user's password to be sent to the server
also, which is also why I don't generally recommend using it either, but
without more info ...

Thanks!

Stephen

* PAWAN SHARMA (er.pawanshr0963@gmail.com) wrote:

So, there is no solution for my first question, we need if users enter the
wrong password more than 5 times than their account gets locked and then
only DBA will unlock this account.

I understood the question and there is an answer- use PAM.

The other option is to not use password-based authentication with PG and
instead use a better method such as certificate-based auth or GSSAPI.

or just don't let 'users' connect directly to the database at all.    users run an app (or browser), which talks to an app server, which has the business logic and does the database connection and executes the business logic specific queries.

-- 
john r pierce, recycling bits in santa cruz