Re: Open 7.3 items - Mailing list pgsql-hackers

OK, I have attached a patch for testing.  Sample output is:

    $ sql -U guest test
    psql: FATAL:  user "test.guest" does not exist
    $ createuser test.guest
    Shall the new user be allowed to create databases? (y/n) n
    Shall the new user be allowed to create more new users? (y/n) n
    CREATE USER
    #$ sql -U guest test
    Welcome to psql, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help on internal slash commands
           \g or terminate with semicolon to execute query
           \q to quit

    test=>

The patch is quite small.  All it does is prepend the database name to
the user name supplied with the connection request when
db_user_namespace is true.

This is not ready for application.  I can find no way from the
postmaster to determine if the user is the super-user and hence bypass
the database prepending.  I was going to do that _only_ for the username
who created the installation for initdb.  Maybe I have to dump that name
out to a file and read it in from the postmaster.  Other ideas?

It also needs documentation.

I am unsure about auto-prepending the dbname for CREATE USER and other
cases.  That could get confusing, especially because createuser accesses
template1, and we would have to handle all other username mentions, like
in GRANT.  We may be better just leaving it along and telling admins
they have to quality the username in those cases.

---------------------------------------------------------------------------

Marc G. Fournier wrote:
> On Wed, 31 Jul 2002, Bruce Momjian wrote:
>
> > Marc G. Fournier wrote:
> > > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> > >
> > > > Marc G. Fournier wrote:
> > > > > > Access to nothing.  I could actually try to quality by dbname.username,
> > > > > > then fall back to just username, but that seems insecure.
> > > > >
> > > > > No, that's cool ... just questions I thought of ...
> > > >
> > > > OK.
> > > >
> > > > > Okay ... hmmm ... just making sure that I understand ... I setup a server,
> > > > > when does this dbname.* come into play?  Only if I enable password/md5 in
> > > > > pg_hba.conf for a specific database?  all others would still use a plain
> > > > > 'username' still works?  or are you getting rid of the 'global usernames'
> > > > > altogether (which is cool too, just want to clarify) ...
> > > >
> > > > There will be a GUC param db_user_namespace which will turn it on/off
> > > > for all access to the cluster _except_ for the super-user.
> > >
> > > Okay ... cluster == database server, or a subset of databases within the
> > > server?  I know what I think of as a cluster, and somehow I suspect this
> > > has to do with the new schema stuff, which means I *really* have to find
> > > time to do some catch-up reading ;)  need more hours in day, days in week
> >
> > Cluster is db server in this case.
>
> 'K, cool, thanks :)
>
> Okay, final request .. how hard would it be to pre-pend the current
> database name if GUC value is on?  ie. if I'm in db1 and run CREATE USER,
> it will add db1. to the username if I hadn't already?   Sounds to me it
> would be simple to do, and it would "fix" the point I made about being
> able to have a db "owner" account with create user privileges (ie. if I'm
> in db1 and run CREATE USER db2.bruce, it should reject that unless I've
> got create database prileges *and* create user) ...
>
> Other then that, most elegant solution, IMHO :)
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/libpq/auth.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/libpq/auth.c,v
retrieving revision 1.82
diff -c -r1.82 auth.c
*** src/backend/libpq/auth.c    20 Jun 2002 20:29:28 -0000    1.82
--- src/backend/libpq/auth.c    1 Aug 2002 05:13:35 -0000
***************
*** 117,123 ****
               version, PG_KRB4_VERSION);
          return STATUS_ERROR;
      }
!     if (strncmp(port->user, auth_data.pname, SM_USER) != 0)
      {
          elog(LOG, "pg_krb4_recvauth: name \"%s\" != \"%s\"",
               port->user, auth_data.pname);
--- 117,123 ----
               version, PG_KRB4_VERSION);
          return STATUS_ERROR;
      }
!     if (strncmp(port->user, auth_data.pname, SM_DATABASE_USER) != 0)
      {
          elog(LOG, "pg_krb4_recvauth: name \"%s\" != \"%s\"",
               port->user, auth_data.pname);
***************
*** 290,296 ****
      }

      kusername = pg_an_to_ln(kusername);
!     if (strncmp(port->user, kusername, SM_USER))
      {
          elog(LOG, "pg_krb5_recvauth: user name \"%s\" != krb5 name \"%s\"",
               port->user, kusername);
--- 290,296 ----
      }

      kusername = pg_an_to_ln(kusername);
!     if (strncmp(port->user, kusername, SM_DATABASE_USER))
      {
          elog(LOG, "pg_krb5_recvauth: user name \"%s\" != krb5 name \"%s\"",
               port->user, kusername);
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.281
diff -c -r1.281 postmaster.c
*** src/backend/postmaster/postmaster.c    13 Jul 2002 01:02:14 -0000    1.281
--- src/backend/postmaster/postmaster.c    1 Aug 2002 05:13:37 -0000
***************
*** 192,197 ****
--- 192,199 ----
  bool        HostnameLookup;        /* for ps display */
  bool        ShowPortNumber;
  bool        Log_connections = false;
+ bool        Db_user_namespace = false;
+

  /* Startup/shutdown state */
  static pid_t StartupPID = 0,
***************
*** 1156,1161 ****
--- 1158,1173 ----
      /* Check a user name was given. */
      if (port->user[0] == '\0')
          elog(FATAL, "no PostgreSQL user name specified in startup packet");
+
+     /* Prefix database name for per-db user namespace */
+     /* XXX look up super-user name from postmaster */
+     if (Db_user_namespace && strcmp(port->user, "postgres"))
+     {
+         char hold_user[SM_DATABASE_USER];
+         snprintf(hold_user, SM_DATABASE_USER, "%s.%s", port->database,
+                  port->user);
+         strcpy(port->user, hold_user);
+     }

      /*
       * If we're going to reject the connection due to database state, say
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.76
diff -c -r1.76 guc.c
*** src/backend/utils/misc/guc.c    30 Jul 2002 16:20:03 -0000    1.76
--- src/backend/utils/misc/guc.c    1 Aug 2002 05:13:40 -0000
***************
*** 481,486 ****
--- 481,490 ----
          { "transform_null_equals", PGC_USERSET }, &Transform_null_equals,
          false, NULL, NULL
      },
+     {
+         { "db_user_namespace", PGC_SIGHUP }, &Db_user_namespace,
+         false, NULL, NULL
+     },

      {
          { NULL, 0 }, NULL, false, NULL, NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.42
diff -c -r1.42 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    30 Jul 2002 04:24:54 -0000    1.42
--- src/backend/utils/misc/postgresql.conf.sample    1 Aug 2002 05:13:40 -0000
***************
*** 112,118 ****
  #
  #    Message display
  #
-
  #server_min_messages = notice    # Values, in order of decreasing detail:
                  #   debug5, debug4, debug3, debug2, debug1,
                  #   info, notice, warning, error, log, fatal,
--- 112,117 ----
***************
*** 200,202 ****
--- 199,202 ----
  #sql_inheritance = true
  #transform_null_equals = false
  #statement_timeout = 0                # 0 is disabled
+ #db_user_namespace = false
Index: src/include/libpq/libpq-be.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/libpq/libpq-be.h,v
retrieving revision 1.32
diff -c -r1.32 libpq-be.h
*** src/include/libpq/libpq-be.h    20 Jun 2002 20:29:49 -0000    1.32
--- src/include/libpq/libpq-be.h    1 Aug 2002 05:13:40 -0000
***************
*** 59,65 ****

      ProtocolVersion proto;
      char        database[SM_DATABASE + 1];
!     char        user[SM_USER + 1];
      char        options[SM_OPTIONS + 1];
      char        tty[SM_TTY + 1];
      char        auth_arg[MAX_AUTH_ARG];
--- 59,65 ----

      ProtocolVersion proto;
      char        database[SM_DATABASE + 1];
!     char        user[SM_DATABASE_USER + 1];
      char        options[SM_OPTIONS + 1];
      char        tty[SM_TTY + 1];
      char        auth_arg[MAX_AUTH_ARG];
***************
*** 72,78 ****
      SSL           *ssl;
      X509       *peer;
      char        peer_dn[128 + 1];
!     char        peer_cn[SM_USER + 1];
      unsigned long count;
  #endif
  } Port;
--- 72,78 ----
      SSL           *ssl;
      X509       *peer;
      char        peer_dn[128 + 1];
!     char        peer_cn[SM_DATABASE_USER + 1];
      unsigned long count;
  #endif
  } Port;
Index: src/include/libpq/pqcomm.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/libpq/pqcomm.h,v
retrieving revision 1.64
diff -c -r1.64 pqcomm.h
*** src/include/libpq/pqcomm.h    20 Jun 2002 20:29:49 -0000    1.64
--- src/include/libpq/pqcomm.h    1 Aug 2002 05:13:40 -0000
***************
*** 114,119 ****
--- 114,121 ----
  #define SM_DATABASE        64
  /* SM_USER should be the same size as the others.  bjm 2002-06-02 */
  #define SM_USER            32
+ /* We prepend database name if db_user_namespace true. */
+ #define SM_DATABASE_USER (SM_DATABASE+SM_USER)
  #define SM_OPTIONS        64
  #define SM_UNUSED        64
  #define SM_TTY            64
***************
*** 124,135 ****
--- 126,139 ----
  {
      ProtocolVersion protoVersion;        /* Protocol version */
      char        database[SM_DATABASE];    /* Database name */
+                 /* Db_user_namespace prepends dbname */
      char        user[SM_USER];    /* User name */
      char        options[SM_OPTIONS];    /* Optional additional args */
      char        unused[SM_UNUSED];        /* Unused */
      char        tty[SM_TTY];    /* Tty for debug output */
  } StartupPacket;

+ extern bool Db_user_namespace;

  /* These are the authentication requests sent by the backend. */