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
[EMAIL PROTECTED] | (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. */
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
