Thread: user connection not recorded?

On 07/30/2015 06:42 AM, Melvin Davidson wrote:
> I have a puzzling question.
>
> All through the error log, there are connections for [unknown] user.
> EG:
> 2015-07-30 00:00:00 CDT [6577]: [1-1]: : [unknown]: LOG:  connection
> received: host=173.239.101.98 port=50687
>
> The log_line_prefix   is  %t [%p]: [%l-1]: %h: %u:
>
> I can understand that the host is not available in nslookup, but why is
> the user not being recorded?

A quick look at the source shows that Postgres system process can have NULL username:

postinit.c:

 else if (IsBackgroundWorker)
        {
                if (username == NULL)
                {
                        InitializeSessionUserIdStandalone();
                        am_superuser = true;

which is then turned into 'unknown'

elog.c:

case 'u':
    if (MyProcPort)
        {
          const char *username = MyProcPort->user_name;

          if (username == NULL || *username == '\0')
                 username = _("[unknown]");


>
> *Melvin Davidson*


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 07/30/2015 06:42 AM, Melvin Davidson wrote:
>> I can understand that the host is not available in nslookup, but why is
>> the user not being recorded?

> A quick look at the source shows that Postgres system process can have NULL username:

Well, the real point is that that message comes out before we've collected
the startup packet, so we don't *have* a username yet.  All we know is
that somebody's opened a TCP connection.  The later "connection
authorized" message will tell you what database user name they gave.

If you see lots more "connection received" than "connection authorized"
then somebody is connecting to your postmaster and failing to
authenticate, which is usually a good thing to investigate.

            regards, tom lane

On 07/30/2015 07:12 AM, Melvin Davidson wrote:
> Thanks  for the quick reply Adrian.

Further testing shows this can happen in other situations.

So:

aklaver@panda:~> psql -d test
psql (9.4.2)
Type "help" for help.

test=>

where trust is set up for local connections yields:

[unknown]-2015-07-30 07:25:36.614 PDT-0LOG:  connection received:
host=[local]
aklaver-2015-07-30 07:25:36.615 PDT-0LOG:  connection authorized:
user=aklaver database=test

So unknown is a placeholder until a username is found or not.

>
> On Thu, Jul 30, 2015 at 10:09 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 07/30/2015 06:42 AM, Melvin Davidson wrote:
>     > I have a puzzling question.
>     >
>     > All through the error log, there are connections for [unknown] user.
>     > EG:
>     > 2015-07-30 00:00:00 CDT [6577]: [1-1]: : [unknown]: LOG:  connection
>     > received: host=173.239.101.98 <tel:173.239.101.98> port=50687
>     >
>     > The log_line_prefix   is  %t [%p]: [%l-1]: %h: %u:
>     >
>     > I can understand that the host is not available in nslookup, but why is
>     > the user not being recorded?
>
>     A quick look at the source shows that Postgres system process can
>     have NULL username:
>
>     postinit.c:
>
>       else if (IsBackgroundWorker)
>              {
>                      if (username == NULL)
>                      {
>                              InitializeSessionUserIdStandalone();
>                              am_superuser = true;
>
>     which is then turned into 'unknown'
>
>     elog.c:
>
>     case 'u':
>          if (MyProcPort)
>              {
>                const char *username = MyProcPort->user_name;
>
>                if (username == NULL || *username == '\0')
>                       username = _("[unknown]");
>
>
>      >
>      > *Melvin Davidson*
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/30/2015 07:21 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 07/30/2015 06:42 AM, Melvin Davidson wrote:
>>> I can understand that the host is not available in nslookup, but why is
>>> the user not being recorded?
>
>> A quick look at the source shows that Postgres system process can have NULL username:
>
> Well, the real point is that that message comes out before we've collected
> the startup packet, so we don't *have* a username yet.  All we know is
> that somebody's opened a TCP connection.  The later "connection
> authorized" message will tell you what database user name they gave.

Eventually got around to figuring that. So just for my reference, the
code snippet I showed from postinit.c seems to show a path where a
username is not used but is  substituted with BOOTSTRAP_SUPERUSERID.

Am I following that correctly and what is BOOTSTRAP_SUPERUSERID?

>
> If you see lots more "connection received" than "connection authorized"
> then somebody is connecting to your postmaster and failing to
> authenticate, which is usually a good thing to investigate.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> Eventually got around to figuring that. So just for my reference, the
> code snippet I showed from postinit.c seems to show a path where a
> username is not used but is  substituted with BOOTSTRAP_SUPERUSERID.

That's single-user mode.

> Am I following that correctly and what is BOOTSTRAP_SUPERUSERID?

BOOTSTRAP_SUPERUSERID is the OID of the precreated superuser.

The reason for that hack is to ensure you can still log in, in single-user
mode, even if you've done something stupid like "delete from pg_authid".
You'll be running under a user OID that doesn't actually exist in
pg_authid, but it won't matter because the code will believe you're a
superuser and will never go looking for the missing row.  Then you
can create yourself a new superuser, and resolve not to do that again.

            regards, tom lane

On 07/30/2015 07:46 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> Eventually got around to figuring that. So just for my reference, the
>> code snippet I showed from postinit.c seems to show a path where a
>> username is not used but is  substituted with BOOTSTRAP_SUPERUSERID.
>
> That's single-user mode.

Alright, but the part that has me confused is this comment in the code:

  * In standalone mode and in autovacuum worker processes, we use a fixed
  * ID, otherwise we figure it out from the authenticated user name.

and this

else if (IsBackgroundWorker)

I read the above to mean background processes also follow that path. I
have been trying to figure more about what goes on internally in
Postgres and reading the source seems the bet way to do that. Reading it
wrong is not helpful, so I would appreciate corrections to my
understanding or lack thereof.

>
>> Am I following that correctly and what is BOOTSTRAP_SUPERUSERID?
>
> BOOTSTRAP_SUPERUSERID is the OID of the precreated superuser.
>
> The reason for that hack is to ensure you can still log in, in single-user
> mode, even if you've done something stupid like "delete from pg_authid".
> You'll be running under a user OID that doesn't actually exist in
> pg_authid, but it won't matter because the code will believe you're a
> superuser and will never go looking for the missing row.  Then you
> can create yourself a new superuser, and resolve not to do that again.

Not that I ever done anything like that:)

>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> Alright, but the part that has me confused is this comment in the code:

>   * In standalone mode and in autovacuum worker processes, we use a fixed
>   * ID, otherwise we figure it out from the authenticated user name.

> and this

> else if (IsBackgroundWorker)

> I read the above to mean background processes also follow that path.

Yeah, I suppose so, since they don't really have a username to use.

            regards, tom lane