What are best practices wrt passwords?
Hello all,
I'd like to be able to use psql without typing passwords again and
again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
want to use it - I have the password in the `.env` file, and having it
in _two_ places comes with its own set of problems, like how to make
sure they don't get out of sync.
I understand why giving the password on the command line or in an
environment variable is a security risk (because of `ps`), but I do not
understand why `psql` doesn't have an option like `--password-command`
accepting a command which then prints the password on stdout. For
example, I could then use `pass` (https://www.passwordstore.org/) with
gpg-agent.
Is there any risk associated with this usage pattern? What is the
recommended practice in my case other than using `.pgpass`?
Thanks in advance,
P.S. Please CC me in replies, since I'm not subscribed to the list.
Thanks.
--
Marcin Borkowski
https://mbork.pl
https://crimsonelevendelightpetrichor.net/
On Wed, Oct 16, 2024 at 2:25 PM <mbork@mbork.pl> wrote:
I'd like to be able to use psql without typing passwords again and
again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
want to use it - I have the password in the `.env` file, and having it
in _two_ places comes with its own set of problems, like how to make
sure they don't get out of sync.
What's wrong with PGPASSWORD?
https://www.postgresql.org/docs/current/libpq-envars.html
I understand why giving the password on the command line or in an
environment variable is a security risk (because of `ps`), but I do not
understand why `psql` doesn't have an option like `--password-command`
accepting a command which then prints the password on stdout. For
example, I could then use `pass` (https://www.passwordstore.org/) with
gpg-agent.
It's not psql, it's libpq, that does that, FTR.
My own apps are libpq based, and inherit all its env-vars and defaults.
But I'd welcome a way to store password encrypted,
unlike the current mechanisms. And what you propose
would allow that I guess, if I understand correctly. So +1.
(and since transient better than enrypted/obfuscated passwords)
Is there any risk associated with this usage pattern? What is the
recommended practice in my case other than using `.pgpass`?
Storing password in plain text? --DD
Use the PGPASSWORD environment variable.
Example:
SET PGPASSWORD=P0stgres
psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT 1;'"
https://www.postgresql.org/docs/current/libpq-envars.html
On Wednesday, October 16, 2024 at 08:26:05 AM GMT-4, <mbork@mbork.pl> wrote:
Hello all,
I'd like to be able to use psql without typing passwords again and
again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
want to use it - I have the password in the `.env` file, and having it
in _two_ places comes with its own set of problems, like how to make
sure they don't get out of sync.
I understand why giving the password on the command line or in an
environment variable is a security risk (because of `ps`), but I do not
understand why `psql` doesn't have an option like `--password-command`
accepting a command which then prints the password on stdout. For
example, I could then use `pass` (https://www.passwordstore.org/) with
gpg-agent.
Is there any risk associated with this usage pattern? What is the
recommended practice in my case other than using `.pgpass`?
Thanks in advance,
P.S. Please CC me in replies, since I'm not subscribed to the list.
Thanks.
--
Marcin Borkowski
https://mbork.pl
https://crimsonelevendelightpetrichor.net/
On 2024-Oct-16, mbork@mbork.pl wrote:
I understand why giving the password on the command line or in an
environment variable is a security risk (because of `ps`), but I do not
understand why `psql` doesn't have an option like `--password-command`
accepting a command which then prints the password on stdout. For
example, I could then use `pass` (https://www.passwordstore.org/) with
gpg-agent.
We had a patch to add PGPASSCOMMAND once:
/messages/by-id/CAE35ztOGZqgwae3mBA=L97pSg3kvin2xycQh=ir=5NiwCApiYQ@mail.gmail.com
I don't remember the overall conclusions (other than the patch being
rejected), but maybe you can give that a read.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On 2024-10-16, at 14:41, Dominique Devienne <ddevienne@gmail.com> wrote:
On Wed, Oct 16, 2024 at 2:25 PM <mbork@mbork.pl> wrote:
I'd like to be able to use psql without typing passwords again and
again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
want to use it - I have the password in the `.env` file, and having it
in _two_ places comes with its own set of problems, like how to make
sure they don't get out of sync.What's wrong with PGPASSWORD?
https://www.postgresql.org/docs/current/libpq-envars.html
`ps auxe` shows all processes with their environments, no?
I understand why giving the password on the command line or in an
environment variable is a security risk (because of `ps`), but I do not
understand why `psql` doesn't have an option like `--password-command`
accepting a command which then prints the password on stdout. For
example, I could then use `pass` (https://www.passwordstore.org/) with
gpg-agent.It's not psql, it's libpq, that does that, FTR.
Good point, thanks.
My own apps are libpq based, and inherit all its env-vars and defaults.
But I'd welcome a way to store password encrypted,
unlike the current mechanisms. And what you propose
would allow that I guess, if I understand correctly. So +1.
(and since transient better than enrypted/obfuscated passwords)Is there any risk associated with this usage pattern? What is the
recommended practice in my case other than using `.pgpass`?Storing password in plain text? --DD
You have to store it somewhere on the server where your application
(which connects to the database) lives anyway, right? I see no
significant difference wrt security between .env and .pgpass. (Though
I'm far from a security expert.)
Best,
--
Marcin Borkowski
https://mbork.pl
https://crimsonelevendelightpetrichor.net/
On Wed, Oct 16, 2024 at 06:16:57PM +0200, mbork@mbork.pl wrote:
On 2024-10-16, at 14:41, Dominique Devienne <ddevienne@gmail.com> wrote:
On Wed, Oct 16, 2024 at 2:25 PM <mbork@mbork.pl> wrote:
I'd like to be able to use psql without typing passwords again and
again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
want to use it - I have the password in the `.env` file, and having it
in _two_ places comes with its own set of problems, like how to make
sure they don't get out of sync.What's wrong with PGPASSWORD?
https://www.postgresql.org/docs/current/libpq-envars.html`ps auxe` shows all processes with their environments, no?
I think that only shows for super-user.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Oct 16, 2024 at 06:16:57PM +0200, mbork@mbork.pl wrote:
`ps auxe` shows all processes with their environments, no?
I think that only shows for super-user.
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
to work for processes owned by yourself even if you're not superuser.
regards, tom lane
On Oct 16, 2024, at 09:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
to work for processes owned by yourself even if you're not superuser.
I just tried it on an (admittedly kind of old) Ubuntu system and MacOS 14, and it looks like shows everything owned by everyone, even from a non-sudoer user.
Στις 16/10/24 19:47, ο/η Tom Lane έγραψε:
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Oct 16, 2024 at 06:16:57PM +0200, mbork@mbork.pl wrote:
`ps auxe` shows all processes with their environments, no?
I think that only shows for super-user.
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
As of FreeBSD smadevnu 14.1-RELEASE-p5 , only the user and root can view
the env, such as PGPASSWORD of a user. Other users can't.
Show quoted text
to work for processes owned by yourself even if you're not superuser.
regards, tom lane
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> writes:
Στις 16/10/24 19:47, ο/η Tom Lane έγραψε:
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
As of FreeBSD smadevnu 14.1-RELEASE-p5 , only the user and root can view
the env, such as PGPASSWORD of a user. Other users can't.
NetBSD 10 seems to behave the same. I don't have a recent OpenBSD
installation to try.
regards, tom lane
On Oct 16, 2024, at 10:50 AM, Christophe Pettus <xof@thebuild.com> wrote:
On Oct 16, 2024, at 09:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
to work for processes owned by yourself even if you're not superuser.I just tried it on an (admittedly kind of old) Ubuntu system and MacOS 14, and it looks like shows everything owned by everyone, even from a non-sudoer user.
Interesting, that’s not my experience. Only root can see the env variables of another user.
Terminal 1
$ cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.6 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.6 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal
$ whoami
testusr
$ export FOOBAR=true
$ bash
$ env | grep FOOBAR
FOOBAR=true
Terminal 2
$ whoami
mtice
$ ps e -U testusr | grep -c FOOBAR
0
$ sudo ps e -U testusr | grep -c FOOBAR
1
On 2024-10-16 09:50:41 -0700, Christophe Pettus wrote:
On Oct 16, 2024, at 09:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
to work for processes owned by yourself even if you're not superuser.I just tried it on an (admittedly kind of old) Ubuntu system and MacOS
14, and it looks like shows everything owned by everyone, even from a
non-sudoer user.
On Linux, unprivileged users can only see the environment of their own
processes since a *very* long time ago. Possibly even before Ubuntu even
existed. So I'm somewhat sceptical about that. Some other Unixes were
more permissive. I don't know what camp MacOS falls into.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On Wed, Oct 16, 2024 at 11:27:15PM +0200, Peter J. Holzer wrote:
On 2024-10-16 09:50:41 -0700, Christophe Pettus wrote:
On Oct 16, 2024, at 09:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I believe it depends on your platform --- some BSDen are pretty
permissive about this, if memory serves. On a Linux box it seems
to work for processes owned by yourself even if you're not superuser.I just tried it on an (admittedly kind of old) Ubuntu system and MacOS
14, and it looks like shows everything owned by everyone, even from a
non-sudoer user.On Linux, unprivileged users can only see the environment of their own
processes since a *very* long time ago. Possibly even before Ubuntu even
existed. So I'm somewhat sceptical about that. Some other Unixes were
more permissive. I don't know what camp MacOS falls into.
Yes, I thought this was fixed long ago.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
On Linux, unprivileged users can only see the environment of their own
processes since a *very* long time ago. Possibly even before Ubuntu even
existed. So I'm somewhat sceptical about that. Some other Unixes were
more permissive. I don't know what camp MacOS falls into.
I poked at that on current macOS (Sequoia). The rule for unprivileged
users seems to be the same as on Linux: you can see the environment
variables of processes belonging to you. What is really interesting
is that "sudo ps auxwwe" and "ps auxwwe" have the same results: you
can still see only your own processes' environment variables. The
sole exception when I tried it was that under sudo the root-owned "ps"
command showed its own environment variables, which is pretty bizarre.
Looks like the test is not actually on the reported process ownership
but some inherited property.
To confuse matters even more, another machine that's running Sequoia
but with SIP turned off acts more like Linux: "sudo ps auxwwe" shows
environment variables for everything. So Apple's marching to their
own drummer as usual, but it doesn't look like there's any case where
they are more permissive than the Linux rule. I'm still not convinced
about whether all *BSD flavors match that, though.
regards, tom lane