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

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

Hi,
maybe you want to use [1]https://www.postgresql.org/docs/11/pgcrypto.html pgcrypto encrypt/decrypt function using "secret"
word stored outside database.

See F.25.4. Raw Encryption Functions

[1]: https://www.postgresql.org/docs/11/pgcrypto.html

Regards,

Il giorno gio 19 set 2019 alle ore 16:19 Adrian Klaver <
adrian.klaver@aklaver.com> ha scritto:

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