Hi again.
About the privileges, our support can create roles / databases, drop
existing databases, dump /restore, change other users passwords. It's not
feasible right now create a 1:1 map of system users and postgres users.
Maybe in the future.
I wrote 2 possible patches, both issuing a detail message only if
log_connections is enabled.
The first one using the Stephen Frost suggestion, inside the Port struct (I
guess that this is the one, I coudn't find the Peer struct)
The second one following the same approach of cf commit 5e0b5dcab, as
pointed by Tom Lane.
Again, feel free to comment and criticize.
On Sun, Jan 17, 2016 at 3:07 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Tom,
>
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > Stephen Frost <sfr...@snowman.net> writes:
> > > What I think we really want here is logging of the general 'system
> > > user' for all auth methods instead of only for the 'peer' method.
> >
> > Well, we don't really know that except in a small subset of auth
> > methods. I agree that when we do know it, it's useful info to log.
>
> Right.
>
> > My big beef with the proposed patch is that the log message is emitted
> > unconditionally. There are lots and lots of users who feel that during
> > normal operation, *zero* log messages should get emitted. Those
> villagers
> > would be on our doorsteps with pitchforks if we shipped this patch as-is.
>
> Agreed.
>
> > I would propose that this information should be emitted only when
> > log_connections is enabled, and indeed that it should be part of the
> > log_connections message not a separate message. So this leads to
> > thinking that somehow, the code for individual auth methods should
> > be able to return an "additional info" field for inclusion in
> > log_connections. We already have such a concept for auth failures,
> > cf commit 5e0b5dcab.
>
> Apologies if it wasn't clear, but that's exactly what I was suggesting
> by saying to add it to PerformAuthentication, which is where we emit
> the connection info when log_connections is enabled.
>
> > > ... and also make it available in pg_stat_activity.
> >
> > That's moving the goalposts quite a bit, and I'm not sure it's necessary
> > or even desirable. Let's just get this added to log_connections output,
> > and then see if there's field demand for more.
>
> This was in context of peer_cn, which is just a specific "system user"
> value and which we're already showing in pg_stat_* info tables. I'd
> love to have the Kerberos principal available, but I don't think it'd
> make sense to have a 'pg_stat_kerberos' just for that.
>
> I agree that it's moving the goalposts for this patch and could be an
> independent patch, but I don't see it as any different, from a
> desirability and requirements perspective, than what we're doing for SSL
> connections.
>
> Thanks!
>
> Stephen
>
--
José Arthur Benetasso Villanova
commit 76594784c50bca1b09f687e58f17ff27230076be
Author: Jose Arthur Benetasso Villanova <jose.art...@locaweb.com.br>
Date: Tue Jan 19 11:50:22 2016 -0200
Log message
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 57c2f48..ac1c785 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -991,6 +991,7 @@ pg_GSS_recvauth(Port *port)
return STATUS_ERROR;
}
+ port->system_user = pstrdup(gbuf.value);
ret = check_usermap(port->hba->usermap, port->user_name, gbuf.value,
pg_krb_caseins_users);
@@ -1291,6 +1292,7 @@ pg_SSPI_recvauth(Port *port)
int retval;
namebuf = psprintf("%s@%s", accountname, domainname);
+ port->system_user = pstrdup(namebuf);
retval = check_usermap(port->hba->usermap, port->user_name, namebuf, true);
pfree(namebuf);
return retval;
@@ -1561,8 +1563,11 @@ ident_inet_done:
pg_freeaddrinfo_all(local_addr.addr.ss_family, la);
if (ident_return)
+ {
/* Success! Check the usermap */
+ port->system_user = pstrdup(ident_user);
return check_usermap(port->hba->usermap, port->user_name, ident_user, false);
+ }
return STATUS_ERROR;
}
@@ -1609,6 +1614,8 @@ auth_peer(hbaPort *port)
}
strlcpy(ident_user, pw->pw_name, IDENT_USERNAME_MAX + 1);
+ port->system_user = pstrdup(ident_user);
+
return check_usermap(port->hba->usermap, port->user_name, ident_user, false);
}
@@ -2124,6 +2131,7 @@ CheckLDAPAuth(Port *port)
return STATUS_ERROR;
}
+ port->system_user = pstrdup(fulluser);
pfree(fulluser);
return STATUS_OK;
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index e22d4db..f425808 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -255,7 +255,8 @@ PerformAuthentication(Port *port)
#endif
ereport(LOG,
(errmsg("replication connection authorized: user=%s",
- port->user_name)));
+ port->user_name),
+ port->system_user ? errdetail_log("system_user=%s", port->system_user) : 0));
}
else
{
@@ -269,7 +270,8 @@ PerformAuthentication(Port *port)
#endif
ereport(LOG,
(errmsg("connection authorized: user=%s database=%s",
- port->user_name, port->database_name)));
+ port->user_name, port->database_name),
+ port->system_user ? errdetail_log("system_user=%s", port->system_user) : 0));
}
}
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 5d07b78..448ac36 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -129,6 +129,7 @@ typedef struct Port
int remote_hostname_errcode; /* see above */
char *remote_port; /* text rep of remote port */
CAC_state canAcceptConnections; /* postmaster connection status */
+ char *system_user; /* remote user name if available */
/*
* Information that needs to be saved from the startup packet and passed
commit 6d75f67bee0ecae46ccb382c70eddbc2eb8c1d03
Author: Jose Arthur Benetasso Villanova <jose.art...@locaweb.com.br>
Date: Wed Jan 27 14:29:18 2016 -0200
Using system_user variable
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 57c2f48..b3cd647 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -55,7 +55,7 @@ static int recv_and_check_password_packet(Port *port, char **logdetail);
static int ident_inet(hbaPort *port);
#ifdef HAVE_UNIX_SOCKETS
-static int auth_peer(hbaPort *port);
+static int auth_peer(hbaPort *port, char **system_user);
#endif
@@ -142,7 +142,7 @@ bool pg_krb_caseins_users;
#include <gssapi/gssapi.h>
#endif
-static int pg_GSS_recvauth(Port *port);
+static int pg_GSS_recvauth(Port *port, char **system_user);
#endif /* ENABLE_GSS */
@@ -154,7 +154,7 @@ static int pg_GSS_recvauth(Port *port);
typedef SECURITY_STATUS
(WINAPI * QUERY_SECURITY_CONTEXT_TOKEN_FN) (
PCtxtHandle, void **);
-static int pg_SSPI_recvauth(Port *port);
+static int pg_SSPI_recvauth(Port *port, char **system_user);
#endif
/*----------------------------------------------------------------
@@ -293,7 +293,7 @@ auth_failed(Port *port, int status, char *logdetail)
* function does not return and the backend process is terminated.
*/
void
-ClientAuthentication(Port *port)
+ClientAuthentication(Port *port, char **system_user)
{
int status = STATUS_ERROR;
char *logdetail = NULL;
@@ -480,7 +480,7 @@ ClientAuthentication(Port *port)
case uaGSS:
#ifdef ENABLE_GSS
sendAuthRequest(port, AUTH_REQ_GSS);
- status = pg_GSS_recvauth(port);
+ status = pg_GSS_recvauth(port, system_user);
#else
Assert(false);
#endif
@@ -489,7 +489,7 @@ ClientAuthentication(Port *port)
case uaSSPI:
#ifdef ENABLE_SSPI
sendAuthRequest(port, AUTH_REQ_SSPI);
- status = pg_SSPI_recvauth(port);
+ status = pg_SSPI_recvauth(port, system_user);
#else
Assert(false);
#endif
@@ -497,7 +497,7 @@ ClientAuthentication(Port *port)
case uaPeer:
#ifdef HAVE_UNIX_SOCKETS
- status = auth_peer(port);
+ status = auth_peer(port, system_user);
#else
Assert(false);
#endif
@@ -773,7 +773,7 @@ pg_GSS_error(int severity, char *errmsg, OM_uint32 maj_stat, OM_uint32 min_stat)
}
static int
-pg_GSS_recvauth(Port *port)
+pg_GSS_recvauth(Port *port, char **system_user)
{
OM_uint32 maj_stat,
min_stat,
@@ -990,7 +990,7 @@ pg_GSS_recvauth(Port *port)
gss_release_buffer(&lmin_s, &gbuf);
return STATUS_ERROR;
}
-
+ *system_user = psprintf(_("GSS user \"%s\""), gbuf.value);
ret = check_usermap(port->hba->usermap, port->user_name, gbuf.value,
pg_krb_caseins_users);
@@ -1023,7 +1023,7 @@ pg_SSPI_error(int severity, const char *errmsg, SECURITY_STATUS r)
}
static int
-pg_SSPI_recvauth(Port *port)
+pg_SSPI_recvauth(Port *port, char **system_user)
{
int mtype;
StringInfoData buf;
@@ -1291,12 +1291,16 @@ pg_SSPI_recvauth(Port *port)
int retval;
namebuf = psprintf("%s@%s", accountname, domainname);
+ *system_user = psprintf(_("SSPI user \"%s\""), namebuf);
retval = check_usermap(port->hba->usermap, port->user_name, namebuf, true);
pfree(namebuf);
return retval;
}
else
+ {
+ *system_user = psprintf(_("SSPI user \"%s\""), accountname);
return check_usermap(port->hba->usermap, port->user_name, accountname, true);
+ }
}
#endif /* ENABLE_SSPI */
@@ -1576,7 +1580,7 @@ ident_inet_done:
#ifdef HAVE_UNIX_SOCKETS
static int
-auth_peer(hbaPort *port)
+auth_peer(hbaPort *port, char **system_user)
{
char ident_user[IDENT_USERNAME_MAX + 1];
uid_t uid;
@@ -1609,6 +1613,7 @@ auth_peer(hbaPort *port)
}
strlcpy(ident_user, pw->pw_name, IDENT_USERNAME_MAX + 1);
+ *system_user = psprintf(_("System user \"%s\""), ident_user);
return check_usermap(port->hba->usermap, port->user_name, ident_user, false);
}
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index e22d4db..9bc2754 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -181,6 +181,7 @@ GetDatabaseTupleByOid(Oid dboid)
static void
PerformAuthentication(Port *port)
{
+ char *system_user = NULL; /* Get system user details if available */
/* This should be set already, but let's make sure */
ClientAuthInProgress = true; /* limit visibility of log messages */
@@ -234,7 +235,7 @@ PerformAuthentication(Port *port)
/*
* Now perform authentication exchange.
*/
- ClientAuthentication(port); /* might not return, if failure */
+ ClientAuthentication(port, &system_user); /* might not return, if failure */
/*
* Done with authentication. Disable the timeout, and log if needed.
@@ -250,7 +251,8 @@ PerformAuthentication(Port *port)
ereport(LOG,
(errmsg("replication connection authorized: user=%s SSL enabled (protocol=%s, cipher=%s, compression=%s)",
port->user_name, SSL_get_version(port->ssl), SSL_get_cipher(port->ssl),
- SSL_get_current_compression(port->ssl) ? _("on") : _("off"))));
+ SSL_get_current_compression(port->ssl) ? _("on") : _("off")),
+ system_user ? errdetail_log("%s", system_user): 0));
else
#endif
ereport(LOG,
@@ -269,10 +271,16 @@ PerformAuthentication(Port *port)
#endif
ereport(LOG,
(errmsg("connection authorized: user=%s database=%s",
- port->user_name, port->database_name)));
+ port->user_name, port->database_name),
+ system_user ? errdetail_log("%s", system_user): 0));
}
}
+ if (system_user)
+ {
+ pfree(system_user);
+ }
+
set_ps_display("startup", false);
ClientAuthInProgress = false; /* client_min_messages is active now */
diff --git a/src/include/libpq/auth.h b/src/include/libpq/auth.h
index 3cd06b7..ec5e308 100644
--- a/src/include/libpq/auth.h
+++ b/src/include/libpq/auth.h
@@ -20,7 +20,7 @@ extern char *pg_krb_server_keyfile;
extern bool pg_krb_caseins_users;
extern char *pg_krb_realm;
-extern void ClientAuthentication(Port *port);
+extern void ClientAuthentication(Port *port, char **system_user);
/* Hook for plugins to get control in ClientAuthentication() */
typedef void (*ClientAuthentication_hook_type) (Port *, int);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
