Обсуждение: Force pg_hba.conf user with LDAP

On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh <jkregloh@sproutloud.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

John McKown <john.archie.mckown@gmail.com> writes:
>​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 already achieve effects of that sort with a security definer
function, eg

begin;

create function sudo(text) returns void as
$$begin execute $1; end$$ language plpgsql security definer;

revoke all on function sudo(text) from public;
grant execute on function sudo(text) to trusted_users;

commit;

(You have to work a bit harder if you want to be able to return query
results, but it's doable.)

The main benefit of approaching things this way is it doesn't have to
be all-or-nothing: the gating function can apply checks on what it
will allow.

            regards, tom lane

On Mon, Aug 1, 2016 at 1:32 PM, John McKown
<john.archie.mckown@gmail.com> wrote:
> On Mon, Aug 1, 2016 at 2:49 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>> On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh <jkregloh@sproutloud.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".

Cheers,

Jeff