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(at)candle(dot)pha(dot)pa(dot)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(at)sable(dot)ox(dot)ac(dot)uk>
> Unix Systems Programmer
> Oxford University Computing Services
>
>

--
Bruce Momjian
maillist(at)candle(dot)pha(dot)pa(dot)us