Thread: [GENERAL] Doubts regarding postgres Security

[GENERAL] Doubts regarding postgres Security

From
PAWAN SHARMA
Date:
Hi All,

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?

Thanks

Re: [GENERAL] Doubts regarding postgres Security

From
Karsten Hilbert
Date:
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


Re: [GENERAL] Doubts regarding postgres Security

From
Stephen Frost
Date:
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

Attachment

Re: [GENERAL] Doubts regarding postgres Security

From
Stephen Frost
Date:
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

Attachment

Re: [GENERAL] Doubts regarding postgres Security

From
Thomas Kellerer
Date:
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?




Re: [GENERAL] Doubts regarding postgres Security

From
Stephen Frost
Date:
* 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

Attachment

Re: [GENERAL] Doubts regarding postgres Security

From
John R Pierce
Date:
On 1/21/2017 1:37 PM, Stephen Frost wrote:
* 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