Thread: More detailed auth info
I came across a case this week where I wanted to be able to determine more detailed auth information on already logged in sessions - not from the client, but from the server. In this specific case, I wanted to examine the "is ssl" flag on the connection. But I can see other things being interesting, such as which user is on the other end (when pg_ident is in use), more detailed SSL information, full kerberos principal when kerberos in use etc. I doubt this is common enough to want to stick it in pg_stat_activity though, but what do people think? And if not there, as a separate view or just as a function to call (e.g. pg_get_detailed_authinfo(<backendpid>)) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> I came across a case this week where I wanted to be able to determine
> more detailed auth information on already logged in sessions - not
> from the client, but from the server. In this specific case, I wanted
> to examine the "is ssl" flag on the connection. But I can see other
> things being interesting, such as which user is on the other end (when
> pg_ident is in use), more detailed SSL information, full kerberos
> principal when kerberos in use etc.
> I doubt this is common enough to want to stick it in pg_stat_activity
> though, but what do people think? And if not there, as a separate view
> or just as a function to call (e.g.
> pg_get_detailed_authinfo(<backendpid>))
By and large, it's been thought to be a possible security hole to expose
such information, except possibly in the postmaster log. I'm certainly
*not* in favor of creating a view for it.
regards, tom lane
On Fri, Jan 21, 2011 at 15:51, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> I came across a case this week where I wanted to be able to determine >> more detailed auth information on already logged in sessions - not >> from the client, but from the server. In this specific case, I wanted >> to examine the "is ssl" flag on the connection. But I can see other >> things being interesting, such as which user is on the other end (when >> pg_ident is in use), more detailed SSL information, full kerberos >> principal when kerberos in use etc. > >> I doubt this is common enough to want to stick it in pg_stat_activity >> though, but what do people think? And if not there, as a separate view >> or just as a function to call (e.g. >> pg_get_detailed_authinfo(<backendpid>)) > > By and large, it's been thought to be a possible security hole to expose > such information, except possibly in the postmaster log. I'm certainly > *not* in favor of creating a view for it. Well, it would obviously be superuser only. Would you object to a function as well? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Fri, Jan 21, 2011 at 10:14 AM, Magnus Hagander <magnus@hagander.net> wrote: > On Fri, Jan 21, 2011 at 15:51, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Magnus Hagander <magnus@hagander.net> writes: >>> I came across a case this week where I wanted to be able to determine >>> more detailed auth information on already logged in sessions - not >>> from the client, but from the server. In this specific case, I wanted >>> to examine the "is ssl" flag on the connection. But I can see other >>> things being interesting, such as which user is on the other end (when >>> pg_ident is in use), more detailed SSL information, full kerberos >>> principal when kerberos in use etc. >> >>> I doubt this is common enough to want to stick it in pg_stat_activity >>> though, but what do people think? And if not there, as a separate view >>> or just as a function to call (e.g. >>> pg_get_detailed_authinfo(<backendpid>)) >> >> By and large, it's been thought to be a possible security hole to expose >> such information, except possibly in the postmaster log. I'm certainly >> *not* in favor of creating a view for it. > > Well, it would obviously be superuser only. What if the user's password is in their connection string? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Jan 21, 2011 at 16:32, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Jan 21, 2011 at 10:14 AM, Magnus Hagander <magnus@hagander.net> wrote: >> On Fri, Jan 21, 2011 at 15:51, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Magnus Hagander <magnus@hagander.net> writes: >>>> I came across a case this week where I wanted to be able to determine >>>> more detailed auth information on already logged in sessions - not >>>> from the client, but from the server. In this specific case, I wanted >>>> to examine the "is ssl" flag on the connection. But I can see other >>>> things being interesting, such as which user is on the other end (when >>>> pg_ident is in use), more detailed SSL information, full kerberos >>>> principal when kerberos in use etc. >>> >>>> I doubt this is common enough to want to stick it in pg_stat_activity >>>> though, but what do people think? And if not there, as a separate view >>>> or just as a function to call (e.g. >>>> pg_get_detailed_authinfo(<backendpid>)) >>> >>> By and large, it's been thought to be a possible security hole to expose >>> such information, except possibly in the postmaster log. I'm certainly >>> *not* in favor of creating a view for it. >> >> Well, it would obviously be superuser only. > > What if the user's password is in their connection string? Um, none of the fields I've suggested so far was "connection string". In fact, that would be Pretty Darn Hard without modifying the client to actually *send* the connection string. Which id doesn't. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Fri, Jan 21, 2011 at 10:34 AM, Magnus Hagander <magnus@hagander.net> wrote: > Um, none of the fields I've suggested so far was "connection string". > In fact, that would be Pretty Darn Hard without modifying the client > to actually *send* the connection string. Which id doesn't. So... is there centralized structure which contains the info you're thinking of exposing? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Jan 21, 2011 at 16:40, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Jan 21, 2011 at 10:34 AM, Magnus Hagander <magnus@hagander.net> wrote: >> Um, none of the fields I've suggested so far was "connection string". >> In fact, that would be Pretty Darn Hard without modifying the client >> to actually *send* the connection string. Which id doesn't. > > So... is there centralized structure which contains the info you're > thinking of exposing? No, not today. That's what would have to be created. (And before you or somebody says something, no, it's not on the CF, so this is likely a 9.2 feature unless that structure thingy turns out to be a lot *less* code than I think it will) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Fri, Jan 21, 2011 at 10:43 AM, Magnus Hagander <magnus@hagander.net> wrote: > On Fri, Jan 21, 2011 at 16:40, Robert Haas <robertmhaas@gmail.com> wrote: >> On Fri, Jan 21, 2011 at 10:34 AM, Magnus Hagander <magnus@hagander.net> wrote: >>> Um, none of the fields I've suggested so far was "connection string". >>> In fact, that would be Pretty Darn Hard without modifying the client >>> to actually *send* the connection string. Which id doesn't. >> >> So... is there centralized structure which contains the info you're >> thinking of exposing? > > No, not today. That's what would have to be created. (And before you > or somebody says something, no, it's not on the CF, so this is likely > a 9.2 feature unless that structure thingy turns out to be a lot > *less* code than I think it will) Well, unlike Tom, I don't object to the basic idea, but I reserve the right to object in detail. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Magnus Hagander <magnus@hagander.net> writes:
> On Fri, Jan 21, 2011 at 16:40, Robert Haas <robertmhaas@gmail.com> wrote:
>> So... is there centralized structure which contains the info you're
>> thinking of exposing?
> No, not today.
I think that most of the info Magnus suggested isn't stored at all,
anywhere, at the moment; much less stored in shared memory.
regards, tom lane
On Fri, Jan 21, 2011 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> On Fri, Jan 21, 2011 at 16:40, Robert Haas <robertmhaas@gmail.com> wrote: >>> So... is there centralized structure which contains the info you're >>> thinking of exposing? > >> No, not today. > > I think that most of the info Magnus suggested isn't stored at all, > anywhere, at the moment; much less stored in shared memory. Oh. Well, that's another can of worms, then. I think this conversation is best deferred at least until we get a 9.1 beta out the door; we're clearly not going to do anything about it right now... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company