Re: How to make PostreSQL utilities honor home directories? - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: How to make PostreSQL utilities honor home directories?
Date
Msg-id 20220902112754.2e7fnrclod73d2sh@hjp.at
Whole thread Raw
In response to Re: How to make PostreSQL utilities honor home directories?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
On 2022-09-01 21:10:44 -0700, Adrian Klaver wrote:
> On 9/1/22 6:31 PM, Jeffrey Walton wrote:
> > On Thu, Sep 1, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > I concur with the other person asking why you want to sudo to postgres
> > > at all, though.  It's generally safest if the client side isn't running
> > > as the same user as the server.
> >
> > The use case is an install of DefectDojo [2]. I _think_ they are
> > taking advantage of the fact that as root, you don't need to
> > authenticate because of postgresql's use of domain sockets. (Hat tip
> > for that, by the way). The installer code will install packages, setup
> > the database, install the DefectDojo programs, etc.
>
> You are not running as root(Postgres won't allow you do this anyway) you are
> running as postgres system user and the authentication is handled by
> pg_hba.conf. I'm also betting that if you look at pg_hba.conf it is set up
> to do peer authentication and hence the need to be postgres system user. You
> can obtain a similar result in pg_hba.conf with:
>
> TYPE  DATABASE        USER            ADDRESS                 METHOD
> local  all            postgres                                trust
>
> Then you could eliminate the whole sudo dance.

OTOH it allows any user on the machine to connect as postgres, which may
or may not be a problem.

I like to use ident for local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident map=localusers

and in pg_ident.conf:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
localusers      root                    postgres

Then root can invoke `psql -U postgres ...`, but other users can't.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: Missing query plan for auto_explain.
Next
From: "Peter J. Holzer"
Date:
Subject: Re: How to make PostreSQL utilities honor home directories?