Skip to content

Commit 85add42

Browse files
bmomjianbmomjian
committed
I have large database and with this DB work more users and I very need
more restriction for fretful users. The current PG allow define only NO-CREATE-DB and NO-CREATE-USER restriction, but for some users I need NO-CREATE-TABLE and NO-LOCK-TABLE. This patch add to current code NOCREATETABLE and NOLOCKTABLE feature: CREATE USER username [ WITH [ SYSID uid ] [ PASSWORD 'password' ] ] [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] -> [ CREATETABLE | NOCREATETABLE ] [ LOCKTABLE | NOLOCKTABLE ] ...etc. If CREATETABLE or LOCKTABLE is not specific in CREATE USER command, as default is set CREATETABLE or LOCKTABLE (true). A user with NOCREATETABLE restriction can't call CREATE TABLE or SELECT INTO commands, only create temp table is allow for him. Karel
1 parent a672e96 commit 85add42

File tree

13 files changed

+225
-53
lines changed

13 files changed

+225
-53
lines changed

‎src/backend/commands/command.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.77 2000/06/04 22:04:32 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.78 2000/06/09 15:50:43 momjian Exp $
1212
*
1313
* NOTES
1414
* The PortalExecutorHeapMemory crap needs to be eliminated
@@ -30,6 +30,7 @@
3030
#include "commands/command.h"
3131
#include "executor/spi.h"
3232
#include "catalog/heap.h"
33+
#include "catalog/pg_shadow.h"
3334
#include "miscadmin.h"
3435
#include "optimizer/prep.h"
3536
#include "utils/acl.h"
@@ -1211,6 +1212,21 @@ LockTableCommand(LockStmt *lockstmt)
12111212
{
12121213
Relation rel;
12131214
int aclresult;
1215+
HeapTuple tup;
1216+
1217+
1218+
/* ----------
1219+
* Check pg_shadow for global lock setting
1220+
* ----------
1221+
*/
1222+
tup = SearchSysCacheTuple(SHADOWNAME, PointerGetDatum(GetPgUserName()), 0, 0, 0);
1223+
1224+
if (!HeapTupleIsValid(tup))
1225+
elog(ERROR, "LOCK TABLE: look at pg_shadow failed");
1226+
1227+
if (!((Form_pg_shadow) GETSTRUCT(tup))->uselocktable)
1228+
elog(ERROR, "LOCK TABLE: permission denied");
1229+
12141230

12151231
rel = heap_openr(lockstmt->relname, NoLock);
12161232
if (!RelationIsValid(rel))

‎src/backend/commands/creatinh.c

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,9 +9,9 @@
99
*
1010
* IDENTIFICATION
1111
<<<<<<< creatinh.c
12-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.59 2000/06/09 01:44:03 momjian Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.60 2000/06/09 15:50:43 momjian Exp $
1313
=======
14-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.59 2000/06/09 01:44:03 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.60 2000/06/09 15:50:43 momjian Exp $
1515
>>>>>>> 1.58
1616
*
1717
*-------------------------------------------------------------------------
@@ -26,8 +26,10 @@
2626
#include "catalog/pg_inherits.h"
2727
#include "catalog/pg_ipl.h"
2828
#include "catalog/pg_type.h"
29+
#include "catalog/pg_shadow.h"
2930
#include "commands/creatinh.h"
3031
#include "utils/syscache.h"
32+
#include "miscadmin.h"
3133

3234
/* ----------------
3335
* local stuff
@@ -63,6 +65,22 @@ DefineRelation(CreateStmt *stmt, char relkind)
6365
int i;
6466
AttrNumber attnum;
6567

68+
if (!stmt->istemp) {
69+
HeapTuple tup;
70+
71+
/* ----------
72+
* Check pg_shadow for global createTable setting
73+
* ----------
74+
*/
75+
tup = SearchSysCacheTuple(SHADOWNAME, PointerGetDatum(GetPgUserName()), 0, 0, 0);
76+
77+
if (!HeapTupleIsValid(tup))
78+
elog(ERROR, "CREATE TABLE: look at pg_shadow failed");
79+
80+
if (!((Form_pg_shadow) GETSTRUCT(tup))->usecreatetable)
81+
elog(ERROR, "CREATE TABLE: permission denied");
82+
}
83+
6684
if (strlen(stmt->relname) >= NAMEDATALEN)
6785
elog(ERROR, "the relation name %s is >= %d characters long",
6886
stmt->relname, NAMEDATALEN);

‎src/backend/commands/user.c

Lines changed: 35 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Header: /cvsroot/pgsql/src/backend/commands/user.c,v 1.58 2000/06/09 01:11:04 tgl Exp $
9+
* $Header: /cvsroot/pgsql/src/backend/commands/user.c,v 1.59 2000/06/09 15:50:43 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -250,6 +250,10 @@ CreateUser(CreateUserStmt *stmt)
250250
return;
251251
}
252252

253+
AssertState(BoolIsValid(stmt->createtable));
254+
new_record[Anum_pg_shadow_usecreatetable-1] = (Datum)(stmt->createtable);
255+
AssertState(BoolIsValid(stmt->locktable));
256+
new_record[Anum_pg_shadow_uselocktable-1] = (Datum)(stmt->locktable);
253257
/*
254258
* Build a tuple to insert
255259
*/
@@ -263,6 +267,8 @@ CreateUser(CreateUserStmt *stmt)
263267
AssertState(BoolIsValid(stmt->createuser));
264268
new_record[Anum_pg_shadow_usesuper - 1] = (Datum) (stmt->createuser);
265269
/* superuser gets catupd right by default */
270+
new_record_nulls[Anum_pg_shadow_usecreatetable-1] = ' ';
271+
new_record_nulls[Anum_pg_shadow_uselocktable-1] = ' ';
266272
new_record[Anum_pg_shadow_usecatupd - 1] = (Datum) (stmt->createuser);
267273

268274
if (stmt->password)
@@ -352,7 +358,8 @@ AlterUser(AlterUserStmt *stmt)
352358

353359
/* must be superuser or just want to change your own password */
354360
if (!superuser() &&
355-
!(stmt->createdb == 0 && stmt->createuser == 0 && !stmt->validUntil
361+
!(stmt->createdb==0 && stmt->createuser==0 && stmt->createtable==0
362+
&& stmt->locktable==0 && !stmt->validUntil
356363
&& stmt->password && strcmp(GetPgUserName(), stmt->user) == 0))
357364
elog(ERROR, "ALTER USER: permission denied");
358365

@@ -380,8 +387,32 @@ AlterUser(AlterUserStmt *stmt)
380387
/*
381388
* Build a tuple to update, perusing the information just obtained
382389
*/
383-
new_record[Anum_pg_shadow_usename - 1] = PointerGetDatum(namein(stmt->user));
384-
new_record_nulls[Anum_pg_shadow_usename - 1] = ' ';
390+
391+
/* createtable */
392+
if (stmt->createtable == 0)
393+
{
394+
/* don't change */
395+
new_record[Anum_pg_shadow_usecreatetable-1] = heap_getattr(tuple, Anum_pg_shadow_usecreatetable, pg_shadow_dsc, &null);
396+
new_record_nulls[Anum_pg_shadow_usecreatetable-1] = null ? 'n' : ' ';
397+
}
398+
else
399+
{
400+
new_record[Anum_pg_shadow_usecreatetable-1] = (Datum)(stmt->createtable > 0 ? true : false);
401+
new_record_nulls[Anum_pg_shadow_usecreatetable-1] = ' ';
402+
}
403+
404+
/* locktable */
405+
if (stmt->locktable == 0)
406+
{
407+
/* don't change */
408+
new_record[Anum_pg_shadow_uselocktable-1] = heap_getattr(tuple, Anum_pg_shadow_uselocktable, pg_shadow_dsc, &null);
409+
new_record_nulls[Anum_pg_shadow_uselocktable-1] = null ? 'n' : ' ';
410+
}
411+
else
412+
{
413+
new_record[Anum_pg_shadow_uselocktable-1] = (Datum)(stmt->locktable > 0 ? true : false);
414+
new_record_nulls[Anum_pg_shadow_uselocktable-1] = ' ';
415+
}
385416

386417
/* sysid - leave as is */
387418
new_record[Anum_pg_shadow_usesysid - 1] = heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_shadow_dsc, &null);

‎src/backend/parser/gram.y

Lines changed: 47 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.170 2000/06/09 01:44:18 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.171 2000/06/09 15:50:44 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -145,7 +145,8 @@ static void doNegateFloat(Value *v);
145145
%type <ival> opt_lock, lock_type
146146
%type <boolean> opt_lmode, opt_force
147147

148-
%type <ival> user_createdb_clause, user_createuser_clause
148+
%type <ival> user_createdb_clause, user_createuser_clause, user_createtable_clause,
149+
user_locktable_clause
149150
%type <str> user_passwd_clause
150151
%type <ival> sysid_clause
151152
%type <str> user_valid_clause
@@ -339,14 +340,14 @@ static void doNegateFloat(Value *v);
339340
*/
340341
%token ABORT_TRANS, ACCESS, AFTER, AGGREGATE, ANALYZE,
341342
BACKWARD, BEFORE, BINARY, BIT,
342-
CACHE, CLUSTER, COMMENT, COPY, CREATEDB, CREATEUSER, CYCLE,
343+
CACHE, CLUSTER, COMMENT, COPY, CREATEDB, CREATETABLE, CREATEUSER, CYCLE,
343344
DATABASE, DELIMITERS, DO,
344345
EACH, ENCODING, EXCLUSIVE, EXPLAIN, EXTEND,
345346
FORCE, FORWARD, FUNCTION, HANDLER,
346347
INCREMENT, INDEX, INHERITS, INSTEAD, ISNULL,
347-
LANCOMPILER, LIMIT, LISTEN, LOAD, LOCATION, LOCK_P,
348+
LANCOMPILER, LIMIT, LISTEN, LOAD, LOCATION, LOCK_P, LOCKTABLE,
348349
MAXVALUE, MINVALUE, MODE, MOVE,
349-
NEW, NOCREATEDB, NOCREATEUSER, NONE, NOTHING, NOTIFY, NOTNULL,
350+
NEW, NOCREATEDB, NOCREATETABLE, NOCREATEUSER, NOLOCKTABLE, NONE, NOTHING, NOTIFY, NOTNULL,
350351
OFFSET, OIDS, OPERATOR, PASSWORD, PROCEDURAL,
351352
REINDEX, RENAME, RESET, RETURNS, ROW, RULE,
352353
SEQUENCE, SERIAL, SETOF, SHARE, SHOW, START, STATEMENT, STDIN, STDOUT, SYSID,
@@ -473,32 +474,37 @@ stmt : AlterTableStmt
473474
*
474475
*****************************************************************************/
475476

476-
CreateUserStmt: CREATE USER UserId
477-
user_createdb_clause user_createuser_clause user_group_clause
477+
CreateUserStmt: CREATE USER UserId user_createdb_clause user_createuser_clause
478+
user_createtable_clause user_locktable_clause user_group_clause
478479
user_valid_clause
479480
{
480481
CreateUserStmt *n = makeNode(CreateUserStmt);
481482
n->user = $3;
482-
n->sysid = -1;
483+
n->sysid = -1;
483484
n->password = NULL;
484485
n->createdb = $4 == +1 ? true : false;
485486
n->createuser = $5 == +1 ? true : false;
486-
n->groupElts = $6;
487-
n->validUntil = $7;
487+
n->createtable = $6 == +1 ? true : false;
488+
n->locktable = $7 == +1 ? true : false;
489+
n->groupElts = $8;
490+
n->validUntil = $9;
488491
$$ = (Node *)n;
489492
}
490493
| CREATE USER UserId WITH sysid_clause user_passwd_clause
491-
user_createdb_clause user_createuser_clause user_group_clause
494+
user_createdb_clause user_createuser_clause
495+
user_createtable_clause user_locktable_clause user_group_clause
492496
user_valid_clause
493497
{
494498
CreateUserStmt *n = makeNode(CreateUserStmt);
495499
n->user = $3;
496-
n->sysid = $5;
500+
n->sysid = $5;
497501
n->password = $6;
498502
n->createdb = $7 == +1 ? true : false;
499503
n->createuser = $8 == +1 ? true : false;
500-
n->groupElts = $9;
501-
n->validUntil = $10;
504+
n->createtable = $9 == +1 ? true : false;
505+
n->locktable = $10 == +1 ? true : false;
506+
n->groupElts = $11;
507+
n->validUntil = $12;
502508
$$ = (Node *)n;
503509
}
504510
;
@@ -510,27 +516,32 @@ CreateUserStmt: CREATE USER UserId
510516
*
511517
*****************************************************************************/
512518

513-
AlterUserStmt: ALTER USER UserId user_createdb_clause
514-
user_createuser_clause user_valid_clause
519+
AlterUserStmt: ALTER USER UserId user_createdb_clause user_createuser_clause
520+
user_createtable_clause user_locktable_clause user_valid_clause
515521
{
516522
AlterUserStmt *n = makeNode(AlterUserStmt);
517523
n->user = $3;
518524
n->password = NULL;
519525
n->createdb = $4;
520526
n->createuser = $5;
521-
n->validUntil = $6;
527+
n->createtable = $6;
528+
n->locktable = $7;
529+
n->validUntil = $8;
522530
$$ = (Node *)n;
523531
}
524532
| ALTER USER UserId WITH PASSWORD Sconst
525-
user_createdb_clause
526-
user_createuser_clause user_valid_clause
533+
user_createdb_clause user_createuser_clause
534+
user_createtable_clause user_locktable_clause
535+
user_valid_clause
527536
{
528537
AlterUserStmt *n = makeNode(AlterUserStmt);
529538
n->user = $3;
530539
n->password = $6;
531540
n->createdb = $7;
532541
n->createuser = $8;
533-
n->validUntil = $9;
542+
n->createtable = $9;
543+
n->locktable = $10;
544+
n->validUntil = $11;
534545
$$ = (Node *)n;
535546
}
536547
;
@@ -573,6 +584,22 @@ user_createuser_clause: CREATEUSER { $$ = +1; }
573584
| /*EMPTY*/ { $$ = 0; }
574585
;
575586

587+
user_createtable_clause: CREATETABLE { $$ = +1; }
588+
| NOCREATETABLE { $$ = -1; }
589+
| /*EMPTY*/ {
590+
/* EMPTY is default = CREATETABLE */
591+
$$ = +1;
592+
}
593+
;
594+
595+
user_locktable_clause: LOCKTABLE { $$ = +1; }
596+
| NOLOCKTABLE { $$ = -1; }
597+
| /*EMPTY*/ {
598+
/* EMPTY is default = LOCKTABLE */
599+
$$ = +1;
600+
}
601+
;
602+
576603
user_list: user_list ',' UserId
577604
{
578605
$$ = lcons((void*)makeString($3), $1);

‎src/backend/parser/keywords.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,9 +9,9 @@
99
*
1010
* IDENTIFICATION
1111
<<<<<<< keywords.c
12-
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.74 2000/06/09 01:44:18 momjian Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.75 2000/06/09 15:50:45 momjian Exp $
1313
=======
14-
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.74 2000/06/09 01:44:18 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.75 2000/06/09 15:50:45 momjian Exp $
1515
>>>>>>> 1.73
1616
*
1717
*-------------------------------------------------------------------------
@@ -75,6 +75,7 @@ static ScanKeyword ScanKeywords[] = {
7575
{"copy", COPY},
7676
{"create", CREATE},
7777
{"createdb", CREATEDB},
78+
{"createtable", CREATETABLE},
7879
{"createuser", CREATEUSER},
7980
{"cross", CROSS},
8081
{"current_date", CURRENT_DATE},
@@ -155,6 +156,7 @@ static ScanKeyword ScanKeywords[] = {
155156
{"local", LOCAL},
156157
{"location", LOCATION},
157158
{"lock", LOCK_P},
159+
{"locktable", LOCKTABLE},
158160
{"match", MATCH},
159161
{"maxvalue", MAXVALUE},
160162
{"minute", MINUTE_P},
@@ -170,7 +172,9 @@ static ScanKeyword ScanKeywords[] = {
170172
{"next", NEXT},
171173
{"no", NO},
172174
{"nocreatedb", NOCREATEDB},
175+
{"nocreatetable", NOCREATETABLE},
173176
{"nocreateuser", NOCREATEUSER},
177+
{"nolocktable", NOLOCKTABLE},
174178
{"none", NONE},
175179
{"not", NOT},
176180
{"nothing", NOTHING},

‎src/backend/tcop/pquery.c

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/tcop/pquery.c,v 1.32 2000/06/04 22:08:53 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/tcop/pquery.c,v 1.33 2000/06/09 15:50:46 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -20,6 +20,9 @@
2020
#include "executor/executor.h"
2121
#include "tcop/pquery.h"
2222
#include "utils/ps_status.h"
23+
#include "catalog/pg_shadow.h"
24+
#include "miscadmin.h"
25+
#include "utils/syscache.h"
2326

2427
static char *CreateOperationTag(int operationType);
2528
static void ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset,
@@ -250,6 +253,23 @@ ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset, Node *limcount)
250253
else if (parseTree->into != NULL)
251254
{
252255
/* select into table */
256+
257+
if (!parseTree->isTemp) {
258+
HeapTuple tup;
259+
260+
/* ----------
261+
* Check pg_shadow for global createTable setting
262+
* ----------
263+
*/
264+
tup = SearchSysCacheTuple(SHADOWNAME, PointerGetDatum(GetPgUserName()), 0, 0, 0);
265+
266+
if (!HeapTupleIsValid(tup))
267+
elog(ERROR, "ProcessQueryDesc: look at pg_shadow failed");
268+
269+
if (!((Form_pg_shadow) GETSTRUCT(tup))->usecreatetable)
270+
elog(ERROR, "SELECT INTO TABLE: permission denied");
271+
}
272+
253273
isRetrieveIntoRelation = true;
254274
}
255275

0 commit comments

Comments
 (0)