[GENERAL] Force pg_hba.conf user with LDAP

[Joseph Kregloh]

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.

-Joseph

[Jeff Janes]

Post by Joseph Kregloh
Hi,
Is there a way to force the user being sent to LDAP?
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]

Post by Joseph Kregloh

Post by Joseph Kregloh
Hi,
Is there a way to force the user being sent to LDAP?
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

Post by Joseph Kregloh
would remove access from both my regular user and my superuser, that's

the

Post by Joseph Kregloh
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.

[Tom Lane]

Post by John McKown
​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

[Jeff Janes]

On Mon, Aug 1, 2016 at 1:32 PM, John McKown

Post by John McKown

Post by Jeff Janes

Post by Joseph Kregloh
Hi,
Is there a way to force the user being sent to LDAP?
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

[Joseph Kregloh]

Post by Jeff Janes
On Mon, Aug 1, 2016 at 1:32 PM, John McKown

Post by John McKown

On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh <

Post by Joseph Kregloh
Hi,
Is there a way to force the user being sent to LDAP?
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

Post by John McKown

Post by Joseph Kregloh
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;

Post by John McKown
who could then do SUDO some other SQL statement; and that SQL statement
would be done as if the PG user was a superuser.

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

Post by Jeff Janes
Cheers,
Jeff
--
http://www.postgresql.org/mailpref/pgsql-general