Re: How to make PostreSQL utilities honor home directories?

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(at)sss(dot)pgh(dot)pa(dot)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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"