Re: [QUESTIONS] How is PostgreSQL doing?

I found this patch in my mailbox.  Is there any intestest in this, or is
it too site-specific?

>
> Eze Ogwuma writes:
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > Can you be specific?  Something I can add to the TODO list.
> >
> > Database based access for users so that each user can be giving access
> > to a particular database only. More permissions for each databse user:
> > Create, Drop, Select, Insert etc. Possibly table based
> > authentification as well.
>
> I needed to do that for the web database that I'm setting up. We have
> 20000 users and each (potentially) needs a separate database which is
> only accessible to them. Rather than having 20000 lines in pg_hba.conf,
> I've patched Postgres so that the special token "%username" in the
> database field of pg_hba.conf allows access only to the username which
> is connecting. (I chose the leading "%" so that it couldn't clash with
> a real database name.) Since the patch is against 6.1 rather than
> 6.2beta, I hadn't made it public. Here it is in case it's of interest.
>
> ----------------------------- cut here -----------------------------
> --- postgresql-v6.1/src/include/libpq/hba.h.ORI    Wed Jul 30 18:05:12 1997
> +++ postgresql-v6.1/src/include/libpq/hba.h    Wed Jul 30 18:05:37 1997
> @@ -42,7 +42,7 @@
>  hba_recvauth(const Port *port, const char database[], const char user[],
>               const char DataDir[]);
>  void find_hba_entry(const char DataDir[], const struct in_addr ip_addr,
> -            const char database[],
> +            const char user[], const char database[],
>              bool *host_ok_p, enum Userauth *userauth_p,
>              char usermap_name[], bool find_password_entries);
>
> --- postgresql-v6.1/src/backend/libpq/hba.c.ORI    Wed Jul 30 18:05:47 1997
> +++ postgresql-v6.1/src/backend/libpq/hba.c    Thu Jul 31 14:18:03 1997
> @@ -144,8 +144,8 @@
>
>  static void
>  process_hba_record(FILE *file,
> -                   const struct in_addr ip_addr, const char database[],
> -                   bool *matches_p, bool *error_p,
> +                   const struct in_addr ip_addr, const char user[],
> +                   const char database[], bool *matches_p, bool *error_p,
>                     enum Userauth *userauth_p, char usermap_name[],
>             bool find_password_entries) {
>  /*---------------------------------------------------------------------------
> @@ -173,7 +173,8 @@
>        if (buf[0] == '\0') *matches_p = false;
>        else {
>          /* If this record isn't for our database, ignore it. */
> -        if (strcmp(buf, database) != 0 && strcmp(buf, "all") != 0) {
> +        if (strcmp(buf, database) != 0 && strcmp(buf, "all") != 0
> +            && (strcmp(buf, "%username") != 0 || strcmp(user, database) != 0)) {
>            *matches_p = false;
>            read_through_eol(file);
>          } else {
> @@ -235,7 +236,8 @@
>
>  static void
>  process_open_config_file(FILE *file,
> -                         const struct in_addr ip_addr, const char database[],
> +                         const struct in_addr ip_addr,
> +                         const char user[], const char database[],
>                           bool *host_ok_p, enum Userauth *userauth_p,
>                           char usermap_name[], bool find_password_entries) {
>  /*---------------------------------------------------------------------------
> @@ -261,7 +263,7 @@
>      else {
>        if (c == '#') read_through_eol(file);
>        else {
> -        process_hba_record(file, ip_addr, database,
> +        process_hba_record(file, ip_addr, user, database,
>                             &found_entry, &error, userauth_p, usermap_name,
>                 find_password_entries);
>        }
> @@ -277,7 +279,7 @@
>
>  void
>  find_hba_entry(const char DataDir[], const struct in_addr ip_addr,
> -               const char database[],
> +               const char user[], const char database[],
>                 bool *host_ok_p, enum Userauth *userauth_p,
>                 char usermap_name[], bool find_password_entries) {
>  /*--------------------------------------------------------------------------
> @@ -348,8 +350,8 @@
>        fputs(PQerrormsg, stderr);
>        pqdebug("%s", PQerrormsg);
>      } else {
> -      process_open_config_file(file, ip_addr, database, host_ok_p, userauth_p,
> -                               usermap_name, find_password_entries);
> +      process_open_config_file(file, ip_addr, user, database, host_ok_p,
> +                               userauth_p, usermap_name, find_password_entries);
>        fclose(file);
>      }
>      free(conf_file);
> @@ -719,7 +721,7 @@
>      /* Our eventual return value */
>
>
> -  find_hba_entry(DataDir, port->raddr.sin_addr, database,
> +  find_hba_entry(DataDir, port->raddr.sin_addr, user, database,
>                   &host_ok, &userauth, usermap_name,
>           false /* don't find password entries of type 'password' */);
>
> --- postgresql-v6.1/src/backend/libpq/password.c.ORI    Wed Jul 30 18:05:55 1997
> +++ postgresql-v6.1/src/backend/libpq/password.c    Wed Jul 30 18:06:43 1997
> @@ -23,7 +23,7 @@
>      char *p, *test_user, *test_pw;
>      char salt[3];
>
> -    find_hba_entry(DataDir, port->raddr.sin_addr, database,
> +    find_hba_entry(DataDir, port->raddr.sin_addr, user, database,
>             &host_ok, &userauth, pw_file_name, true);
>
>      if(!host_ok) {
> ----------------------------- cut here -----------------------------
>
> --Malcolm
>
> --
> Malcolm Beattie <mbeattie@sable.ox.ac.uk>
> Unix Systems Programmer
> Oxford University Computing Services
>
>


--
Bruce Momjian
maillist@candle.pha.pa.us