Re: Force pg_hba.conf user with LDAP

On Mon, Aug 1, 2016 at 5:21 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Mon, Aug 1, 2016 at 1:32 PM, John McKown
> <john(dot)archie(dot)mckown(at)gmail(dot)com> wrote:
> > On Mon, Aug 1, 2016 at 2:49 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> >>
> >> On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh <
> jkregloh(at)sproutloud(dot)com>
> >> wrote:
> >> > Hi,
> >> >
> >> > Is there a way to force the user being sent to LDAP?
> >> >
> >> > For example I have the following entry in my pg_hba.conf file:
> >> > host apdb apuser 10.0.20.1/22 ldap
> >> > ldapserver="389-ds1.sl.com:389" ldapbasedn="dc=sl,dc=com"
> >> >
> >> > - I will be connecting as apuser.
> >> > - I will supply my own user's password.
> >> >
> >> > When PostgreSQL does the authentication I would like it to replace
> >> > apuser
> >> > with jkregloh.
> >> >
> >> > The reason why I want to do this is to limit power granted to a user.
> >> > For
> >> > example I want to be able to user my regular user jkregloh for
> everyday
> >> > things. But when I need super user actions I will login using apuser.
> >> > Now
> >> > this is easy enough to do without LDAP. But if I disable my user via
> >> > LDAP it
> >> > would remove access from both my regular user and my superuser, that's
> >> > the
> >> > functionality I am looking for.
> >> >
> >> > I am pretty sure this is not possible, but I am floating the question
> >> > anyways in hope of suggestions.
> >>
> >> I've wanted this as well, and for the same reason. I think you are
> >> correct, that this is not currently possible. Only authentication
> >> methods which inherently provide the authenticating user's username
> >> implement the pg_ident.conf mechanism. LDAP does not independently
> >> provide a username, it only uses the one provided to it.
> >>
> >> I thought a quick and dirty solution would be stuff both user names
> >> (the authenticating username and the database username) into the
> >> existing username slot of the libpq protocol, separated by some
> >> obscure character. Then break them apart on that character, and look
> >> in pg_ident.conf to make sure the specified authenticating user is
> >> allowed to connect as the specified database user. I've never gotten
> >> around to implementing it, though, and I doubt it would be accepted
> >> into core with the "magic character" design.
> >>
> >> Cheers,
> >>
> >> Jeff
> >>
> >
> > Perhaps what is necessary is something akin to the UNIX "sudo" facility.
> > That is, an SQL statement prefix which, if used, runs the given SQL
> > statement as a PG superuser. You then GRANT(?) authority to that facility
> > like you would to a table or database or ... . E.g. GRANT SUDO TO
> SOMEBODY;
> > who could then do SUDO some other SQL statement; and that SQL statement
> > would be done as if the PG user was a superuser.
>
> You can do something like:
>
> create user jkregloh login noinherit;
> grant apuser to jkregloh;
>
>
> Now once he logs in as jkrogloh he can promote himself to apuser by
> using "set role apuser". So it takes an intentional action to grant
> yourself extra powers, so should be effective at avoiding mistakes.
> It is not quite as emphatic as having to do an entirely separate
> login, however. Also, if you want the user to inherit from some roles
> and not from others, I think you are out of luck with this approach.
> Finally if you have customized user settings by "alter role apuser set
> ..." those will not get processed when you do a "set role apuser".
>
>
Thanks, this makes sense. It's kind of like the sudo approach mentioned
earlier. They would also need to take an action to return back to their
original role.

-Joseph

> Cheers,
>
> Jeff
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>