Subject: Who am I?

From: [email-masked]
Date: Sun, 26 Apr 92 19:59:08 -0700

When connecting to a backend via libpq from a remote host, who does the
backend think I am? I want to define rules that disallow access to
instances based on attributes of those instances, depending on the user
trying to do the retrievals; thus, I need some way to know who the user
performing the query is. The manual claims that pg_username() returns
the user name of the "current user" (define rule man page), but what
does that mean? Does libpq transmit the UID of the user? If the remote
user's number UID is not one of the ones in the system catalogs (entered
via createuser, I assume), then are libpq connections disallowed?

Also, is there a way to get rid of a rule? I don't want to start mucking
about with thes and find I can't unmuck.

Enquiring minds want to know...
			-- Sean
--
Sean Levy <[email-masked]>		   `... from intuition we can pass to
CMU Engineering Design Research Center      analysis, but not from analysis to
ndim Group                                  intuition.' --Henri Bergson
                      <--  Standard Disclaimers Apply  -->

---

From: [email-masked] (Juergen Schoenwaelder)
Date: Wed, 29 Apr 92 12:04:35 MET DST

Hi!
 
> via createuser, I assume), then are libpq connections disallowed?

I had to look in $POSTGRESHOME/src/lib/libpq/*.c, because I wanted to
execute queries from a host during system boot. I always got a core
dump and so I found in fe-pqexec.c:

/* ----------------
 *      EstablishComm
 * ----------------
 */
static
void
EstablishComm()
{
    if (!PQportset) {
        read_initstr();

        if (pq_connect(PQdatabase, getenv("USER"), PQoption, PQhost, PQtty,
                        (char *) NULL, (short)atoi(PQport) ) == -1 ) {
            libpq_raise(&ProtocolError,
              form((int)"Failed to connect to backend (host=%s, port=%s)",
                   PQhost, PQport));
        }

        pq_flush();
        PQportset = 1;
    }
}

EstablishComm() initializes a connection. This functions gets the user name 
using the getenv() function. I got core dumps, because there is no environment
variable USER during system boot. Now, I looked into pq_connect() and I
found that the user name is transmitted to the backend as a normal string.

It should be clear, that fooling a backend about the user identity 
is no problem. We are currently thinking about using kerberos to
authenticate user for remote database access.

							Juergen

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 30 Apr 92 00:54:11 PDT

> via createuser, I assume), then are libpq connections disallowed?

The backend gets the user's name as a character string in the start-up
packet from the front-end.  It will believe whatever the front-end tells
it.  pg_username() returns a pointer to this string.

We make a *feeble* attempt to lock out unauthorized users by looking in
pg_user for the user name, but it would be simplicity itself to fake the
system out.

> about with thes and find I can't unmuck.

The commands you want are:

	remove instance rule <rulename>
	remove rewrite rule <rulename>

> Enquiring minds want to know...

Did you hear about the Elvis sighting?

Jeff Meredith
[email-masked]

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 30 Apr 92 12:20:39 PDT

> dump and so I found in fe-pqexec.c:

 ...
> found that the user name is transmitted to the backend as a normal string.

bug above.

> authenticate user for remote database access.

This is one of the many reasons why I often tell everyone that security
in postgres is nonexistant.

Jeff Meredith