Thread

1. What are best practices wrt passwords?

   mbork@mbork.pl — 2024-10-16T09:35:46Z

   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/
   
   
   
   

2. Re: What are best practices wrt passwords?

   Dominique Devienne <ddevienne@gmail.com> — 2024-10-16T12:41:47Z

   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
   
   
   
   

3. Re: What are best practices wrt passwords?

   felix.quintgz@yahoo.com — 2024-10-16T14:16:45Z

   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/
   
   
   
   

4. Re: What are best practices wrt passwords?

   Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-10-16T14:37:11Z

   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/
   
   
   
   

5. Re: What are best practices wrt passwords?

   mbork@mbork.pl — 2024-10-16T16:16:57Z

   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/
   
   
   
   

6. Re: What are best practices wrt passwords?

   Bruce Momjian <bruce@momjian.us> — 2024-10-16T16:33:01Z

   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?"
   
   
   
   

7. Re: What are best practices wrt passwords?

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-16T16:47:25Z

   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
   
   
   
   

8. Re: What are best practices wrt passwords?

   Christophe Pettus <xof@thebuild.com> — 2024-10-16T16:50:41Z

   
   > 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.
   
   
   

9. Re: What are best practices wrt passwords?

   Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2024-10-16T18:29:20Z

   Στις 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
   >
   >
   
   
   
   

10. Re: What are best practices wrt passwords?

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-16T18:47:05Z

    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
    
    
    
    

11. Re: What are best practices wrt passwords?

    Matthew Tice <mjtice@gmail.com> — 2024-10-16T19:07:37Z

    
    > 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
    
    
    
    
    

12. Re: What are best practices wrt passwords?

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2024-10-16T21:27:15Z

    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!"
    

13. Re: What are best practices wrt passwords?

    Bruce Momjian <bruce@momjian.us> — 2024-10-16T21:37:19Z

    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?"
    
    
    
    

14. Re: What are best practices wrt passwords?

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-16T22:03:15Z

    "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