Proposal: Save user's original authenticated identity for logging

Greetings,

* Jacob Champion (pchampion@vmware.com) wrote:

First, the context: recently I've been digging into the use of third-
party authentication systems with Postgres. One sticking point is the
need to have a Postgres role corresponding to the third-party user
identity, which becomes less manageable at scale. I've been trying to
come up with ways to make that less painful, and to start peeling off
smaller feature requests.

Yeah, it'd be nice to improve things in this area.

= Problem =

For auth methods that allow pg_ident mapping, there's a way around the
one-role-per-user problem, which is to have all users that match some
pattern map to a single role. For Kerberos, you might specify that all
user principals under @EXAMPLE.COM are allowed to connect as some
generic user role, and that everyone matching */admin@EXAMPLE.COM is
additionally allowed to connect as an admin role.

Unfortunately, once you've been assigned a role, Postgres either makes
the original identity difficult to retrieve, or forgets who you were
entirely:

- for GSS, the original principal is saved in the Port struct, and you
need to either pull it out of pg_stat_gssapi, or enable log_connections
and piece the log line together with later log entries;

This has been improved on of late, but it's been done piece-meal.

- for LDAP, the bind DN is discarded entirely;

We don't support pg_ident.conf-style entries for LDAP, meaning that the
user provided has to match what we check, so I'm not sure what would be
improved with this change..? I'm also just generally not thrilled with
putting much effort into LDAP as it's a demonstrably insecure
authentication mechanism.

- for TLS client certs, the DN has to be pulled from pg_stat_ssl or the
sslinfo extension (and it's truncated to 64 characters, so good luck if
you have a particularly verbose PKI tree);

Yeah, it'd be nice to improve on this.

- for peer auth, the username of the peereid is discarded;

Would be good to improve this too.

= Proposal =

I propose that every auth method should store the string it uses to
identify a user -- what I'll call an "authenticated identity" -- into
one central location in Port, after authentication succeeds but before
any pg_ident authorization occurs. This field can then be exposed in
log_line_prefix. (It could additionally be exposed through a catalog
table or SQL function, if that were deemed useful.) This would let a
DBA more easily audit user activity when using more complicated
pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

Would this be generally useful for those of you using pg_ident in
production? Have I missed something that already provides this
functionality?

For some auth methods, eg: GSS, we've recently added information into
the authentication method which logs what the authenticated identity
was. The advantage with that approach is that it avoids bloating the
log by only logging that information once upon connection rather than
on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Jacob Champion (pchampion@vmware.com) wrote:

I propose that every auth method should store the string it uses to
identify a user -- what I'll call an "authenticated identity" -- into
one central location in Port, after authentication succeeds but before
any pg_ident authorization occurs. This field can then be exposed in
log_line_prefix. (It could additionally be exposed through a catalog
table or SQL function, if that were deemed useful.) This would let a
DBA more easily audit user activity when using more complicated
pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

What happens if ALTER USER RENAME is done while the session is still
alive?

More generally, exposing this in log_line_prefix seems like an awfully
narrow-minded view of what people will want it for. I'd personally
think pg_stat_activity a better place to look, for example.

on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

Yeah, this seems like about the most expensive way that we could possibly
choose to make the info available.

regards, tom lane

On Fri, 2021-01-29 at 17:01 -0500, Stephen Frost wrote:

- for LDAP, the bind DN is discarded entirely;

We don't support pg_ident.conf-style entries for LDAP, meaning that the
user provided has to match what we check, so I'm not sure what would be
improved with this change..?

For simple binds, this gives you almost nothing. For bind+search,
logging the actual bind DN is still important, in my opinion, since the
mechanism for determining it is more opaque (and may change over time).

But as Tom noted -- for both cases, if the role name changes, this
mechanism can still help you audit who the user _actually_ bound as,
not who you think they should have bound as based on their current role
name.

(There's also the fact that I think pg_ident mapping for LDAP would be
just as useful as it is for GSS or certs. That's for a different
conversation.)

I'm also just generally not thrilled with
putting much effort into LDAP as it's a demonstrably insecure
authentication mechanism.

Because Postgres has to proxy the password? Or is there something else?

I propose that every auth method should store the string it uses to
identify a user -- what I'll call an "authenticated identity" -- into
one central location in Port, after authentication succeeds but before
any pg_ident authorization occurs. This field can then be exposed in
log_line_prefix. (It could additionally be exposed through a catalog
table or SQL function, if that were deemed useful.) This would let a
DBA more easily audit user activity when using more complicated
pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

Agreed in principle... Is the CSV format configurable? Forcing it into
CSV logs by default seems like it'd be a hard sell, especially for
people not using pg_ident.

For some auth methods, eg: GSS, we've recently added information into
the authentication method which logs what the authenticated identity
was. The advantage with that approach is that it avoids bloating the
log by only logging that information once upon connection rather than
on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

As long as the identity can be easily logged and reviewed by DBAs, I'm
happy.

--Jacob

On Fri, 2021-01-29 at 17:30 -0500, Tom Lane wrote:

What happens if ALTER USER RENAME is done while the session is still
alive?

IMO the authenticated identity should be write-once. Especially since
one of my goals is to have greater auditability into events as they've
actually happened. So ALTER USER RENAME should have no effect.

This also doesn't really affect third-party auth methods. If I'm bound
as pchampion@EXAMPLE.COM and a superuser changes my username to tlane,
you _definitely_ don't want to see my authenticated identity change to
tlane@EXAMPLE.COM. That's not who I am.

So the potential confusion would come into play with first-party authn.
From an audit perspective, I think it's worth it. I did authenticate as
pchampion, not tlane.

More generally, exposing this in log_line_prefix seems like an awfully
narrow-minded view of what people will want it for. I'd personally
think pg_stat_activity a better place to look, for example.
[...]
Yeah, this seems like about the most expensive way that we could possibly
choose to make the info available.

I'm happy as long as it's _somewhere_. :D It's relatively easy to
expose a single location through multiple avenues, but currently there
is no single location.

--Jacob

Jacob Champion <pchampion@vmware.com> writes:

On Fri, 2021-01-29 at 17:30 -0500, Tom Lane wrote:

What happens if ALTER USER RENAME is done while the session is still
alive?

IMO the authenticated identity should be write-once. Especially since
one of my goals is to have greater auditability into events as they've
actually happened. So ALTER USER RENAME should have no effect.

This also doesn't really affect third-party auth methods. If I'm bound
as pchampion@EXAMPLE.COM and a superuser changes my username to tlane,
you _definitely_ don't want to see my authenticated identity change to
tlane@EXAMPLE.COM. That's not who I am.

Ah. So basically, this comes into play when you consider that some
outside-the-database entity is your "real" authenticated identity.
That seems reasonable when using Kerberos or the like, though it's
not real meaningful for traditional password-type authentication.
I'd misunderstood your point before.

So, if we store this "real" identity, is there any security issue
involved in exposing it to other users (via pg_stat_activity or
whatever)?

I remain concerned about the cost and inconvenience of exposing
it via log_line_prefix, but at least that shouldn't be visible
to anyone who's not entitled to know who's logged in ...

regards, tom lane

On Fri, 2021-01-29 at 18:40 -0500, Tom Lane wrote:

Ah. So basically, this comes into play when you consider that some
outside-the-database entity is your "real" authenticated identity.
That seems reasonable when using Kerberos or the like, though it's
not real meaningful for traditional password-type authentication.

Right.

So, if we store this "real" identity, is there any security issue
involved in exposing it to other users (via pg_stat_activity or
whatever)?

I think that could be a concern for some, yeah. Besides being able to
get information on other logged-in users, the ability to connect an
authenticated identity to a username also gives you some insight into
the pg_hba configuration.

--Jacob

On Sat, Jan 30, 2021 at 12:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jacob Champion <pchampion@vmware.com> writes:

On Fri, 2021-01-29 at 17:30 -0500, Tom Lane wrote:

What happens if ALTER USER RENAME is done while the session is still
alive?

IMO the authenticated identity should be write-once. Especially since
one of my goals is to have greater auditability into events as they've
actually happened. So ALTER USER RENAME should have no effect.

This also doesn't really affect third-party auth methods. If I'm bound
as pchampion@EXAMPLE.COM and a superuser changes my username to tlane,
you _definitely_ don't want to see my authenticated identity change to
tlane@EXAMPLE.COM. That's not who I am.

Ah. So basically, this comes into play when you consider that some
outside-the-database entity is your "real" authenticated identity.
That seems reasonable when using Kerberos or the like, though it's
not real meaningful for traditional password-type authentication.

I think the usecases where it's relevant is a relatively close match
to the usecases where we support user mapping in pg_ident.conf. There
is a small exception in the ldap search+bind since it's a two-step
operation and the interesting part would be in the mid-step, but I'm
not sure there is any other case than those where it adds a lot of
value.

I'd misunderstood your point before.

So, if we store this "real" identity, is there any security issue
involved in exposing it to other users (via pg_stat_activity or
whatever)?

I'd say it might. It might for example reveal where in a hierarchical
authentication setup your "real identity" lives. I think it'd at least
have to be limited to superusers.

I remain concerned about the cost and inconvenience of exposing
it via log_line_prefix, but at least that shouldn't be visible
to anyone who's not entitled to know who's logged in ...

What if we logged it as part of log_connection=on, but only there and
only once? It could still be traced through the rest of that sessions
logging using the fields identifying the session, and we'd only end up
logging it once.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

On Sat, Jan 30, 2021 at 12:21 AM Jacob Champion <pchampion@vmware.com> wrote:

On Fri, 2021-01-29 at 17:01 -0500, Stephen Frost wrote:

- for LDAP, the bind DN is discarded entirely;

We don't support pg_ident.conf-style entries for LDAP, meaning that the
user provided has to match what we check, so I'm not sure what would be
improved with this change..?

For simple binds, this gives you almost nothing. For bind+search,
logging the actual bind DN is still important, in my opinion, since the
mechanism for determining it is more opaque (and may change over time).

Yeah, that's definitely a piece of information that can be hard to get at today.

(There's also the fact that I think pg_ident mapping for LDAP would be
just as useful as it is for GSS or certs. That's for a different
conversation.)

Specifically for search+bind, I would assume?

I'm also just generally not thrilled with
putting much effort into LDAP as it's a demonstrably insecure
authentication mechanism.

Because Postgres has to proxy the password? Or is there something else?

Stephen is on a bit of a crusade against ldap :) Mostly for good
reasons of course. A large amount of those who choose ldap also have a
kerberos system (because, say, active directory) and the pick ldap
only because they think it's good, not because it is...

But yes, I think the enforced cleartext password proxying is at the
core of the problem. LDAP also encourages the idea of centralized
password-reuse, which is not exactly a great thing for security.

That said, I don't think either of those are reasons not to improve on
LDAP. It can certainly be a reason for somebody not to want to spend
their own time on it, but there's no reason it should prevent
improvements.

I propose that every auth method should store the string it uses to
identify a user -- what I'll call an "authenticated identity" -- into
one central location in Port, after authentication succeeds but before
any pg_ident authorization occurs. This field can then be exposed in
log_line_prefix. (It could additionally be exposed through a catalog
table or SQL function, if that were deemed useful.) This would let a
DBA more easily audit user activity when using more complicated
pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

Agreed in principle... Is the CSV format configurable? Forcing it into
CSV logs by default seems like it'd be a hard sell, especially for
people not using pg_ident.

For CVS, all columns are always included, and that's a feature -- it
makes it predictable.

To make it optional it would have to be a configuration parameter that
turns the field into an empty one. but it should still be there.

For some auth methods, eg: GSS, we've recently added information into
the authentication method which logs what the authenticated identity
was. The advantage with that approach is that it avoids bloating the
log by only logging that information once upon connection rather than
on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

As long as the identity can be easily logged and reviewed by DBAs, I'm
happy.

Yeah, per my previous mail, I think this is a better way - make it
part of log_connections. But it would be good to find a way that we
can log it the same way for all of them -- rather than slightly
different ways depending on authentication method.

With that I think it would also be useful to have it available in the
system as well -- either as a column in pg_stat_activity or maybe just
as a function like pg_get_authenticated_identity() since it might be
something that's interesting to a smallish subset of users (but very
interesting to those).

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

On Fri, 29 Jan 2021 at 18:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ah. So basically, this comes into play when you consider that some
outside-the-database entity is your "real" authenticated identity.
That seems reasonable when using Kerberos or the like, though it's
not real meaningful for traditional password-type authentication.
I'd misunderstood your point before.

I wonder if there isn't room to handle this the other way around. To
configure Postgres to not need a CREATE ROLE for every role but
delegate the user management to the external authentication service.

So Postgres would consider the actual role to be the one kerberos said
it was even if that role didn't exist in pg_role. Presumably you would
want to delegate to a corresponding authorization system as well so if
the role was absent from pg_role (or more likely fit some pattern)
Postgres would ignore pg_role and consult the authorization system
configured like AD or whatever people use with Kerberos these days.

--
greg

Magnus Hagander <magnus@hagander.net> writes:

On Sat, Jan 30, 2021 at 12:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I remain concerned about the cost and inconvenience of exposing
it via log_line_prefix, but at least that shouldn't be visible
to anyone who's not entitled to know who's logged in ...

What if we logged it as part of log_connection=on, but only there and
only once? It could still be traced through the rest of that sessions
logging using the fields identifying the session, and we'd only end up
logging it once.

I'm certainly fine with including this info in the log_connection output.
Perhaps it'd also be good to have a superuser-only column in
pg_stat_activity, or some other restricted way to get the info from an
existing session. I doubt we really want a log_line_prefix option.

regards, tom lane

Greg Stark <stark@mit.edu> writes:

I wonder if there isn't room to handle this the other way around. To
configure Postgres to not need a CREATE ROLE for every role but
delegate the user management to the external authentication service.

So Postgres would consider the actual role to be the one kerberos said
it was even if that role didn't exist in pg_role. Presumably you would
want to delegate to a corresponding authorization system as well so if
the role was absent from pg_role (or more likely fit some pattern)
Postgres would ignore pg_role and consult the authorization system
configured like AD or whatever people use with Kerberos these days.

This doesn't sound particularly workable: how would you manage
inside-the-database permissions? Kerberos isn't going to know
what "view foo" is, let alone know whether you should be allowed
to read or write it. So ISTM there has to be a role to hold
those permissions. Certainly, you could allow multiple external
identities to share a role ... but that works today.

regards, tom lane

Greetings,

* Magnus Hagander (magnus@hagander.net) wrote:

On Sat, Jan 30, 2021 at 12:21 AM Jacob Champion <pchampion@vmware.com> wrote:

I'm also just generally not thrilled with
putting much effort into LDAP as it's a demonstrably insecure
authentication mechanism.

Because Postgres has to proxy the password? Or is there something else?

Yes.

Stephen is on a bit of a crusade against ldap :) Mostly for good
reasons of course. A large amount of those who choose ldap also have a
kerberos system (because, say, active directory) and the pick ldap
only because they think it's good, not because it is...

This is certainly one area of frustration, but even if Kerberos isn't
available, it doesn't make it a good idea to use LDAP.

But yes, I think the enforced cleartext password proxying is at the
core of the problem. LDAP also encourages the idea of centralized
password-reuse, which is not exactly a great thing for security.

Right- passing around a user's password in the clear (or even through an
encrypted tunnel) has been strongly discouraged for a very long time,
for very good reason. LDAP does double-down on that by being a
centralized password, meaning that someone's entire identity (for all
the services that share that LDAP system, at least) are compromised if
any one system in the environment is.

Ideally, we'd have a 'PasswordAuthentication' option which would
disallow cleartext passwords, as has been discussed elsewhere, which
would make things like ldap and pam auth methods disallowed.

That said, I don't think either of those are reasons not to improve on
LDAP. It can certainly be a reason for somebody not to want to spend
their own time on it, but there's no reason it should prevent
improvements.

I realize that this isn't a popular opinion, but I'd much rather we
actively move in the direction of deprecating auth methods which use
cleartext passwords. The one auth method we have that works that way
and isn't terrible is radius, though it also isn't great since the pin
doesn't change and would be compromised, not to mention that it likely
depends on the specific system as to if an attacker might be able to use
the exact same code provided to log into other systems if done fast
enough.

I propose that every auth method should store the string it uses to
identify a user -- what I'll call an "authenticated identity" -- into
one central location in Port, after authentication succeeds but before
any pg_ident authorization occurs. This field can then be exposed in
log_line_prefix. (It could additionally be exposed through a catalog
table or SQL function, if that were deemed useful.) This would let a
DBA more easily audit user activity when using more complicated
pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

Agreed in principle... Is the CSV format configurable? Forcing it into
CSV logs by default seems like it'd be a hard sell, especially for
people not using pg_ident.

For CVS, all columns are always included, and that's a feature -- it
makes it predictable.

To make it optional it would have to be a configuration parameter that
turns the field into an empty one. but it should still be there.

Yeah, we've been around this before and, as I recall anyway, there was
actually a prior patch proposed to add this information to the CSV log.
There is the question about if it's valuable enough to repeat on every
line or not. These days, I think I lean in the same direction as the
majority on this thread that it's sufficient to log as part of the
connection authorized message.

For some auth methods, eg: GSS, we've recently added information into
the authentication method which logs what the authenticated identity
was. The advantage with that approach is that it avoids bloating the
log by only logging that information once upon connection rather than
on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

As long as the identity can be easily logged and reviewed by DBAs, I'm
happy.

Yeah, per my previous mail, I think this is a better way - make it
part of log_connections. But it would be good to find a way that we
can log it the same way for all of them -- rather than slightly
different ways depending on authentication method.

+1.

With that I think it would also be useful to have it available in the
system as well -- either as a column in pg_stat_activity or maybe just
as a function like pg_get_authenticated_identity() since it might be
something that's interesting to a smallish subset of users (but very
interesting to those).

We've been trending in the direction of having separate functions/views
for the different types of auth, as the specific information you'd want
varies (SSL has a different set than GSS, for example). Maybe it makes
sense to have the one string that's used to match against in pg_ident
included in pg_stat_activity also but I'm not completely sure- after
all, there's a reason we have the separate views. Also, if we do add
it, I would think we'd have it under the same check as the other
sensitive pg_stat_activity fields and not be superuser-only.

Thanks,

Stephen

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Greg Stark <stark@mit.edu> writes:

I wonder if there isn't room to handle this the other way around. To
configure Postgres to not need a CREATE ROLE for every role but
delegate the user management to the external authentication service.

So Postgres would consider the actual role to be the one kerberos said
it was even if that role didn't exist in pg_role. Presumably you would
want to delegate to a corresponding authorization system as well so if
the role was absent from pg_role (or more likely fit some pattern)
Postgres would ignore pg_role and consult the authorization system
configured like AD or whatever people use with Kerberos these days.

This doesn't sound particularly workable: how would you manage
inside-the-database permissions? Kerberos isn't going to know
what "view foo" is, let alone know whether you should be allowed
to read or write it. So ISTM there has to be a role to hold
those permissions. Certainly, you could allow multiple external
identities to share a role ... but that works today.

Agreed- we would need something in the database to tie it to and I don't
see it making much sense to try to invent something else for that when
that's what roles are. What's been discussed before and would certainly
be nice, however, would be a way to have roles automatically created.
There's pg_ldap_sync for that today but it'd be nice to have something
built-in and which happens at connection/authentication time, or maybe a
background worker that connects to an ldap server and listens for
changes and creates appropriate roles when they're created. Considering
we've got the LDAP code already, that'd be a really nice capability.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

This doesn't sound particularly workable: how would you manage
inside-the-database permissions? Kerberos isn't going to know
what "view foo" is, let alone know whether you should be allowed
to read or write it. So ISTM there has to be a role to hold
those permissions. Certainly, you could allow multiple external
identities to share a role ... but that works today.

Agreed- we would need something in the database to tie it to and I don't
see it making much sense to try to invent something else for that when
that's what roles are. What's been discussed before and would certainly
be nice, however, would be a way to have roles automatically created.
There's pg_ldap_sync for that today but it'd be nice to have something
built-in and which happens at connection/authentication time, or maybe a
background worker that connects to an ldap server and listens for
changes and creates appropriate roles when they're created. Considering
we've got the LDAP code already, that'd be a really nice capability.

That's still got the same issue though: where does the role get any
permissions from?

I suppose you could say "allow auto-creation of new roles and make them
members of group X", where X holds the permissions that "everybody"
should have. But I'm not sure how much that buys compared to just
letting everyone log in as X.

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

This doesn't sound particularly workable: how would you manage
inside-the-database permissions? Kerberos isn't going to know
what "view foo" is, let alone know whether you should be allowed
to read or write it. So ISTM there has to be a role to hold
those permissions. Certainly, you could allow multiple external
identities to share a role ... but that works today.

Agreed- we would need something in the database to tie it to and I don't
see it making much sense to try to invent something else for that when
that's what roles are. What's been discussed before and would certainly
be nice, however, would be a way to have roles automatically created.
There's pg_ldap_sync for that today but it'd be nice to have something
built-in and which happens at connection/authentication time, or maybe a
background worker that connects to an ldap server and listens for
changes and creates appropriate roles when they're created. Considering
we've got the LDAP code already, that'd be a really nice capability.

That's still got the same issue though: where does the role get any
permissions from?

I suppose you could say "allow auto-creation of new roles and make them
members of group X", where X holds the permissions that "everybody"
should have. But I'm not sure how much that buys compared to just
letting everyone log in as X.

Right, pg_ldap_sync already supports making new roles a member of
another role in PG such as a group role, we'd want to do something
similar. Also- once the role exists, then permissions could be assigned
directly as well, of course, which would be the advantage of a
background worker that's keeping the set of roles in sync, as the role
would be created at nearly the same time in both the authentication
system itself (eg: AD) and in PG. That kind of integration exists in
other products and would go a long way to making PG easier to use and
administer.

Thanks,

Stephen

On Mon, Feb 1, 2021 at 6:32 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

This doesn't sound particularly workable: how would you manage
inside-the-database permissions? Kerberos isn't going to know
what "view foo" is, let alone know whether you should be allowed
to read or write it. So ISTM there has to be a role to hold
those permissions. Certainly, you could allow multiple external
identities to share a role ... but that works today.

Agreed- we would need something in the database to tie it to and I don't
see it making much sense to try to invent something else for that when
that's what roles are. What's been discussed before and would certainly
be nice, however, would be a way to have roles automatically created.
There's pg_ldap_sync for that today but it'd be nice to have something
built-in and which happens at connection/authentication time, or maybe a
background worker that connects to an ldap server and listens for
changes and creates appropriate roles when they're created. Considering
we've got the LDAP code already, that'd be a really nice capability.

That's still got the same issue though: where does the role get any
permissions from?

I suppose you could say "allow auto-creation of new roles and make them
members of group X", where X holds the permissions that "everybody"
should have. But I'm not sure how much that buys compared to just
letting everyone log in as X.

What people would *really* want I think is "alow auto-creation of new
roles, and then look up which other roles they should be members of
using ldap" (or "using this script over here" for a more flexible
approach). Which is of course a whole different thing to do in the
process of authentication.

The main thing you'd gain by auto-creating users rather than just
letting them log in is the ability to know exactly which user did
something, and view who it really is through pg_stat_activity. Adding
the "original auth id" as a field or available method would provide
that information in the mapped user case -- making the difference even
smaller. It's really the auto-membership that's the killer feature of
that one, I think.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

On Sun, 2021-01-31 at 12:27 +0100, Magnus Hagander wrote:

(There's also the fact that I think pg_ident mapping for LDAP would be
just as useful as it is for GSS or certs. That's for a different
conversation.)

Specifically for search+bind, I would assume?

Even for the simple bind case, I think it'd be useful to be able to
perform a pg_ident mapping of

ldapmap /.* ldapuser

so that anyone who is able to authenticate against the LDAP server is
allowed to assume the ldapuser role. (For this to work, you'd need to
be able to specify your LDAP username as a connection option, similar
to how you can specify a client certificate, so that you could set
PGUSER=ldapuser.)

But again, that's orthogonal to the current discussion.

With that I think it would also be useful to have it available in the
system as well -- either as a column in pg_stat_activity or maybe just
as a function like pg_get_authenticated_identity() since it might be
something that's interesting to a smallish subset of users (but very
interesting to those).

Agreed, it would slot in nicely with the other per-backend stats functions.
--Jacob

On Mon, 2021-02-01 at 11:49 -0500, Stephen Frost wrote:

* Magnus Hagander (magnus@hagander.net) wrote:

But yes, I think the enforced cleartext password proxying is at the
core of the problem. LDAP also encourages the idea of centralized
password-reuse, which is not exactly a great thing for security.

Right- passing around a user's password in the clear (or even through an
encrypted tunnel) has been strongly discouraged for a very long time,
for very good reason. LDAP does double-down on that by being a
centralized password, meaning that someone's entire identity (for all
the services that share that LDAP system, at least) are compromised if
any one system in the environment is.

Sure. I don't disagree with anything you've said in that paragraph, but
as someone who's implementing solutions for other people who are
actually deploying, I don't have a lot of control over whether a
customer's IT department wants to use LDAP or not. And I'm not holding
my breath for LDAP servers to start implementing federated identity,
though that would be nice.

Also, if we do add
it, I would think we'd have it under the same check as the other
sensitive pg_stat_activity fields and not be superuser-only.

Just the standard HAS_PGSTAT_PERMISSIONS(), then?

To double-check -- since giving this ability to the pg_read_all_stats
role would expand its scope -- could that be dangerous for anyone?

--Jacob

Greetings,

* Jacob Champion (pchampion@vmware.com) wrote:

On Mon, 2021-02-01 at 11:49 -0500, Stephen Frost wrote:

* Magnus Hagander (magnus@hagander.net) wrote:

But yes, I think the enforced cleartext password proxying is at the
core of the problem. LDAP also encourages the idea of centralized
password-reuse, which is not exactly a great thing for security.

Right- passing around a user's password in the clear (or even through an
encrypted tunnel) has been strongly discouraged for a very long time,
for very good reason. LDAP does double-down on that by being a
centralized password, meaning that someone's entire identity (for all
the services that share that LDAP system, at least) are compromised if
any one system in the environment is.

Sure. I don't disagree with anything you've said in that paragraph, but
as someone who's implementing solutions for other people who are
actually deploying, I don't have a lot of control over whether a
customer's IT department wants to use LDAP or not. And I'm not holding
my breath for LDAP servers to start implementing federated identity,
though that would be nice.

Not sure exactly what you're referring to here but AD already provides
Kerberos with cross-domain trusts (aka forests). The future is here..?
:)

Also, if we do add
it, I would think we'd have it under the same check as the other
sensitive pg_stat_activity fields and not be superuser-only.

Just the standard HAS_PGSTAT_PERMISSIONS(), then?

To double-check -- since giving this ability to the pg_read_all_stats
role would expand its scope -- could that be dangerous for anyone?

I don't agree that this really expands its scope- in fact, you'll see
that the GSSAPI and SSL user authentication information is already
allowed under HAS_PGSTAT_PERMISSIONS().

Thanks,

Stephen