Thread: 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: https://www.postgresql.org/message-id/flat/CAE35ztOGZqgwae3mBA%3DL97pSg3kvin2xycQh%3Dir%3D5NiwCApiYQ%40mail.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. > 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 byeveryone, 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 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