default tablespace for roles

Hi!

It would be nice to have tablespaces for each users. This is a small
pathc that does the job.
- gramar file: "alter|create user ... with default tablespace 'tblspc'"
added;
- new column in pg_authid: roltblspc, an Oid referring to the pg_tablespace
- at alter/add role neccessary default tablepsace modifications+checking
if it exists
- pg_dumall outputs script that alters users with default tablespace
after dumping users and tablespaces

(for 8.2, if you like it)
Regards,
Laszlo

Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.3
diff -r1.3 pg_authid.h
51a52

Oid roltblspc; /* default tablespace oid */

74c75
< #define Natts_pg_authid 11
---

#define Natts_pg_authid 12

82,85c83,87
< #define Anum_pg_authid_rolconnlimit 8
< #define Anum_pg_authid_rolpassword 9
< #define Anum_pg_authid_rolvaliduntil 10
< #define Anum_pg_authid_rolconfig 11
---

#define Anum_pg_authid_roltblspc 8
#define Anum_pg_authid_rolconnlimit 9
#define Anum_pg_authid_rolpassword 10
#define Anum_pg_authid_rolvaliduntil 11
#define Anum_pg_authid_rolconfig 12

94c96
< DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
---

DATA(insert OID = 10 ( "POSTGRES" t t t t t t 1663 -1 _null_ _null_

_null_ ));
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.66
diff -r1.66 pg_dumpall.c
718a719,735

res = executeQuery(conn, "SELECT rolname, spcname from

pg_catalog.pg_authid inner join pg_catalog.pg_tablespace on
pg_tablespace.oid = pg_authid.roltblspc");

if (PQntuples(res) > 0)
printf("--\n-- User default tablespaces\n--\n\n");

for (i = 0; i < PQntuples(res); i++)
{
char *rolname = PQgetvalue(res, i, 0);
char *spcname = PQgetvalue(res, i, 1);

printf("ALTER ROLE %s WITH DEFAULT TABLESPACE '%s';\n",

rolname, spcname);

}

PQclear(res);
printf("\n\n");

Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.507
diff -r2.507 gram.y
690a691,694

| DEFAULT TABLESPACE Sconst
{
$$ = makeDefElem("roltblspc", (Node

*)makeString($3));

}

Index: src/backend/commands/user.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.160
diff -r1.160 user.c
93a94

char *tblspc = NULL;

104a106

DefElem *dtblspc = NULL;

123a126,133

if(strcmp(defel->defname, "roltblspc") == 0){
if(dtblspc)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
dtblspc = defel;
} else

227a238,239

if (dtblspc)
tblspc = strVal(dtblspc -> arg);

307a320,328

if(tblspc) {
Oid userTblSpc;
userTblSpc = get_tablespace_oid(tblspc);
if(!OidIsValid(userTblSpc))
elog(ERROR, "Tablespace %s does not exist", tblspc);
new_record[Anum_pg_authid_roltblspc -1] =

DatumGetObjectId(userTblSpc);

} else {
new_record_nulls[Anum_pg_authid_roltblspc -1] = 'n';
}

419a441

char *tblspc = NULL;

429a452

DefElem *dtblspc = NULL;

435a459,466

if(strcmp(defel->defname, "roltblspc") == 0){
if(dtblspc)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
dtblspc = defel;
} else

538c569,571
<
---

if(dtblspc) {
tblspc = strVal(dtblspc -> arg);
}

653a687,692

if(tblspc)
{
new_record[Anum_pg_authid_roltblspc -1] =

get_tablespace_oid(tblspc);

new_record_repl[Anum_pg_authid_roltblspc -1] = 'r';
}

Index: src/backend/commands/tablespace.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.26
diff -r1.26 tablespace.c
56a57

#include "catalog/pg_authid.h"

888a890,902

Oid userDefTblSpc;
HeapTuple authId;
Oid sessionUser;

/* Get the session users default tablespace */
sessionUser = GetSessionUserId();
authId = SearchSysCache(AUTHOID, ObjectIdGetDatum(sessionUser),

0, 0, 0);

userDefTblSpc = ((Form_pg_authid) GETSTRUCT(authId))->roltblspc;
ReleaseSysCache(authId);

/* if it was set, return it, otherwise the old procedure takes

place */

Show quoted text

if(OidIsValid(userDefTblSpc))
return userDefTblSpc;