Thread: PGPASSWORD in crypted form, for example BlowFish or SHA-256

Hello,

Our software, a huge ILS, is running on Linux with DBS Sybase. To
connect to the Sybase server (over the network, even on localhost),
credentials must be known: a user (say 'sisis') and its password.

For Sybase we have them stored on the disk of the system in a file
syb.npw as:

$ cat /opt/lib/sisis/etc/syb/syb.npw
sisis:e53902b9923ab2fb
sa:64406def48efca8c

for the user 'sisis' and the administrator 'sa'. Our software has as
shared library a blob which knows how to decrypt the password hash above
shown as 'e53902b9923ab2fb' into clear text which is then used in the
ESQL/C or Java layer to connect to the Sybase server.

For PostgreSQL the password must be typed in (for pgsql) or can be
provided in an environment variable PGPASSWORD=blabla

Is there somehow an API in PG to use ciphered passwords and provide as a
shared library the blob to decrypt it? If not, we will use the mechanism same as
we use for Sybase. Or any other idea to not make detectable the
credentials? This was a request of our customers some years ago.

    matthias


-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Mientras haya voluntad de lucha habrá esperanza de vencer.

Hello,

On Thu, 2019-09-19 at 12:30 +0200, Matthias Apitz wrote:
> Hello,
> 
> Our software, a huge ILS, is running on Linux with DBS Sybase. To
> connect to the Sybase server (over the network, even on localhost),
> credentials must be known: a user (say 'sisis') and its password.
> 
> For Sybase we have them stored on the disk of the system in a file
> syb.npw as:
> 
> $ cat /opt/lib/sisis/etc/syb/syb.npw
> sisis:e53902b9923ab2fb
> sa:64406def48efca8c
> 
> for the user 'sisis' and the administrator 'sa'. Our software has as
> shared library a blob which knows how to decrypt the password hash
> above
> shown as 'e53902b9923ab2fb' into clear text which is then used in the
> ESQL/C or Java layer to connect to the Sybase server.
> 
> For PostgreSQL the password must be typed in (for pgsql) or can be
> provided in an environment variable PGPASSWORD=blabla
> 
> Is there somehow an API in PG to use ciphered passwords and provide
> as a
> shared library the blob to decrypt it? If not, we will use the
> mechanism same as
> we use for Sybase. Or any other idea to not make detectable the
> credentials? This was a request of our customers some years ago.
> 
>     matthias
> 
> 


https://www.postgresql.org/docs/11/auth-password.html

Chapters 20.5 and 20.6 may give you more information.

HTH,
Robert

El día Thursday, September 19, 2019 a las 10:31:01PM +1000, rob stone escribió:

> Hello,
> 
> On Thu, 2019-09-19 at 12:30 +0200, Matthias Apitz wrote:
> > Hello,
> > 
> > Our software, a huge ILS, is running on Linux with DBS Sybase. To
> > connect to the Sybase server (over the network, even on localhost),
> > credentials must be known: a user (say 'sisis') and its password.
> > 
> > For Sybase we have them stored on the disk of the system in a file
> > syb.npw as:
> > 
> > $ cat /opt/lib/sisis/etc/syb/syb.npw
> > sisis:e53902b9923ab2fb
> > sa:64406def48efca8c
> > 
> > for the user 'sisis' and the administrator 'sa'. Our software has as
> > shared library a blob which knows how to decrypt the password hash
> > above
> > shown as 'e53902b9923ab2fb' into clear text which is then used in the
> > ESQL/C or Java layer to connect to the Sybase server.
> > 
> > For PostgreSQL the password must be typed in (for pgsql) or can be
> > provided in an environment variable PGPASSWORD=blabla
> > 
> > Is there somehow an API in PG to use ciphered passwords and provide
> > as a
> > shared library the blob to decrypt it? If not, we will use the
> > mechanism same as
> > we use for Sybase. Or any other idea to not make detectable the
> > credentials? This was a request of our customers some years ago.
> > 
> 
> 
> https://www.postgresql.org/docs/11/auth-password.html
> 
> Chapters 20.5 and 20.6 may give you more information.

The form of the password hash store in the PG server or interchange over
the network is not my question. The question is more: When the Linux
server starts and with this the (ESQL/C written) application servers are
starting, they need the password to connect and this is not provided at
this moment from some keyboard or humanbeing. It must be stored on the
server and available in clear for the server, but not for other eyes on
the server, i.e. the place of the sorage must be ciphered.

    matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Matthias Apitz <guru@unixarea.de> writes:
> Is there somehow an API in PG to use ciphered passwords and provide as a
> shared library the blob to decrypt it?

No.  Consider a non-password auth mechanism, for instance SSL
certificates.  You might find that an SSL certificate file
stored where libpq will find it is already about as secure as
what you're doing now.  If you want to jump through extra
hoops for more security, I think you can use ssh-agent to
hold the keys.

            regards, tom lane

On 9/19/19 3:30 AM, Matthias Apitz wrote:
> 
> Hello,
> 
> Our software, a huge ILS, is running on Linux with DBS Sybase. To
> connect to the Sybase server (over the network, even on localhost),
> credentials must be known: a user (say 'sisis') and its password.
> 
> For Sybase we have them stored on the disk of the system in a file
> syb.npw as:
> 
> $ cat /opt/lib/sisis/etc/syb/syb.npw
> sisis:e53902b9923ab2fb
> sa:64406def48efca8c
> 
> for the user 'sisis' and the administrator 'sa'. Our software has as
> shared library a blob which knows how to decrypt the password hash above
> shown as 'e53902b9923ab2fb' into clear text which is then used in the
> ESQL/C or Java layer to connect to the Sybase server.
> 
> For PostgreSQL the password must be typed in (for pgsql) or can be
> provided in an environment variable PGPASSWORD=blabla
> 
> Is there somehow an API in PG to use ciphered passwords and provide as a
> shared library the blob to decrypt it? If not, we will use the mechanism same as

There is not and I am not sure that would be much use even if it did 
exist. You would be right back at someone being able to grab the 
credentials from a file and feeding them to the database for access.

The system you currently have at least seems to limit access to a 
specific program external to Postgres.

> we use for Sybase. Or any other idea to not make detectable the
> credentials? This was a request of our customers some years ago.
> 
>     matthias
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 2019-09-19 at 15:23 +0200, Matthias Apitz wrote:
> El día Thursday, September 19, 2019 a las 10:31:01PM +1000, rob stone
> escribió:
> 
> > 
> > https://www.postgresql.org/docs/11/auth-password.html
> > 
> > Chapters 20.5 and 20.6 may give you more information.
> 
> The form of the password hash store in the PG server or interchange
> over
> the network is not my question. The question is more: When the Linux
> server starts and with this the (ESQL/C written) application servers
> are
> starting, they need the password to connect and this is not provided
> at
> this moment from some keyboard or humanbeing. It must be stored on
> the
> server and available in clear for the server, but not for other eyes
> on
> the server, i.e. the place of the sorage must be ciphered.
> 
>     matthias
> 

Sorry. More caffeine needed.

If you use pg_service.conf you could write a little program to encrypt
the password and store it in this file in its encrypted form.
Then you application obtains the connection credentials from
pg_service.conf, de-encrypts the password and is then able to form the
connection string to access the required database.

HTH,
Robert