Thread: grant/revoke bug with delete/update
Hi,
first I'm sorry to not fill the form, I'm too lazy, and it's not platform
nor version dependent AFAIK.
I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
fact that update and insert are considered the same thing when you modify
permissions with grant and revoke. (Maybe it was the wrong place to post
it.)
for example a "grant delete" also grants "update" which is completely
wrong. More importantly the user is not informed, and this could lead to
VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
update existing records, have the permission to delete all records...
I've read postgresql documentation, especially the grant and revoke
manpages, and I've found no mention of this bug, which is IMHO a Big
Mistake (tm).
attached to this message you'll find a patch for version 6.5.2 wich
differentiate delete and update, because before they were considered as
"write". The patch only modifies .c .y and .h files, but no documentation.
the new acl rights look like: arRdu
a for append
r for read
R for rules
d for delete
u for update
instead of: arwR
a for append
r for read
w for update AND delete
R for rules
This patch seems to work at least with what I've tested, you'll find a
test session at the end of this message.
I hope this patch will help and that it will be easy to incorporate it in
7.0, which I haven't the time to do for now.
And for the bug report I posted on Feb 23rd on "drop user" which keeps the
user's acl in the database, and the deleted user id being reused, I've not
done anything, but I consider this a major problem. Please consider it for
a next version.
Because I'm not an expert, I suggest you remove gram.c before applying the
patch, in order for this file to be generated again from gram.y, but maybe
this is not necessary.
I'd be very pleased if some people could test this more than I can,
because I don't use postgresql intensively with special permissions.
I'm not sure for some parts of the patch, especially in execMain.c
so if a postgresql hacker could examine it, this would be fine.
dump of test session:
---------------------
------- CUT -------
template1=> create database db;
CREATEDB
template1=> create user john;
CREATE USER
template1=> \connect db
connecting to new database: db
db=> create table t (id INT4, name TEXT);
CREATE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | |
+----------+--------------------------+
db=> grant all on t to john;
CHANGE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=arduR"} |
+----------+--------------------------+
db=> \connect db john
connecting to new database: db as user: john
db=> insert into t (id, name) values (1, 'xxx');
INSERT 18560 1
db=> update t set name = 'yyy' where id=1;
UPDATE 1
db=> select * from t;
id|name
--+----
1|yyy
(1 row)
db=> delete from t;
DELETE 1
db=> select * from t;
id|name
--+----
(0 rows)
db=> insert into t (id, name) values (1, 'xxx');
INSERT 18561 1
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke update on t from john;
CHANGE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=ardR"} |
+----------+--------------------------+
db=> \connect db john;
connecting to new database: db as user: john
db=> insert into t (id, name) values (2, 'yyy');
INSERT 18592 1
db=> update t set name='modified by john' where id=2;
ERROR: t: Permission denied.
db=> delete from t where id=2;
DELETE 1
db=> select * from t;
id|name
--+----
1|xxx
(1 row)
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke insert on t from john;
CHANGE
db=> \connect db john;
connecting to new database: db as user: john
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=rdR"} |
+----------+--------------------------+
db=> insert into t (id, name) values (3, 'I try to insert something');
ERROR: t: Permission denied.
db=> delete from t;
DELETE 1
db=> select * from t;
id|name
--+----
(0 rows)
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> insert into t (id, name) values (1, 'xxx');
INSERT 18624 1
db=> \connect db john;
connecting to new database: db as user: john
db=> update t set name='john' where id =1;
ERROR: t: Permission denied.
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke delete on t from john;
CHANGE
db=> grant update on t to john;
CHANGE
db=> \connect db john;
connecting to new database: db as user: john
db=> delete from t;
ERROR: t: Permission denied.
db=> update t set name='john' where id=1;
UPDATE 1
db=> select * from t;
id|name
--+----
1|john
(1 row)
------- CUT -------
Thank you for reading.
bye,
Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Looks very nice, but we can't apply it during beta. Only bug fixes, and
this looks a little tricky. We can try it for 7.1. Maybe you can get
us a 7.0 based patch.
> Hi,
>
> first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> nor version dependent AFAIK.
>
> I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> fact that update and insert are considered the same thing when you modify
> permissions with grant and revoke. (Maybe it was the wrong place to post
> it.)
>
> for example a "grant delete" also grants "update" which is completely
> wrong. More importantly the user is not informed, and this could lead to
> VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> update existing records, have the permission to delete all records...
>
> I've read postgresql documentation, especially the grant and revoke
> manpages, and I've found no mention of this bug, which is IMHO a Big
> Mistake (tm).
>
> attached to this message you'll find a patch for version 6.5.2 wich
> differentiate delete and update, because before they were considered as
> "write". The patch only modifies .c .y and .h files, but no documentation.
>
> the new acl rights look like: arRdu
> a for append
> r for read
> R for rules
> d for delete
> u for update
>
> instead of: arwR
> a for append
> r for read
> w for update AND delete
> R for rules
>
> This patch seems to work at least with what I've tested, you'll find a
> test session at the end of this message.
>
> I hope this patch will help and that it will be easy to incorporate it in
> 7.0, which I haven't the time to do for now.
>
> And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> user's acl in the database, and the deleted user id being reused, I've not
> done anything, but I consider this a major problem. Please consider it for
> a next version.
>
> Because I'm not an expert, I suggest you remove gram.c before applying the
> patch, in order for this file to be generated again from gram.y, but maybe
> this is not necessary.
>
> I'd be very pleased if some people could test this more than I can,
> because I don't use postgresql intensively with special permissions.
>
> I'm not sure for some parts of the patch, especially in execMain.c
> so if a postgresql hacker could examine it, this would be fine.
>
> dump of test session:
> ---------------------
>
> ------- CUT -------
>
> template1=> create database db;
> CREATEDB
> template1=> create user john;
> CREATE USER
> template1=> \connect db
> connecting to new database: db
> db=> create table t (id INT4, name TEXT);
> CREATE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | |
> +----------+--------------------------+
> db=> grant all on t to john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=arduR"} |
> +----------+--------------------------+
> db=> \connect db john
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18560 1
> db=> update t set name = 'yyy' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|yyy
> (1 row)
>
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18561 1
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke update on t from john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=ardR"} |
> +----------+--------------------------+
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (2, 'yyy');
> INSERT 18592 1
> db=> update t set name='modified by john' where id=2;
> ERROR: t: Permission denied.
> db=> delete from t where id=2;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> 1|xxx
> (1 row)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke insert on t from john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=rdR"} |
> +----------+--------------------------+
> db=> insert into t (id, name) values (3, 'I try to insert something');
> ERROR: t: Permission denied.
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18624 1
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> update t set name='john' where id =1;
> ERROR: t: Permission denied.
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke delete on t from john;
> CHANGE
> db=> grant update on t to john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> delete from t;
> ERROR: t: Permission denied.
> db=> update t set name='john' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|john
> (1 row)
>
> ------- CUT -------
>
> Thank you for reading.
>
> bye,
>
> Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Content-Description: the 6.5.2 patch
> diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> @@ -381,7 +381,7 @@
> * pg_database table, there is still additional permissions
> * checking in dbcommands.c
> */
> - if ((mode & ACL_WR) || (mode & ACL_AP))
> + if (mode & ACL_AP)
> return ACLCHECK_OK;
> }
>
> @@ -390,7 +390,7 @@
> * pg_shadow.usecatupd is set. (This is to let superusers protect
> * themselves from themselves.)
> */
> - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> + if ((mode & ACL_AP) &&
> !allowSystemTableMods && IsSystemRelationName(relname) &&
> !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> {
> diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> @@ -524,7 +524,9 @@
> if (lockstmt->mode == AccessShareLock)
> aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> else
> - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> + /* do we really need to have all these permissions at the same time ? */
> + /* shouldn't we test lockstmt->mode first ? */
> + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
>
> if (aclresult != ACLCHECK_OK)
> elog(ERROR, "LOCK TABLE: permission denied");
> diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> @@ -242,7 +242,8 @@
> FILE *fp;
> Relation rel;
> extern char *UserName; /* defined in global.c */
> - const AclMode required_access = from ? ACL_WR : ACL_RD;
> + /* why should we need other permissions than APPEND ? */
> + const AclMode required_access = from ? ACL_AP : ACL_RD;
> int result;
>
> rel = heap_openr(relname);
> diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c postgresql-6.5.2-patched/src/backend/commands/sequence.c
> --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> @@ -314,7 +314,8 @@
> Form_pg_sequence seq;
>
> #ifndef NO_SECURITY
> - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE permission ? */
> + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> seqname, seqname);
> #endif
> diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> @@ -115,7 +115,7 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> @@ -227,7 +227,8 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> @@ -329,11 +330,12 @@
> BeginTransactionBlock();
>
> /*
> - * Make sure the user attempting to create a user can delete from the
> + * Make sure the user attempting to delete a user can delete from the
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than DELETE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c postgresql-6.5.2-patched/src/backend/executor/execMain.c
> --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> @@ -464,14 +464,16 @@
> switch (operation)
> {
> case CMD_INSERT:
> - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> opstr = "append";
> break;
> case CMD_DELETE:
> + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> + opstr = "delete";
> + break;
> case CMD_UPDATE:
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> + opstr = "update";
> break;
> default:
> elog(ERROR, "ExecCheckPerms: bogus operation %d",
> @@ -508,8 +510,9 @@
> StrNCpy(rname.data,
> ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> NAMEDATALEN);
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + /* is it the right thing to do ? */
> + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> + opstr = "write"; /* unused ? */
> if (!ok)
> elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> }
> diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> @@ -1694,11 +1694,11 @@
>
> privileges: ALL PRIVILEGES
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | ALL
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | operation_commalist
> {
> @@ -1726,11 +1726,11 @@
> }
> | UPDATE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_UP_CHR;
> }
> | DELETE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_DE_CHR;
> }
> | RULE
> {
> diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> @@ -29,236 +29,236 @@
> RuleStmt *rstmt;
> InsertStmt *astmt;
> } YYSTYPE;
> -#define ABSOLUTE 257
> -#define ACTION 258
> -#define ADD 259
> -#define ALL 260
> -#define ALTER 261
> -#define AND 262
> -#define ANY 263
> -#define AS 264
> -#define ASC 265
> -#define BEGIN_TRANS 266
> -#define BETWEEN 267
> -#define BOTH 268
> -#define BY 269
> -#define CASCADE 270
> -#define CASE 271
> -#define CAST 272
> -#define CHAR 273
> -#define CHARACTER 274
> -#define CHECK 275
> -#define CLOSE 276
> -#define COALESCE 277
> -#define COLLATE 278
> -#define COLUMN 279
> -#define COMMIT 280
> -#define CONSTRAINT 281
> -#define CREATE 282
> -#define CROSS 283
> -#define CURRENT 284
> -#define CURRENT_DATE 285
> -#define CURRENT_TIME 286
> -#define CURRENT_TIMESTAMP 287
> -#define CURRENT_USER 288
> -#define CURSOR 289
> -#define DAY_P 290
> -#define DECIMAL 291
> -#define DECLARE 292
> -#define DEFAULT 293
> -#define DELETE 294
> -#define DESC 295
> -#define DISTINCT 296
> -#define DOUBLE 297
> -#define DROP 298
> -#define ELSE 299
> -#define END_TRANS 300
> -#define EXCEPT 301
> -#define EXECUTE 302
> -#define EXISTS 303
> -#define EXTRACT 304
> -#define FALSE_P 305
> -#define FETCH 306
> -#define FLOAT 307
> -#define FOR 308
> -#define FOREIGN 309
> -#define FROM 310
> -#define FULL 311
> -#define GLOBAL 312
> -#define GRANT 313
> -#define GROUP 314
> -#define HAVING 315
> -#define HOUR_P 316
> -#define IN 317
> -#define INNER_P 318
> -#define INSENSITIVE 319
> -#define INSERT 320
> -#define INTERSECT 321
> -#define INTERVAL 322
> -#define INTO 323
> -#define IS 324
> -#define ISOLATION 325
> -#define JOIN 326
> -#define KEY 327
> -#define LANGUAGE 328
> -#define LEADING 329
> -#define LEFT 330
> -#define LEVEL 331
> -#define LIKE 332
> -#define LOCAL 333
> -#define MATCH 334
> -#define MINUTE_P 335
> -#define MONTH_P 336
> -#define NAMES 337
> -#define NATIONAL 338
> -#define NATURAL 339
> -#define NCHAR 340
> -#define NEXT 341
> -#define NO 342
> -#define NOT 343
> -#define NULLIF 344
> -#define NULL_P 345
> -#define NUMERIC 346
> -#define OF 347
> -#define ON 348
> -#define ONLY 349
> -#define OPTION 350
> -#define OR 351
> -#define ORDER 352
> -#define OUTER_P 353
> -#define PARTIAL 354
> -#define POSITION 355
> -#define PRECISION 356
> -#define PRIMARY 357
> -#define PRIOR 358
> -#define PRIVILEGES 359
> -#define PROCEDURE 360
> -#define PUBLIC 361
> -#define READ 362
> -#define REFERENCES 363
> -#define RELATIVE 364
> -#define REVOKE 365
> -#define RIGHT 366
> -#define ROLLBACK 367
> -#define SCROLL 368
> -#define SECOND_P 369
> -#define SELECT 370
> -#define SET 371
> -#define SUBSTRING 372
> -#define TABLE 373
> -#define TEMP 374
> -#define TEMPORARY 375
> -#define THEN 376
> -#define TIME 377
> -#define TIMESTAMP 378
> -#define TIMEZONE_HOUR 379
> -#define TIMEZONE_MINUTE 380
> -#define TO 381
> -#define TRAILING 382
> -#define TRANSACTION 383
> -#define TRIM 384
> -#define TRUE_P 385
> -#define UNION 386
> -#define UNIQUE 387
> -#define UPDATE 388
> -#define USER 389
> -#define USING 390
> -#define VALUES 391
> -#define VARCHAR 392
> -#define VARYING 393
> -#define VIEW 394
> -#define WHEN 395
> -#define WHERE 396
> -#define WITH 397
> -#define WORK 398
> -#define YEAR_P 399
> -#define ZONE 400
> -#define TRIGGER 401
> -#define COMMITTED 402
> -#define SERIALIZABLE 403
> -#define TYPE_P 404
> -#define ABORT_TRANS 405
> -#define ACCESS 406
> -#define AFTER 407
> -#define AGGREGATE 408
> -#define ANALYZE 409
> -#define BACKWARD 410
> -#define BEFORE 411
> -#define BINARY 412
> -#define CACHE 413
> -#define CLUSTER 414
> -#define COPY 415
> -#define CREATEDB 416
> -#define CREATEUSER 417
> -#define CYCLE 418
> -#define DATABASE 419
> -#define DELIMITERS 420
> -#define DO 421
> -#define EACH 422
> -#define ENCODING 423
> -#define EXCLUSIVE 424
> -#define EXPLAIN 425
> -#define EXTEND 426
> -#define FORWARD 427
> -#define FUNCTION 428
> -#define HANDLER 429
> -#define INCREMENT 430
> -#define INDEX 431
> -#define INHERITS 432
> -#define INSTEAD 433
> -#define ISNULL 434
> -#define LANCOMPILER 435
> -#define LIMIT 436
> -#define LISTEN 437
> -#define LOAD 438
> -#define LOCATION 439
> -#define LOCK_P 440
> -#define MAXVALUE 441
> -#define MINVALUE 442
> -#define MODE 443
> -#define MOVE 444
> -#define NEW 445
> -#define NOCREATEDB 446
> -#define NOCREATEUSER 447
> -#define NONE 448
> -#define NOTHING 449
> -#define NOTIFY 450
> -#define NOTNULL 451
> -#define OFFSET 452
> -#define OIDS 453
> -#define OPERATOR 454
> -#define PASSWORD 455
> -#define PROCEDURAL 456
> -#define RENAME 457
> -#define RESET 458
> -#define RETURNS 459
> -#define ROW 460
> -#define RULE 461
> -#define SEQUENCE 462
> -#define SERIAL 463
> -#define SETOF 464
> -#define SHARE 465
> -#define SHOW 466
> -#define START 467
> -#define STATEMENT 468
> -#define STDIN 469
> -#define STDOUT 470
> -#define TRUSTED 471
> -#define UNLISTEN 472
> -#define UNTIL 473
> -#define VACUUM 474
> -#define VALID 475
> -#define VERBOSE 476
> -#define VERSION 477
> -#define IDENT 478
> -#define SCONST 479
> -#define Op 480
> -#define ICONST 481
> -#define PARAM 482
> -#define FCONST 483
> -#define OP 484
> -#define UMINUS 485
> -#define TYPECAST 486
> +#define ABSOLUTE 258
> +#define ACTION 259
> +#define ADD 260
> +#define ALL 261
> +#define ALTER 262
> +#define AND 263
> +#define ANY 264
> +#define AS 265
> +#define ASC 266
> +#define BEGIN_TRANS 267
> +#define BETWEEN 268
> +#define BOTH 269
> +#define BY 270
> +#define CASCADE 271
> +#define CASE 272
> +#define CAST 273
> +#define CHAR 274
> +#define CHARACTER 275
> +#define CHECK 276
> +#define CLOSE 277
> +#define COALESCE 278
> +#define COLLATE 279
> +#define COLUMN 280
> +#define COMMIT 281
> +#define CONSTRAINT 282
> +#define CREATE 283
> +#define CROSS 284
> +#define CURRENT 285
> +#define CURRENT_DATE 286
> +#define CURRENT_TIME 287
> +#define CURRENT_TIMESTAMP 288
> +#define CURRENT_USER 289
> +#define CURSOR 290
> +#define DAY_P 291
> +#define DECIMAL 292
> +#define DECLARE 293
> +#define DEFAULT 294
> +#define DELETE 295
> +#define DESC 296
> +#define DISTINCT 297
> +#define DOUBLE 298
> +#define DROP 299
> +#define ELSE 300
> +#define END_TRANS 301
> +#define EXCEPT 302
> +#define EXECUTE 303
> +#define EXISTS 304
> +#define EXTRACT 305
> +#define FALSE_P 306
> +#define FETCH 307
> +#define FLOAT 308
> +#define FOR 309
> +#define FOREIGN 310
> +#define FROM 311
> +#define FULL 312
> +#define GLOBAL 313
> +#define GRANT 314
> +#define GROUP 315
> +#define HAVING 316
> +#define HOUR_P 317
> +#define IN 318
> +#define INNER_P 319
> +#define INSENSITIVE 320
> +#define INSERT 321
> +#define INTERSECT 322
> +#define INTERVAL 323
> +#define INTO 324
> +#define IS 325
> +#define ISOLATION 326
> +#define JOIN 327
> +#define KEY 328
> +#define LANGUAGE 329
> +#define LEADING 330
> +#define LEFT 331
> +#define LEVEL 332
> +#define LIKE 333
> +#define LOCAL 334
> +#define MATCH 335
> +#define MINUTE_P 336
> +#define MONTH_P 337
> +#define NAMES 338
> +#define NATIONAL 339
> +#define NATURAL 340
> +#define NCHAR 341
> +#define NEXT 342
> +#define NO 343
> +#define NOT 344
> +#define NULLIF 345
> +#define NULL_P 346
> +#define NUMERIC 347
> +#define OF 348
> +#define ON 349
> +#define ONLY 350
> +#define OPTION 351
> +#define OR 352
> +#define ORDER 353
> +#define OUTER_P 354
> +#define PARTIAL 355
> +#define POSITION 356
> +#define PRECISION 357
> +#define PRIMARY 358
> +#define PRIOR 359
> +#define PRIVILEGES 360
> +#define PROCEDURE 361
> +#define PUBLIC 362
> +#define READ 363
> +#define REFERENCES 364
> +#define RELATIVE 365
> +#define REVOKE 366
> +#define RIGHT 367
> +#define ROLLBACK 368
> +#define SCROLL 369
> +#define SECOND_P 370
> +#define SELECT 371
> +#define SET 372
> +#define SUBSTRING 373
> +#define TABLE 374
> +#define TEMP 375
> +#define TEMPORARY 376
> +#define THEN 377
> +#define TIME 378
> +#define TIMESTAMP 379
> +#define TIMEZONE_HOUR 380
> +#define TIMEZONE_MINUTE 381
> +#define TO 382
> +#define TRAILING 383
> +#define TRANSACTION 384
> +#define TRIM 385
> +#define TRUE_P 386
> +#define UNION 387
> +#define UNIQUE 388
> +#define UPDATE 389
> +#define USER 390
> +#define USING 391
> +#define VALUES 392
> +#define VARCHAR 393
> +#define VARYING 394
> +#define VIEW 395
> +#define WHEN 396
> +#define WHERE 397
> +#define WITH 398
> +#define WORK 399
> +#define YEAR_P 400
> +#define ZONE 401
> +#define TRIGGER 402
> +#define COMMITTED 403
> +#define SERIALIZABLE 404
> +#define TYPE_P 405
> +#define ABORT_TRANS 406
> +#define ACCESS 407
> +#define AFTER 408
> +#define AGGREGATE 409
> +#define ANALYZE 410
> +#define BACKWARD 411
> +#define BEFORE 412
> +#define BINARY 413
> +#define CACHE 414
> +#define CLUSTER 415
> +#define COPY 416
> +#define CREATEDB 417
> +#define CREATEUSER 418
> +#define CYCLE 419
> +#define DATABASE 420
> +#define DELIMITERS 421
> +#define DO 422
> +#define EACH 423
> +#define ENCODING 424
> +#define EXCLUSIVE 425
> +#define EXPLAIN 426
> +#define EXTEND 427
> +#define FORWARD 428
> +#define FUNCTION 429
> +#define HANDLER 430
> +#define INCREMENT 431
> +#define INDEX 432
> +#define INHERITS 433
> +#define INSTEAD 434
> +#define ISNULL 435
> +#define LANCOMPILER 436
> +#define LIMIT 437
> +#define LISTEN 438
> +#define LOAD 439
> +#define LOCATION 440
> +#define LOCK_P 441
> +#define MAXVALUE 442
> +#define MINVALUE 443
> +#define MODE 444
> +#define MOVE 445
> +#define NEW 446
> +#define NOCREATEDB 447
> +#define NOCREATEUSER 448
> +#define NONE 449
> +#define NOTHING 450
> +#define NOTIFY 451
> +#define NOTNULL 452
> +#define OFFSET 453
> +#define OIDS 454
> +#define OPERATOR 455
> +#define PASSWORD 456
> +#define PROCEDURAL 457
> +#define RENAME 458
> +#define RESET 459
> +#define RETURNS 460
> +#define ROW 461
> +#define RULE 462
> +#define SEQUENCE 463
> +#define SERIAL 464
> +#define SETOF 465
> +#define SHARE 466
> +#define SHOW 467
> +#define START 468
> +#define STATEMENT 469
> +#define STDIN 470
> +#define STDOUT 471
> +#define TRUSTED 472
> +#define UNLISTEN 473
> +#define UNTIL 474
> +#define VACUUM 475
> +#define VALID 476
> +#define VERBOSE 477
> +#define VERSION 478
> +#define IDENT 479
> +#define SCONST 480
> +#define Op 481
> +#define ICONST 482
> +#define PARAM 483
> +#define FCONST 484
> +#define OP 485
> +#define UMINUS 486
> +#define TYPECAST 487
>
>
> extern YYSTYPE yylval;
> diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> @@ -601,7 +601,8 @@
>
> if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> - ACL_WR : ACL_RD)))
> + /* if nextval and setval are atomic, which I don't know, update should be enough */
> + ACL_UP : ACL_RD)))
> != ACLCHECK_OK)
> elog(ERROR, "%s.%s: %s",
> seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> @@ -228,8 +228,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
> else
> diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> @@ -2282,8 +2282,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
>
> diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> @@ -154,8 +154,11 @@
> case ACL_MODE_RD_CHR:
> aip->ai_mode |= ACL_RD;
> break;
> - case ACL_MODE_WR_CHR:
> - aip->ai_mode |= ACL_WR;
> + case ACL_MODE_DE_CHR:
> + aip->ai_mode |= ACL_DE;
> + break;
> + case ACL_MODE_UP_CHR:
> + aip->ai_mode |= ACL_UP;
> break;
> case ACL_MODE_RU_CHR:
> aip->ai_mode |= ACL_RU;
> @@ -272,7 +275,7 @@
> if (!aip)
> aip = &default_aclitem;
>
> - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> if (!out)
> elog(ERROR, "aclitemout: palloc failed");
> *p = '\0';
> @@ -605,9 +608,8 @@
> int i;
> int l;
>
> - Assert(strlen(old_privlist) < 5);
> - priv = palloc(5); /* at most "rwaR" */ ;
> -
> + Assert(strlen(old_privlist) < 6);
> + priv = palloc(6); /* at most "arduR" */ ;
> if (old_privlist == NULL || old_privlist[0] == '\0')
> {
> priv[0] = new_priv;
> @@ -619,7 +621,7 @@
>
> l = strlen(old_privlist);
>
> - if (l == 4)
> + if (l == 5)
> { /* can't add any more privileges */
> return priv;
> }
> diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> @@ -54,9 +54,10 @@
> #define ACL_NO 0 /* no permissions */
> #define ACL_AP (1<<0) /* append */
> #define ACL_RD (1<<1) /* read */
> -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> -#define ACL_RU (1<<3) /* place rules */
> -#define N_ACL_MODES 4
> +#define ACL_DE (1<<2) /* delete */
> +#define ACL_UP (1<<3) /* update/replace */
> +#define ACL_RU (1<<4) /* place rules */
> +#define N_ACL_MODES 5
>
> #define ACL_MODECHG_ADD 1
> #define ACL_MODECHG_DEL 2
> @@ -65,7 +66,8 @@
> /* change this line if you want to set the default acl permission */
> #define ACL_WORLD_DEFAULT (ACL_NO)
> /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> +
> +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
>
> /*
> * AclItem
> @@ -118,10 +120,12 @@
> #define ACL_MODECHG_ADD_CHR '+'
> #define ACL_MODECHG_DEL_CHR '-'
> #define ACL_MODECHG_EQL_CHR '='
> -#define ACL_MODE_STR "arwR" /* list of valid characters */
> +
> +#define ACL_MODE_STR "arduR" /* list of valid characters */
> #define ACL_MODE_AP_CHR 'a'
> #define ACL_MODE_RD_CHR 'r'
> -#define ACL_MODE_WR_CHR 'w'
> +#define ACL_MODE_DE_CHR 'd'
> +#define ACL_MODE_UP_CHR 'u'
> #define ACL_MODE_RU_CHR 'R'
>
> /* result codes for pg_aclcheck */
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes: > Looks very nice, but we can't apply it during beta. Only bug fixes, and > this looks a little tricky. We can try it for 7.1. Maybe you can get > us a 7.0 based patch. It was me that encouraged him to send in this patch now because Karel and I are currently talking about redoing the ACL stuff for 7.1. I considered this a bug and the fix looks pretty straightforward. Perhaps it should go into 7.0.1? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter, thanks for your support ! I'm surprised this bug isn't taken seriously by other people. about the fact that this isn't considered as a bug fix, I disagree entirely: it's a fix to an important security issue. It adds nothing. The only thing it changes is "du" instead of "w" in the acls, so people would have to dump and restore their databases when upgrading to a fixed version, but that's probably already the case for upgrading from 6.5x to 7.x (I don't know). Of course I agree that this fix needs a lot more testing than most bug fixes, and I haven't tested all the possibilities (particularly with sequences, which I have not tested at all). I'm even more surprised this wasn't noticed before, or do all users deal with databases as superuser ? For those of you who have any doubt, I suggest you look at a recent thread on BUGTRAQ (find it on http://www.securityfocus.com) to know what problems this bug can generate if used by bad people. I've even received a mail trying to explain me that update and delete are the same thing because you can update a record you want to delete but have no right to, to change its data... of course this is possible, but nevertheless the record isn't deleted, so update and delete really are two different things, not to mention you may want to give delete permission but not insert nor update. As I told previously in private to Bruce, I won't be able to make this patch for 7.0 until a week or two, so if someone do it before (please do, because you better know postgresql code than me, so you'll make less mistakes), just tell me because I don't really want to duplicate the effort. bye, PS: could someone explain me what "tricky" means ? Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE On Sat, 4 Mar 2000, Peter Eisentraut wrote: > Bruce Momjian writes: > > > Looks very nice, but we can't apply it during beta. Only bug fixes, and > > this looks a little tricky. We can try it for 7.1. Maybe you can get > > us a 7.0 based patch. > > It was me that encouraged him to send in this patch now because Karel and > I are currently talking about redoing the ACL stuff for 7.1. > > I considered this a bug and the fix looks pretty straightforward. Perhaps > it should go into 7.0.1? > > -- > Peter Eisentraut Sernanders väg 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden >
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> Looks very nice, but we can't apply it during beta. Only bug fixes, and
>> this looks a little tricky. We can try it for 7.1. Maybe you can get
>> us a 7.0 based patch.
> It was me that encouraged him to send in this patch now because Karel and
> I are currently talking about redoing the ACL stuff for 7.1.
> I considered this a bug and the fix looks pretty straightforward. Perhaps
> it should go into 7.0.1?
It looked to me like a definition change that hadn't been adequately
discussed. We tend to be especially leery of those during beta;
rushing in a "bug fix" that may prove to have been a bad idea is
not productive.
regards, tom lane
On Tue, 7 Mar 2000, Tom Lane wrote: > It looked to me like a definition change that hadn't been adequately > discussed. We tend to be especially leery of those during beta; > rushing in a "bug fix" that may prove to have been a bad idea is > not productive. ok, but what are you planning to do and when to correct this security issue ? I agree it's not a complete rewrite of acls in postgresql, which maybe (I don't know) need to be rewritten from scratch, because I'm really not able to do this. However saying that a quick fix to correct a major security problem is a bad idea makes me laugh loudly (or cry, if you prefer). for now and until someone acts correctly regarding this problem, I'll patch my good old 6.5.2 version and use it, and you can throw my patch in your ass or wherever you prefer if you don't want it. Don't even expect me to rewrite this patch for 7.0, because it's not my problem anymore, it's yours (and other postgresql users') ! I really don't mind you don't include my patch in postgresql, what I'm concerned about is that you don't plan anything to quickly solve this problem. Maybe you don't know, which would surprise me, but some people write programs which rely on acls and other SQL features working correctly. At least you should document this security problem. Don't try to tell me to use another product, because unfortunately for you I really like postgresql. thank you for reading. Peter: thanks again for your support. bye, Jerome
[Charset ISO-8859-1 unsupported, filtering to ASCII...] > Bruce Momjian writes: > > > Looks very nice, but we can't apply it during beta. Only bug fixes, and > > this looks a little tricky. We can try it for 7.1. Maybe you can get > > us a 7.0 based patch. > > It was me that encouraged him to send in this patch now because Karel and > I are currently talking about redoing the ACL stuff for 7.1. > > I considered this a bug and the fix looks pretty straightforward. Perhaps > it should go into 7.0.1? It will never make it into 7.0.1. Beta is your only chance, and I don't think it is do-able. It will take a few weeks to get a 7.0 based patch, and I don't see the reason to add a feature at this time. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> I've even received a mail trying to explain me that update and delete are > the same thing because you can update a record you want to delete but have > no right to, to change its data... of course this is possible, but > nevertheless the record isn't deleted, so update and delete really are two > different things, not to mention you may want to give delete permission > but not insert nor update. > > As I told previously in private to Bruce, I won't be able to make this > patch for 7.0 until a week or two, so if someone do it before (please do, > because you better know postgresql code than me, so you'll make less > mistakes), just tell me because I don't really want to duplicate the > effort. > > bye, > > PS: could someone explain me what "tricky" means ? Tricky means not based on 7.0, and it mucks with the internals, and that it may require an initdb. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Jerome ALET writes: [censored stuff snipped] > Don't even expect me to rewrite this patch for 7.0, because it's not my > problem anymore, it's yours (and other postgresql users') ! I don't think that personal attacks like this are warranted. Tom points out that this is a relatively big code change by an "outsider" during beta which would also require users to re-initdb their database. It's an unfortunate situation but if a plurality of core developers says that this would be too much potential burden during beta you have to accept it. You are free to publish your patch via other mechanisms if you like, or contribute it to 7.1. And patches based on older versions can't be used in general. > At least you should document this security problem. Agreed. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Are we addressing this?
> Hi,
>
> first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> nor version dependent AFAIK.
>
> I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> fact that update and insert are considered the same thing when you modify
> permissions with grant and revoke. (Maybe it was the wrong place to post
> it.)
>
> for example a "grant delete" also grants "update" which is completely
> wrong. More importantly the user is not informed, and this could lead to
> VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> update existing records, have the permission to delete all records...
>
> I've read postgresql documentation, especially the grant and revoke
> manpages, and I've found no mention of this bug, which is IMHO a Big
> Mistake (tm).
>
> attached to this message you'll find a patch for version 6.5.2 wich
> differentiate delete and update, because before they were considered as
> "write". The patch only modifies .c .y and .h files, but no documentation.
>
> the new acl rights look like: arRdu
> a for append
> r for read
> R for rules
> d for delete
> u for update
>
> instead of: arwR
> a for append
> r for read
> w for update AND delete
> R for rules
>
> This patch seems to work at least with what I've tested, you'll find a
> test session at the end of this message.
>
> I hope this patch will help and that it will be easy to incorporate it in
> 7.0, which I haven't the time to do for now.
>
> And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> user's acl in the database, and the deleted user id being reused, I've not
> done anything, but I consider this a major problem. Please consider it for
> a next version.
>
> Because I'm not an expert, I suggest you remove gram.c before applying the
> patch, in order for this file to be generated again from gram.y, but maybe
> this is not necessary.
>
> I'd be very pleased if some people could test this more than I can,
> because I don't use postgresql intensively with special permissions.
>
> I'm not sure for some parts of the patch, especially in execMain.c
> so if a postgresql hacker could examine it, this would be fine.
>
> dump of test session:
> ---------------------
>
> ------- CUT -------
>
> template1=> create database db;
> CREATEDB
> template1=> create user john;
> CREATE USER
> template1=> \connect db
> connecting to new database: db
> db=> create table t (id INT4, name TEXT);
> CREATE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | |
> +----------+--------------------------+
> db=> grant all on t to john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=arduR"} |
> +----------+--------------------------+
> db=> \connect db john
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18560 1
> db=> update t set name = 'yyy' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|yyy
> (1 row)
>
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18561 1
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke update on t from john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=ardR"} |
> +----------+--------------------------+
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (2, 'yyy');
> INSERT 18592 1
> db=> update t set name='modified by john' where id=2;
> ERROR: t: Permission denied.
> db=> delete from t where id=2;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> 1|xxx
> (1 row)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke insert on t from john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=rdR"} |
> +----------+--------------------------+
> db=> insert into t (id, name) values (3, 'I try to insert something');
> ERROR: t: Permission denied.
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18624 1
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> update t set name='john' where id =1;
> ERROR: t: Permission denied.
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke delete on t from john;
> CHANGE
> db=> grant update on t to john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> delete from t;
> ERROR: t: Permission denied.
> db=> update t set name='john' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|john
> (1 row)
>
> ------- CUT -------
>
> Thank you for reading.
>
> bye,
>
> Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Content-Description: the 6.5.2 patch
> diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> @@ -381,7 +381,7 @@
> * pg_database table, there is still additional permissions
> * checking in dbcommands.c
> */
> - if ((mode & ACL_WR) || (mode & ACL_AP))
> + if (mode & ACL_AP)
> return ACLCHECK_OK;
> }
>
> @@ -390,7 +390,7 @@
> * pg_shadow.usecatupd is set. (This is to let superusers protect
> * themselves from themselves.)
> */
> - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> + if ((mode & ACL_AP) &&
> !allowSystemTableMods && IsSystemRelationName(relname) &&
> !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> {
> diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> @@ -524,7 +524,9 @@
> if (lockstmt->mode == AccessShareLock)
> aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> else
> - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> + /* do we really need to have all these permissions at the same time ? */
> + /* shouldn't we test lockstmt->mode first ? */
> + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
>
> if (aclresult != ACLCHECK_OK)
> elog(ERROR, "LOCK TABLE: permission denied");
> diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> @@ -242,7 +242,8 @@
> FILE *fp;
> Relation rel;
> extern char *UserName; /* defined in global.c */
> - const AclMode required_access = from ? ACL_WR : ACL_RD;
> + /* why should we need other permissions than APPEND ? */
> + const AclMode required_access = from ? ACL_AP : ACL_RD;
> int result;
>
> rel = heap_openr(relname);
> diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c postgresql-6.5.2-patched/src/backend/commands/sequence.c
> --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> @@ -314,7 +314,8 @@
> Form_pg_sequence seq;
>
> #ifndef NO_SECURITY
> - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE permission ? */
> + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> seqname, seqname);
> #endif
> diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> @@ -115,7 +115,7 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> @@ -227,7 +227,8 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> @@ -329,11 +330,12 @@
> BeginTransactionBlock();
>
> /*
> - * Make sure the user attempting to create a user can delete from the
> + * Make sure the user attempting to delete a user can delete from the
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than DELETE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c postgresql-6.5.2-patched/src/backend/executor/execMain.c
> --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> @@ -464,14 +464,16 @@
> switch (operation)
> {
> case CMD_INSERT:
> - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> opstr = "append";
> break;
> case CMD_DELETE:
> + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> + opstr = "delete";
> + break;
> case CMD_UPDATE:
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> + opstr = "update";
> break;
> default:
> elog(ERROR, "ExecCheckPerms: bogus operation %d",
> @@ -508,8 +510,9 @@
> StrNCpy(rname.data,
> ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> NAMEDATALEN);
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + /* is it the right thing to do ? */
> + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> + opstr = "write"; /* unused ? */
> if (!ok)
> elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> }
> diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> @@ -1694,11 +1694,11 @@
>
> privileges: ALL PRIVILEGES
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | ALL
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | operation_commalist
> {
> @@ -1726,11 +1726,11 @@
> }
> | UPDATE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_UP_CHR;
> }
> | DELETE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_DE_CHR;
> }
> | RULE
> {
> diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> @@ -29,236 +29,236 @@
> RuleStmt *rstmt;
> InsertStmt *astmt;
> } YYSTYPE;
> -#define ABSOLUTE 257
> -#define ACTION 258
> -#define ADD 259
> -#define ALL 260
> -#define ALTER 261
> -#define AND 262
> -#define ANY 263
> -#define AS 264
> -#define ASC 265
> -#define BEGIN_TRANS 266
> -#define BETWEEN 267
> -#define BOTH 268
> -#define BY 269
> -#define CASCADE 270
> -#define CASE 271
> -#define CAST 272
> -#define CHAR 273
> -#define CHARACTER 274
> -#define CHECK 275
> -#define CLOSE 276
> -#define COALESCE 277
> -#define COLLATE 278
> -#define COLUMN 279
> -#define COMMIT 280
> -#define CONSTRAINT 281
> -#define CREATE 282
> -#define CROSS 283
> -#define CURRENT 284
> -#define CURRENT_DATE 285
> -#define CURRENT_TIME 286
> -#define CURRENT_TIMESTAMP 287
> -#define CURRENT_USER 288
> -#define CURSOR 289
> -#define DAY_P 290
> -#define DECIMAL 291
> -#define DECLARE 292
> -#define DEFAULT 293
> -#define DELETE 294
> -#define DESC 295
> -#define DISTINCT 296
> -#define DOUBLE 297
> -#define DROP 298
> -#define ELSE 299
> -#define END_TRANS 300
> -#define EXCEPT 301
> -#define EXECUTE 302
> -#define EXISTS 303
> -#define EXTRACT 304
> -#define FALSE_P 305
> -#define FETCH 306
> -#define FLOAT 307
> -#define FOR 308
> -#define FOREIGN 309
> -#define FROM 310
> -#define FULL 311
> -#define GLOBAL 312
> -#define GRANT 313
> -#define GROUP 314
> -#define HAVING 315
> -#define HOUR_P 316
> -#define IN 317
> -#define INNER_P 318
> -#define INSENSITIVE 319
> -#define INSERT 320
> -#define INTERSECT 321
> -#define INTERVAL 322
> -#define INTO 323
> -#define IS 324
> -#define ISOLATION 325
> -#define JOIN 326
> -#define KEY 327
> -#define LANGUAGE 328
> -#define LEADING 329
> -#define LEFT 330
> -#define LEVEL 331
> -#define LIKE 332
> -#define LOCAL 333
> -#define MATCH 334
> -#define MINUTE_P 335
> -#define MONTH_P 336
> -#define NAMES 337
> -#define NATIONAL 338
> -#define NATURAL 339
> -#define NCHAR 340
> -#define NEXT 341
> -#define NO 342
> -#define NOT 343
> -#define NULLIF 344
> -#define NULL_P 345
> -#define NUMERIC 346
> -#define OF 347
> -#define ON 348
> -#define ONLY 349
> -#define OPTION 350
> -#define OR 351
> -#define ORDER 352
> -#define OUTER_P 353
> -#define PARTIAL 354
> -#define POSITION 355
> -#define PRECISION 356
> -#define PRIMARY 357
> -#define PRIOR 358
> -#define PRIVILEGES 359
> -#define PROCEDURE 360
> -#define PUBLIC 361
> -#define READ 362
> -#define REFERENCES 363
> -#define RELATIVE 364
> -#define REVOKE 365
> -#define RIGHT 366
> -#define ROLLBACK 367
> -#define SCROLL 368
> -#define SECOND_P 369
> -#define SELECT 370
> -#define SET 371
> -#define SUBSTRING 372
> -#define TABLE 373
> -#define TEMP 374
> -#define TEMPORARY 375
> -#define THEN 376
> -#define TIME 377
> -#define TIMESTAMP 378
> -#define TIMEZONE_HOUR 379
> -#define TIMEZONE_MINUTE 380
> -#define TO 381
> -#define TRAILING 382
> -#define TRANSACTION 383
> -#define TRIM 384
> -#define TRUE_P 385
> -#define UNION 386
> -#define UNIQUE 387
> -#define UPDATE 388
> -#define USER 389
> -#define USING 390
> -#define VALUES 391
> -#define VARCHAR 392
> -#define VARYING 393
> -#define VIEW 394
> -#define WHEN 395
> -#define WHERE 396
> -#define WITH 397
> -#define WORK 398
> -#define YEAR_P 399
> -#define ZONE 400
> -#define TRIGGER 401
> -#define COMMITTED 402
> -#define SERIALIZABLE 403
> -#define TYPE_P 404
> -#define ABORT_TRANS 405
> -#define ACCESS 406
> -#define AFTER 407
> -#define AGGREGATE 408
> -#define ANALYZE 409
> -#define BACKWARD 410
> -#define BEFORE 411
> -#define BINARY 412
> -#define CACHE 413
> -#define CLUSTER 414
> -#define COPY 415
> -#define CREATEDB 416
> -#define CREATEUSER 417
> -#define CYCLE 418
> -#define DATABASE 419
> -#define DELIMITERS 420
> -#define DO 421
> -#define EACH 422
> -#define ENCODING 423
> -#define EXCLUSIVE 424
> -#define EXPLAIN 425
> -#define EXTEND 426
> -#define FORWARD 427
> -#define FUNCTION 428
> -#define HANDLER 429
> -#define INCREMENT 430
> -#define INDEX 431
> -#define INHERITS 432
> -#define INSTEAD 433
> -#define ISNULL 434
> -#define LANCOMPILER 435
> -#define LIMIT 436
> -#define LISTEN 437
> -#define LOAD 438
> -#define LOCATION 439
> -#define LOCK_P 440
> -#define MAXVALUE 441
> -#define MINVALUE 442
> -#define MODE 443
> -#define MOVE 444
> -#define NEW 445
> -#define NOCREATEDB 446
> -#define NOCREATEUSER 447
> -#define NONE 448
> -#define NOTHING 449
> -#define NOTIFY 450
> -#define NOTNULL 451
> -#define OFFSET 452
> -#define OIDS 453
> -#define OPERATOR 454
> -#define PASSWORD 455
> -#define PROCEDURAL 456
> -#define RENAME 457
> -#define RESET 458
> -#define RETURNS 459
> -#define ROW 460
> -#define RULE 461
> -#define SEQUENCE 462
> -#define SERIAL 463
> -#define SETOF 464
> -#define SHARE 465
> -#define SHOW 466
> -#define START 467
> -#define STATEMENT 468
> -#define STDIN 469
> -#define STDOUT 470
> -#define TRUSTED 471
> -#define UNLISTEN 472
> -#define UNTIL 473
> -#define VACUUM 474
> -#define VALID 475
> -#define VERBOSE 476
> -#define VERSION 477
> -#define IDENT 478
> -#define SCONST 479
> -#define Op 480
> -#define ICONST 481
> -#define PARAM 482
> -#define FCONST 483
> -#define OP 484
> -#define UMINUS 485
> -#define TYPECAST 486
> +#define ABSOLUTE 258
> +#define ACTION 259
> +#define ADD 260
> +#define ALL 261
> +#define ALTER 262
> +#define AND 263
> +#define ANY 264
> +#define AS 265
> +#define ASC 266
> +#define BEGIN_TRANS 267
> +#define BETWEEN 268
> +#define BOTH 269
> +#define BY 270
> +#define CASCADE 271
> +#define CASE 272
> +#define CAST 273
> +#define CHAR 274
> +#define CHARACTER 275
> +#define CHECK 276
> +#define CLOSE 277
> +#define COALESCE 278
> +#define COLLATE 279
> +#define COLUMN 280
> +#define COMMIT 281
> +#define CONSTRAINT 282
> +#define CREATE 283
> +#define CROSS 284
> +#define CURRENT 285
> +#define CURRENT_DATE 286
> +#define CURRENT_TIME 287
> +#define CURRENT_TIMESTAMP 288
> +#define CURRENT_USER 289
> +#define CURSOR 290
> +#define DAY_P 291
> +#define DECIMAL 292
> +#define DECLARE 293
> +#define DEFAULT 294
> +#define DELETE 295
> +#define DESC 296
> +#define DISTINCT 297
> +#define DOUBLE 298
> +#define DROP 299
> +#define ELSE 300
> +#define END_TRANS 301
> +#define EXCEPT 302
> +#define EXECUTE 303
> +#define EXISTS 304
> +#define EXTRACT 305
> +#define FALSE_P 306
> +#define FETCH 307
> +#define FLOAT 308
> +#define FOR 309
> +#define FOREIGN 310
> +#define FROM 311
> +#define FULL 312
> +#define GLOBAL 313
> +#define GRANT 314
> +#define GROUP 315
> +#define HAVING 316
> +#define HOUR_P 317
> +#define IN 318
> +#define INNER_P 319
> +#define INSENSITIVE 320
> +#define INSERT 321
> +#define INTERSECT 322
> +#define INTERVAL 323
> +#define INTO 324
> +#define IS 325
> +#define ISOLATION 326
> +#define JOIN 327
> +#define KEY 328
> +#define LANGUAGE 329
> +#define LEADING 330
> +#define LEFT 331
> +#define LEVEL 332
> +#define LIKE 333
> +#define LOCAL 334
> +#define MATCH 335
> +#define MINUTE_P 336
> +#define MONTH_P 337
> +#define NAMES 338
> +#define NATIONAL 339
> +#define NATURAL 340
> +#define NCHAR 341
> +#define NEXT 342
> +#define NO 343
> +#define NOT 344
> +#define NULLIF 345
> +#define NULL_P 346
> +#define NUMERIC 347
> +#define OF 348
> +#define ON 349
> +#define ONLY 350
> +#define OPTION 351
> +#define OR 352
> +#define ORDER 353
> +#define OUTER_P 354
> +#define PARTIAL 355
> +#define POSITION 356
> +#define PRECISION 357
> +#define PRIMARY 358
> +#define PRIOR 359
> +#define PRIVILEGES 360
> +#define PROCEDURE 361
> +#define PUBLIC 362
> +#define READ 363
> +#define REFERENCES 364
> +#define RELATIVE 365
> +#define REVOKE 366
> +#define RIGHT 367
> +#define ROLLBACK 368
> +#define SCROLL 369
> +#define SECOND_P 370
> +#define SELECT 371
> +#define SET 372
> +#define SUBSTRING 373
> +#define TABLE 374
> +#define TEMP 375
> +#define TEMPORARY 376
> +#define THEN 377
> +#define TIME 378
> +#define TIMESTAMP 379
> +#define TIMEZONE_HOUR 380
> +#define TIMEZONE_MINUTE 381
> +#define TO 382
> +#define TRAILING 383
> +#define TRANSACTION 384
> +#define TRIM 385
> +#define TRUE_P 386
> +#define UNION 387
> +#define UNIQUE 388
> +#define UPDATE 389
> +#define USER 390
> +#define USING 391
> +#define VALUES 392
> +#define VARCHAR 393
> +#define VARYING 394
> +#define VIEW 395
> +#define WHEN 396
> +#define WHERE 397
> +#define WITH 398
> +#define WORK 399
> +#define YEAR_P 400
> +#define ZONE 401
> +#define TRIGGER 402
> +#define COMMITTED 403
> +#define SERIALIZABLE 404
> +#define TYPE_P 405
> +#define ABORT_TRANS 406
> +#define ACCESS 407
> +#define AFTER 408
> +#define AGGREGATE 409
> +#define ANALYZE 410
> +#define BACKWARD 411
> +#define BEFORE 412
> +#define BINARY 413
> +#define CACHE 414
> +#define CLUSTER 415
> +#define COPY 416
> +#define CREATEDB 417
> +#define CREATEUSER 418
> +#define CYCLE 419
> +#define DATABASE 420
> +#define DELIMITERS 421
> +#define DO 422
> +#define EACH 423
> +#define ENCODING 424
> +#define EXCLUSIVE 425
> +#define EXPLAIN 426
> +#define EXTEND 427
> +#define FORWARD 428
> +#define FUNCTION 429
> +#define HANDLER 430
> +#define INCREMENT 431
> +#define INDEX 432
> +#define INHERITS 433
> +#define INSTEAD 434
> +#define ISNULL 435
> +#define LANCOMPILER 436
> +#define LIMIT 437
> +#define LISTEN 438
> +#define LOAD 439
> +#define LOCATION 440
> +#define LOCK_P 441
> +#define MAXVALUE 442
> +#define MINVALUE 443
> +#define MODE 444
> +#define MOVE 445
> +#define NEW 446
> +#define NOCREATEDB 447
> +#define NOCREATEUSER 448
> +#define NONE 449
> +#define NOTHING 450
> +#define NOTIFY 451
> +#define NOTNULL 452
> +#define OFFSET 453
> +#define OIDS 454
> +#define OPERATOR 455
> +#define PASSWORD 456
> +#define PROCEDURAL 457
> +#define RENAME 458
> +#define RESET 459
> +#define RETURNS 460
> +#define ROW 461
> +#define RULE 462
> +#define SEQUENCE 463
> +#define SERIAL 464
> +#define SETOF 465
> +#define SHARE 466
> +#define SHOW 467
> +#define START 468
> +#define STATEMENT 469
> +#define STDIN 470
> +#define STDOUT 471
> +#define TRUSTED 472
> +#define UNLISTEN 473
> +#define UNTIL 474
> +#define VACUUM 475
> +#define VALID 476
> +#define VERBOSE 477
> +#define VERSION 478
> +#define IDENT 479
> +#define SCONST 480
> +#define Op 481
> +#define ICONST 482
> +#define PARAM 483
> +#define FCONST 484
> +#define OP 485
> +#define UMINUS 486
> +#define TYPECAST 487
>
>
> extern YYSTYPE yylval;
> diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> @@ -601,7 +601,8 @@
>
> if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> - ACL_WR : ACL_RD)))
> + /* if nextval and setval are atomic, which I don't know, update should be enough */
> + ACL_UP : ACL_RD)))
> != ACLCHECK_OK)
> elog(ERROR, "%s.%s: %s",
> seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> @@ -228,8 +228,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
> else
> diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> @@ -2282,8 +2282,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
>
> diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> @@ -154,8 +154,11 @@
> case ACL_MODE_RD_CHR:
> aip->ai_mode |= ACL_RD;
> break;
> - case ACL_MODE_WR_CHR:
> - aip->ai_mode |= ACL_WR;
> + case ACL_MODE_DE_CHR:
> + aip->ai_mode |= ACL_DE;
> + break;
> + case ACL_MODE_UP_CHR:
> + aip->ai_mode |= ACL_UP;
> break;
> case ACL_MODE_RU_CHR:
> aip->ai_mode |= ACL_RU;
> @@ -272,7 +275,7 @@
> if (!aip)
> aip = &default_aclitem;
>
> - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> if (!out)
> elog(ERROR, "aclitemout: palloc failed");
> *p = '\0';
> @@ -605,9 +608,8 @@
> int i;
> int l;
>
> - Assert(strlen(old_privlist) < 5);
> - priv = palloc(5); /* at most "rwaR" */ ;
> -
> + Assert(strlen(old_privlist) < 6);
> + priv = palloc(6); /* at most "arduR" */ ;
> if (old_privlist == NULL || old_privlist[0] == '\0')
> {
> priv[0] = new_priv;
> @@ -619,7 +621,7 @@
>
> l = strlen(old_privlist);
>
> - if (l == 4)
> + if (l == 5)
> { /* can't add any more privileges */
> return priv;
> }
> diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> @@ -54,9 +54,10 @@
> #define ACL_NO 0 /* no permissions */
> #define ACL_AP (1<<0) /* append */
> #define ACL_RD (1<<1) /* read */
> -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> -#define ACL_RU (1<<3) /* place rules */
> -#define N_ACL_MODES 4
> +#define ACL_DE (1<<2) /* delete */
> +#define ACL_UP (1<<3) /* update/replace */
> +#define ACL_RU (1<<4) /* place rules */
> +#define N_ACL_MODES 5
>
> #define ACL_MODECHG_ADD 1
> #define ACL_MODECHG_DEL 2
> @@ -65,7 +66,8 @@
> /* change this line if you want to set the default acl permission */
> #define ACL_WORLD_DEFAULT (ACL_NO)
> /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> +
> +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
>
> /*
> * AclItem
> @@ -118,10 +120,12 @@
> #define ACL_MODECHG_ADD_CHR '+'
> #define ACL_MODECHG_DEL_CHR '-'
> #define ACL_MODECHG_EQL_CHR '='
> -#define ACL_MODE_STR "arwR" /* list of valid characters */
> +
> +#define ACL_MODE_STR "arduR" /* list of valid characters */
> #define ACL_MODE_AP_CHR 'a'
> #define ACL_MODE_RD_CHR 'r'
> -#define ACL_MODE_WR_CHR 'w'
> +#define ACL_MODE_DE_CHR 'd'
> +#define ACL_MODE_UP_CHR 'u'
> #define ACL_MODE_RU_CHR 'R'
>
> /* result codes for pg_aclcheck */
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, 9 Jun 2000, Bruce Momjian wrote: > Are we addressing this? Yes, please do. And please don't forget the following: when dropping an user postgresql (actually the superuser must do it manually) should first revoke all user's permissions on all databases, because the deleted userid is reused on the next create user so the new user inherits all permissions from the deleted user => may be very very bad (an example of what can be done is not necessary I suppose ?) > > And for the bug report I posted on Feb 23rd on "drop user" which keeps the > > user's acl in the database, and the deleted user id being reused, I've not > > done anything, but I consider this a major problem. Please consider it for > > a next version. bye, Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
OK, this was a good point. Were did we leave this, folks?
> Hi,
>
> first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> nor version dependent AFAIK.
>
> I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> fact that update and insert are considered the same thing when you modify
> permissions with grant and revoke. (Maybe it was the wrong place to post
> it.)
>
> for example a "grant delete" also grants "update" which is completely
> wrong. More importantly the user is not informed, and this could lead to
> VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> update existing records, have the permission to delete all records...
>
> I've read postgresql documentation, especially the grant and revoke
> manpages, and I've found no mention of this bug, which is IMHO a Big
> Mistake (tm).
>
> attached to this message you'll find a patch for version 6.5.2 wich
> differentiate delete and update, because before they were considered as
> "write". The patch only modifies .c .y and .h files, but no documentation.
>
> the new acl rights look like: arRdu
> a for append
> r for read
> R for rules
> d for delete
> u for update
>
> instead of: arwR
> a for append
> r for read
> w for update AND delete
> R for rules
>
> This patch seems to work at least with what I've tested, you'll find a
> test session at the end of this message.
>
> I hope this patch will help and that it will be easy to incorporate it in
> 7.0, which I haven't the time to do for now.
>
> And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> user's acl in the database, and the deleted user id being reused, I've not
> done anything, but I consider this a major problem. Please consider it for
> a next version.
>
> Because I'm not an expert, I suggest you remove gram.c before applying the
> patch, in order for this file to be generated again from gram.y, but maybe
> this is not necessary.
>
> I'd be very pleased if some people could test this more than I can,
> because I don't use postgresql intensively with special permissions.
>
> I'm not sure for some parts of the patch, especially in execMain.c
> so if a postgresql hacker could examine it, this would be fine.
>
> dump of test session:
> ---------------------
>
> ------- CUT -------
>
> template1=> create database db;
> CREATEDB
> template1=> create user john;
> CREATE USER
> template1=> \connect db
> connecting to new database: db
> db=> create table t (id INT4, name TEXT);
> CREATE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | |
> +----------+--------------------------+
> db=> grant all on t to john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=arduR"} |
> +----------+--------------------------+
> db=> \connect db john
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18560 1
> db=> update t set name = 'yyy' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|yyy
> (1 row)
>
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18561 1
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke update on t from john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=ardR"} |
> +----------+--------------------------+
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (2, 'yyy');
> INSERT 18592 1
> db=> update t set name='modified by john' where id=2;
> ERROR: t: Permission denied.
> db=> delete from t where id=2;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> 1|xxx
> (1 row)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke insert on t from john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=rdR"} |
> +----------+--------------------------+
> db=> insert into t (id, name) values (3, 'I try to insert something');
> ERROR: t: Permission denied.
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18624 1
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> update t set name='john' where id =1;
> ERROR: t: Permission denied.
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke delete on t from john;
> CHANGE
> db=> grant update on t to john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> delete from t;
> ERROR: t: Permission denied.
> db=> update t set name='john' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|john
> (1 row)
>
> ------- CUT -------
>
> Thank you for reading.
>
> bye,
>
> Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Content-Description: the 6.5.2 patch
> diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> @@ -381,7 +381,7 @@
> * pg_database table, there is still additional permissions
> * checking in dbcommands.c
> */
> - if ((mode & ACL_WR) || (mode & ACL_AP))
> + if (mode & ACL_AP)
> return ACLCHECK_OK;
> }
>
> @@ -390,7 +390,7 @@
> * pg_shadow.usecatupd is set. (This is to let superusers protect
> * themselves from themselves.)
> */
> - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> + if ((mode & ACL_AP) &&
> !allowSystemTableMods && IsSystemRelationName(relname) &&
> !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> {
> diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> @@ -524,7 +524,9 @@
> if (lockstmt->mode == AccessShareLock)
> aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> else
> - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> + /* do we really need to have all these permissions at the same time ? */
> + /* shouldn't we test lockstmt->mode first ? */
> + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
>
> if (aclresult != ACLCHECK_OK)
> elog(ERROR, "LOCK TABLE: permission denied");
> diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> @@ -242,7 +242,8 @@
> FILE *fp;
> Relation rel;
> extern char *UserName; /* defined in global.c */
> - const AclMode required_access = from ? ACL_WR : ACL_RD;
> + /* why should we need other permissions than APPEND ? */
> + const AclMode required_access = from ? ACL_AP : ACL_RD;
> int result;
>
> rel = heap_openr(relname);
> diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c postgresql-6.5.2-patched/src/backend/commands/sequence.c
> --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> @@ -314,7 +314,8 @@
> Form_pg_sequence seq;
>
> #ifndef NO_SECURITY
> - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE permission ? */
> + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> seqname, seqname);
> #endif
> diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> @@ -115,7 +115,7 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> @@ -227,7 +227,8 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> @@ -329,11 +330,12 @@
> BeginTransactionBlock();
>
> /*
> - * Make sure the user attempting to create a user can delete from the
> + * Make sure the user attempting to delete a user can delete from the
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than DELETE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c postgresql-6.5.2-patched/src/backend/executor/execMain.c
> --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> @@ -464,14 +464,16 @@
> switch (operation)
> {
> case CMD_INSERT:
> - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> opstr = "append";
> break;
> case CMD_DELETE:
> + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> + opstr = "delete";
> + break;
> case CMD_UPDATE:
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> + opstr = "update";
> break;
> default:
> elog(ERROR, "ExecCheckPerms: bogus operation %d",
> @@ -508,8 +510,9 @@
> StrNCpy(rname.data,
> ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> NAMEDATALEN);
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + /* is it the right thing to do ? */
> + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> + opstr = "write"; /* unused ? */
> if (!ok)
> elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> }
> diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> @@ -1694,11 +1694,11 @@
>
> privileges: ALL PRIVILEGES
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | ALL
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | operation_commalist
> {
> @@ -1726,11 +1726,11 @@
> }
> | UPDATE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_UP_CHR;
> }
> | DELETE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_DE_CHR;
> }
> | RULE
> {
> diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> @@ -29,236 +29,236 @@
> RuleStmt *rstmt;
> InsertStmt *astmt;
> } YYSTYPE;
> -#define ABSOLUTE 257
> -#define ACTION 258
> -#define ADD 259
> -#define ALL 260
> -#define ALTER 261
> -#define AND 262
> -#define ANY 263
> -#define AS 264
> -#define ASC 265
> -#define BEGIN_TRANS 266
> -#define BETWEEN 267
> -#define BOTH 268
> -#define BY 269
> -#define CASCADE 270
> -#define CASE 271
> -#define CAST 272
> -#define CHAR 273
> -#define CHARACTER 274
> -#define CHECK 275
> -#define CLOSE 276
> -#define COALESCE 277
> -#define COLLATE 278
> -#define COLUMN 279
> -#define COMMIT 280
> -#define CONSTRAINT 281
> -#define CREATE 282
> -#define CROSS 283
> -#define CURRENT 284
> -#define CURRENT_DATE 285
> -#define CURRENT_TIME 286
> -#define CURRENT_TIMESTAMP 287
> -#define CURRENT_USER 288
> -#define CURSOR 289
> -#define DAY_P 290
> -#define DECIMAL 291
> -#define DECLARE 292
> -#define DEFAULT 293
> -#define DELETE 294
> -#define DESC 295
> -#define DISTINCT 296
> -#define DOUBLE 297
> -#define DROP 298
> -#define ELSE 299
> -#define END_TRANS 300
> -#define EXCEPT 301
> -#define EXECUTE 302
> -#define EXISTS 303
> -#define EXTRACT 304
> -#define FALSE_P 305
> -#define FETCH 306
> -#define FLOAT 307
> -#define FOR 308
> -#define FOREIGN 309
> -#define FROM 310
> -#define FULL 311
> -#define GLOBAL 312
> -#define GRANT 313
> -#define GROUP 314
> -#define HAVING 315
> -#define HOUR_P 316
> -#define IN 317
> -#define INNER_P 318
> -#define INSENSITIVE 319
> -#define INSERT 320
> -#define INTERSECT 321
> -#define INTERVAL 322
> -#define INTO 323
> -#define IS 324
> -#define ISOLATION 325
> -#define JOIN 326
> -#define KEY 327
> -#define LANGUAGE 328
> -#define LEADING 329
> -#define LEFT 330
> -#define LEVEL 331
> -#define LIKE 332
> -#define LOCAL 333
> -#define MATCH 334
> -#define MINUTE_P 335
> -#define MONTH_P 336
> -#define NAMES 337
> -#define NATIONAL 338
> -#define NATURAL 339
> -#define NCHAR 340
> -#define NEXT 341
> -#define NO 342
> -#define NOT 343
> -#define NULLIF 344
> -#define NULL_P 345
> -#define NUMERIC 346
> -#define OF 347
> -#define ON 348
> -#define ONLY 349
> -#define OPTION 350
> -#define OR 351
> -#define ORDER 352
> -#define OUTER_P 353
> -#define PARTIAL 354
> -#define POSITION 355
> -#define PRECISION 356
> -#define PRIMARY 357
> -#define PRIOR 358
> -#define PRIVILEGES 359
> -#define PROCEDURE 360
> -#define PUBLIC 361
> -#define READ 362
> -#define REFERENCES 363
> -#define RELATIVE 364
> -#define REVOKE 365
> -#define RIGHT 366
> -#define ROLLBACK 367
> -#define SCROLL 368
> -#define SECOND_P 369
> -#define SELECT 370
> -#define SET 371
> -#define SUBSTRING 372
> -#define TABLE 373
> -#define TEMP 374
> -#define TEMPORARY 375
> -#define THEN 376
> -#define TIME 377
> -#define TIMESTAMP 378
> -#define TIMEZONE_HOUR 379
> -#define TIMEZONE_MINUTE 380
> -#define TO 381
> -#define TRAILING 382
> -#define TRANSACTION 383
> -#define TRIM 384
> -#define TRUE_P 385
> -#define UNION 386
> -#define UNIQUE 387
> -#define UPDATE 388
> -#define USER 389
> -#define USING 390
> -#define VALUES 391
> -#define VARCHAR 392
> -#define VARYING 393
> -#define VIEW 394
> -#define WHEN 395
> -#define WHERE 396
> -#define WITH 397
> -#define WORK 398
> -#define YEAR_P 399
> -#define ZONE 400
> -#define TRIGGER 401
> -#define COMMITTED 402
> -#define SERIALIZABLE 403
> -#define TYPE_P 404
> -#define ABORT_TRANS 405
> -#define ACCESS 406
> -#define AFTER 407
> -#define AGGREGATE 408
> -#define ANALYZE 409
> -#define BACKWARD 410
> -#define BEFORE 411
> -#define BINARY 412
> -#define CACHE 413
> -#define CLUSTER 414
> -#define COPY 415
> -#define CREATEDB 416
> -#define CREATEUSER 417
> -#define CYCLE 418
> -#define DATABASE 419
> -#define DELIMITERS 420
> -#define DO 421
> -#define EACH 422
> -#define ENCODING 423
> -#define EXCLUSIVE 424
> -#define EXPLAIN 425
> -#define EXTEND 426
> -#define FORWARD 427
> -#define FUNCTION 428
> -#define HANDLER 429
> -#define INCREMENT 430
> -#define INDEX 431
> -#define INHERITS 432
> -#define INSTEAD 433
> -#define ISNULL 434
> -#define LANCOMPILER 435
> -#define LIMIT 436
> -#define LISTEN 437
> -#define LOAD 438
> -#define LOCATION 439
> -#define LOCK_P 440
> -#define MAXVALUE 441
> -#define MINVALUE 442
> -#define MODE 443
> -#define MOVE 444
> -#define NEW 445
> -#define NOCREATEDB 446
> -#define NOCREATEUSER 447
> -#define NONE 448
> -#define NOTHING 449
> -#define NOTIFY 450
> -#define NOTNULL 451
> -#define OFFSET 452
> -#define OIDS 453
> -#define OPERATOR 454
> -#define PASSWORD 455
> -#define PROCEDURAL 456
> -#define RENAME 457
> -#define RESET 458
> -#define RETURNS 459
> -#define ROW 460
> -#define RULE 461
> -#define SEQUENCE 462
> -#define SERIAL 463
> -#define SETOF 464
> -#define SHARE 465
> -#define SHOW 466
> -#define START 467
> -#define STATEMENT 468
> -#define STDIN 469
> -#define STDOUT 470
> -#define TRUSTED 471
> -#define UNLISTEN 472
> -#define UNTIL 473
> -#define VACUUM 474
> -#define VALID 475
> -#define VERBOSE 476
> -#define VERSION 477
> -#define IDENT 478
> -#define SCONST 479
> -#define Op 480
> -#define ICONST 481
> -#define PARAM 482
> -#define FCONST 483
> -#define OP 484
> -#define UMINUS 485
> -#define TYPECAST 486
> +#define ABSOLUTE 258
> +#define ACTION 259
> +#define ADD 260
> +#define ALL 261
> +#define ALTER 262
> +#define AND 263
> +#define ANY 264
> +#define AS 265
> +#define ASC 266
> +#define BEGIN_TRANS 267
> +#define BETWEEN 268
> +#define BOTH 269
> +#define BY 270
> +#define CASCADE 271
> +#define CASE 272
> +#define CAST 273
> +#define CHAR 274
> +#define CHARACTER 275
> +#define CHECK 276
> +#define CLOSE 277
> +#define COALESCE 278
> +#define COLLATE 279
> +#define COLUMN 280
> +#define COMMIT 281
> +#define CONSTRAINT 282
> +#define CREATE 283
> +#define CROSS 284
> +#define CURRENT 285
> +#define CURRENT_DATE 286
> +#define CURRENT_TIME 287
> +#define CURRENT_TIMESTAMP 288
> +#define CURRENT_USER 289
> +#define CURSOR 290
> +#define DAY_P 291
> +#define DECIMAL 292
> +#define DECLARE 293
> +#define DEFAULT 294
> +#define DELETE 295
> +#define DESC 296
> +#define DISTINCT 297
> +#define DOUBLE 298
> +#define DROP 299
> +#define ELSE 300
> +#define END_TRANS 301
> +#define EXCEPT 302
> +#define EXECUTE 303
> +#define EXISTS 304
> +#define EXTRACT 305
> +#define FALSE_P 306
> +#define FETCH 307
> +#define FLOAT 308
> +#define FOR 309
> +#define FOREIGN 310
> +#define FROM 311
> +#define FULL 312
> +#define GLOBAL 313
> +#define GRANT 314
> +#define GROUP 315
> +#define HAVING 316
> +#define HOUR_P 317
> +#define IN 318
> +#define INNER_P 319
> +#define INSENSITIVE 320
> +#define INSERT 321
> +#define INTERSECT 322
> +#define INTERVAL 323
> +#define INTO 324
> +#define IS 325
> +#define ISOLATION 326
> +#define JOIN 327
> +#define KEY 328
> +#define LANGUAGE 329
> +#define LEADING 330
> +#define LEFT 331
> +#define LEVEL 332
> +#define LIKE 333
> +#define LOCAL 334
> +#define MATCH 335
> +#define MINUTE_P 336
> +#define MONTH_P 337
> +#define NAMES 338
> +#define NATIONAL 339
> +#define NATURAL 340
> +#define NCHAR 341
> +#define NEXT 342
> +#define NO 343
> +#define NOT 344
> +#define NULLIF 345
> +#define NULL_P 346
> +#define NUMERIC 347
> +#define OF 348
> +#define ON 349
> +#define ONLY 350
> +#define OPTION 351
> +#define OR 352
> +#define ORDER 353
> +#define OUTER_P 354
> +#define PARTIAL 355
> +#define POSITION 356
> +#define PRECISION 357
> +#define PRIMARY 358
> +#define PRIOR 359
> +#define PRIVILEGES 360
> +#define PROCEDURE 361
> +#define PUBLIC 362
> +#define READ 363
> +#define REFERENCES 364
> +#define RELATIVE 365
> +#define REVOKE 366
> +#define RIGHT 367
> +#define ROLLBACK 368
> +#define SCROLL 369
> +#define SECOND_P 370
> +#define SELECT 371
> +#define SET 372
> +#define SUBSTRING 373
> +#define TABLE 374
> +#define TEMP 375
> +#define TEMPORARY 376
> +#define THEN 377
> +#define TIME 378
> +#define TIMESTAMP 379
> +#define TIMEZONE_HOUR 380
> +#define TIMEZONE_MINUTE 381
> +#define TO 382
> +#define TRAILING 383
> +#define TRANSACTION 384
> +#define TRIM 385
> +#define TRUE_P 386
> +#define UNION 387
> +#define UNIQUE 388
> +#define UPDATE 389
> +#define USER 390
> +#define USING 391
> +#define VALUES 392
> +#define VARCHAR 393
> +#define VARYING 394
> +#define VIEW 395
> +#define WHEN 396
> +#define WHERE 397
> +#define WITH 398
> +#define WORK 399
> +#define YEAR_P 400
> +#define ZONE 401
> +#define TRIGGER 402
> +#define COMMITTED 403
> +#define SERIALIZABLE 404
> +#define TYPE_P 405
> +#define ABORT_TRANS 406
> +#define ACCESS 407
> +#define AFTER 408
> +#define AGGREGATE 409
> +#define ANALYZE 410
> +#define BACKWARD 411
> +#define BEFORE 412
> +#define BINARY 413
> +#define CACHE 414
> +#define CLUSTER 415
> +#define COPY 416
> +#define CREATEDB 417
> +#define CREATEUSER 418
> +#define CYCLE 419
> +#define DATABASE 420
> +#define DELIMITERS 421
> +#define DO 422
> +#define EACH 423
> +#define ENCODING 424
> +#define EXCLUSIVE 425
> +#define EXPLAIN 426
> +#define EXTEND 427
> +#define FORWARD 428
> +#define FUNCTION 429
> +#define HANDLER 430
> +#define INCREMENT 431
> +#define INDEX 432
> +#define INHERITS 433
> +#define INSTEAD 434
> +#define ISNULL 435
> +#define LANCOMPILER 436
> +#define LIMIT 437
> +#define LISTEN 438
> +#define LOAD 439
> +#define LOCATION 440
> +#define LOCK_P 441
> +#define MAXVALUE 442
> +#define MINVALUE 443
> +#define MODE 444
> +#define MOVE 445
> +#define NEW 446
> +#define NOCREATEDB 447
> +#define NOCREATEUSER 448
> +#define NONE 449
> +#define NOTHING 450
> +#define NOTIFY 451
> +#define NOTNULL 452
> +#define OFFSET 453
> +#define OIDS 454
> +#define OPERATOR 455
> +#define PASSWORD 456
> +#define PROCEDURAL 457
> +#define RENAME 458
> +#define RESET 459
> +#define RETURNS 460
> +#define ROW 461
> +#define RULE 462
> +#define SEQUENCE 463
> +#define SERIAL 464
> +#define SETOF 465
> +#define SHARE 466
> +#define SHOW 467
> +#define START 468
> +#define STATEMENT 469
> +#define STDIN 470
> +#define STDOUT 471
> +#define TRUSTED 472
> +#define UNLISTEN 473
> +#define UNTIL 474
> +#define VACUUM 475
> +#define VALID 476
> +#define VERBOSE 477
> +#define VERSION 478
> +#define IDENT 479
> +#define SCONST 480
> +#define Op 481
> +#define ICONST 482
> +#define PARAM 483
> +#define FCONST 484
> +#define OP 485
> +#define UMINUS 486
> +#define TYPECAST 487
>
>
> extern YYSTYPE yylval;
> diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> @@ -601,7 +601,8 @@
>
> if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> - ACL_WR : ACL_RD)))
> + /* if nextval and setval are atomic, which I don't know, update should be enough */
> + ACL_UP : ACL_RD)))
> != ACLCHECK_OK)
> elog(ERROR, "%s.%s: %s",
> seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> @@ -228,8 +228,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
> else
> diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> @@ -2282,8 +2282,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
>
> diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> @@ -154,8 +154,11 @@
> case ACL_MODE_RD_CHR:
> aip->ai_mode |= ACL_RD;
> break;
> - case ACL_MODE_WR_CHR:
> - aip->ai_mode |= ACL_WR;
> + case ACL_MODE_DE_CHR:
> + aip->ai_mode |= ACL_DE;
> + break;
> + case ACL_MODE_UP_CHR:
> + aip->ai_mode |= ACL_UP;
> break;
> case ACL_MODE_RU_CHR:
> aip->ai_mode |= ACL_RU;
> @@ -272,7 +275,7 @@
> if (!aip)
> aip = &default_aclitem;
>
> - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> if (!out)
> elog(ERROR, "aclitemout: palloc failed");
> *p = '\0';
> @@ -605,9 +608,8 @@
> int i;
> int l;
>
> - Assert(strlen(old_privlist) < 5);
> - priv = palloc(5); /* at most "rwaR" */ ;
> -
> + Assert(strlen(old_privlist) < 6);
> + priv = palloc(6); /* at most "arduR" */ ;
> if (old_privlist == NULL || old_privlist[0] == '\0')
> {
> priv[0] = new_priv;
> @@ -619,7 +621,7 @@
>
> l = strlen(old_privlist);
>
> - if (l == 4)
> + if (l == 5)
> { /* can't add any more privileges */
> return priv;
> }
> diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> @@ -54,9 +54,10 @@
> #define ACL_NO 0 /* no permissions */
> #define ACL_AP (1<<0) /* append */
> #define ACL_RD (1<<1) /* read */
> -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> -#define ACL_RU (1<<3) /* place rules */
> -#define N_ACL_MODES 4
> +#define ACL_DE (1<<2) /* delete */
> +#define ACL_UP (1<<3) /* update/replace */
> +#define ACL_RU (1<<4) /* place rules */
> +#define N_ACL_MODES 5
>
> #define ACL_MODECHG_ADD 1
> #define ACL_MODECHG_DEL 2
> @@ -65,7 +66,8 @@
> /* change this line if you want to set the default acl permission */
> #define ACL_WORLD_DEFAULT (ACL_NO)
> /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> +
> +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
>
> /*
> * AclItem
> @@ -118,10 +120,12 @@
> #define ACL_MODECHG_ADD_CHR '+'
> #define ACL_MODECHG_DEL_CHR '-'
> #define ACL_MODECHG_EQL_CHR '='
> -#define ACL_MODE_STR "arwR" /* list of valid characters */
> +
> +#define ACL_MODE_STR "arduR" /* list of valid characters */
> #define ACL_MODE_AP_CHR 'a'
> #define ACL_MODE_RD_CHR 'r'
> -#define ACL_MODE_WR_CHR 'w'
> +#define ACL_MODE_DE_CHR 'd'
> +#define ACL_MODE_UP_CHR 'u'
> #define ACL_MODE_RU_CHR 'R'
>
> /* result codes for pg_aclcheck */
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes: > OK, this was a good point. Were did we leave this, folks? I was going to do some extensive work on the privilege system, but if you guys always postpone beta for a month just a day before it was supposed to be I'll never know when to start. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Nevertheless, you should probably consider installing the patch.
Bruce Momjian writes:
> OK, this was a good point. Were did we leave this, folks?
>
>
> > Hi,
> >
> > first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> > nor version dependent AFAIK.
> >
> > I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> > fact that update and insert are considered the same thing when you modify
> > permissions with grant and revoke. (Maybe it was the wrong place to post
> > it.)
> >
> > for example a "grant delete" also grants "update" which is completely
> > wrong. More importantly the user is not informed, and this could lead to
> > VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> > update existing records, have the permission to delete all records...
> >
> > I've read postgresql documentation, especially the grant and revoke
> > manpages, and I've found no mention of this bug, which is IMHO a Big
> > Mistake (tm).
> >
> > attached to this message you'll find a patch for version 6.5.2 wich
> > differentiate delete and update, because before they were considered as
> > "write". The patch only modifies .c .y and .h files, but no documentation.
> >
> > the new acl rights look like: arRdu
> > a for append
> > r for read
> > R for rules
> > d for delete
> > u for update
> >
> > instead of: arwR
> > a for append
> > r for read
> > w for update AND delete
> > R for rules
> >
> > This patch seems to work at least with what I've tested, you'll find a
> > test session at the end of this message.
> >
> > I hope this patch will help and that it will be easy to incorporate it in
> > 7.0, which I haven't the time to do for now.
> >
> > And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> > user's acl in the database, and the deleted user id being reused, I've not
> > done anything, but I consider this a major problem. Please consider it for
> > a next version.
> >
> > Because I'm not an expert, I suggest you remove gram.c before applying the
> > patch, in order for this file to be generated again from gram.y, but maybe
> > this is not necessary.
> >
> > I'd be very pleased if some people could test this more than I can,
> > because I don't use postgresql intensively with special permissions.
> >
> > I'm not sure for some parts of the patch, especially in execMain.c
> > so if a postgresql hacker could examine it, this would be fine.
> >
> > dump of test session:
> > ---------------------
> >
> > ------- CUT -------
> >
> > template1=> create database db;
> > CREATEDB
> > template1=> create user john;
> > CREATE USER
> > template1=> \connect db
> > connecting to new database: db
> > db=> create table t (id INT4, name TEXT);
> > CREATE
> > db=> \z
> > Database = db
> > +----------+--------------------------+
> > | Relation | Grant/Revoke Permissions |
> > +----------+--------------------------+
> > | t | |
> > +----------+--------------------------+
> > db=> grant all on t to john;
> > CHANGE
> > db=> \z
> > Database = db
> > +----------+--------------------------+
> > | Relation | Grant/Revoke Permissions |
> > +----------+--------------------------+
> > | t | {"=","john=arduR"} |
> > +----------+--------------------------+
> > db=> \connect db john
> > connecting to new database: db as user: john
> > db=> insert into t (id, name) values (1, 'xxx');
> > INSERT 18560 1
> > db=> update t set name = 'yyy' where id=1;
> > UPDATE 1
> > db=> select * from t;
> > id|name
> > --+----
> > 1|yyy
> > (1 row)
> >
> > db=> delete from t;
> > DELETE 1
> > db=> select * from t;
> > id|name
> > --+----
> > (0 rows)
> >
> > db=> insert into t (id, name) values (1, 'xxx');
> > INSERT 18561 1
> > db=> \connect db postgres
> > connecting to new database: db as user: postgres
> > db=> revoke update on t from john;
> > CHANGE
> > db=> \z
> > Database = db
> > +----------+--------------------------+
> > | Relation | Grant/Revoke Permissions |
> > +----------+--------------------------+
> > | t | {"=","john=ardR"} |
> > +----------+--------------------------+
> > db=> \connect db john;
> > connecting to new database: db as user: john
> > db=> insert into t (id, name) values (2, 'yyy');
> > INSERT 18592 1
> > db=> update t set name='modified by john' where id=2;
> > ERROR: t: Permission denied.
> > db=> delete from t where id=2;
> > DELETE 1
> > db=> select * from t;
> > id|name
> > --+----
> > 1|xxx
> > (1 row)
> >
> > db=> \connect db postgres
> > connecting to new database: db as user: postgres
> > db=> revoke insert on t from john;
> > CHANGE
> > db=> \connect db john;
> > connecting to new database: db as user: john
> > db=> \z
> > Database = db
> > +----------+--------------------------+
> > | Relation | Grant/Revoke Permissions |
> > +----------+--------------------------+
> > | t | {"=","john=rdR"} |
> > +----------+--------------------------+
> > db=> insert into t (id, name) values (3, 'I try to insert something');
> > ERROR: t: Permission denied.
> > db=> delete from t;
> > DELETE 1
> > db=> select * from t;
> > id|name
> > --+----
> > (0 rows)
> >
> > db=> \connect db postgres
> > connecting to new database: db as user: postgres
> > db=> insert into t (id, name) values (1, 'xxx');
> > INSERT 18624 1
> > db=> \connect db john;
> > connecting to new database: db as user: john
> > db=> update t set name='john' where id =1;
> > ERROR: t: Permission denied.
> > db=> \connect db postgres
> > connecting to new database: db as user: postgres
> > db=> revoke delete on t from john;
> > CHANGE
> > db=> grant update on t to john;
> > CHANGE
> > db=> \connect db john;
> > connecting to new database: db as user: john
> > db=> delete from t;
> > ERROR: t: Permission denied.
> > db=> update t set name='john' where id=1;
> > UPDATE 1
> > db=> select * from t;
> > id|name
> > --+----
> > 1|john
> > (1 row)
> >
> > ------- CUT -------
> >
> > Thank you for reading.
> >
> > bye,
> >
> > Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> > Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> > 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
> Content-Description: the 6.5.2 patch
>
> > diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> > --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> > +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> > @@ -381,7 +381,7 @@
> > * pg_database table, there is still additional permissions
> > * checking in dbcommands.c
> > */
> > - if ((mode & ACL_WR) || (mode & ACL_AP))
> > + if (mode & ACL_AP)
> > return ACLCHECK_OK;
> > }
> >
> > @@ -390,7 +390,7 @@
> > * pg_shadow.usecatupd is set. (This is to let superusers protect
> > * themselves from themselves.)
> > */
> > - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> > + if ((mode & ACL_AP) &&
> > !allowSystemTableMods && IsSystemRelationName(relname) &&
> > !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> > {
> > diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> > --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> > +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> > @@ -524,7 +524,9 @@
> > if (lockstmt->mode == AccessShareLock)
> > aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> > else
> > - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> > + /* do we really need to have all these permissions at the same time ? */
> > + /* shouldn't we test lockstmt->mode first ? */
> > + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
> >
> > if (aclresult != ACLCHECK_OK)
> > elog(ERROR, "LOCK TABLE: permission denied");
> > diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> > --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> > +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> > @@ -242,7 +242,8 @@
> > FILE *fp;
> > Relation rel;
> > extern char *UserName; /* defined in global.c */
> > - const AclMode required_access = from ? ACL_WR : ACL_RD;
> > + /* why should we need other permissions than APPEND ? */
> > + const AclMode required_access = from ? ACL_AP : ACL_RD;
> > int result;
> >
> > rel = heap_openr(relname);
> > diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c
postgresql-6.5.2-patched/src/backend/commands/sequence.c
> > --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> > +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> > @@ -314,7 +314,8 @@
> > Form_pg_sequence seq;
> >
> > #ifndef NO_SECURITY
> > - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> > + /* why should we need more than UPDATE permission ? */
> > + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> > elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> > seqname, seqname);
> > #endif
> > diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> > --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> > +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> > @@ -115,7 +115,7 @@
> > * pg_shadow relation.
> > */
> > pg_shadow = GetPgUserName();
> > - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> > + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> > {
> > UserAbortTransactionBlock();
> > elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> > @@ -227,7 +227,8 @@
> > * pg_shadow relation.
> > */
> > pg_shadow = GetPgUserName();
> > - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> > + /* why should we need more than UPDATE ? */
> > + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> > {
> > UserAbortTransactionBlock();
> > elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> > @@ -329,11 +330,12 @@
> > BeginTransactionBlock();
> >
> > /*
> > - * Make sure the user attempting to create a user can delete from the
> > + * Make sure the user attempting to delete a user can delete from the
> > * pg_shadow relation.
> > */
> > pg_shadow = GetPgUserName();
> > - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> > + /* why should we need more than DELETE ? */
> > + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> > {
> > UserAbortTransactionBlock();
> > elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> > diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c
postgresql-6.5.2-patched/src/backend/executor/execMain.c
> > --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> > +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> > @@ -464,14 +464,16 @@
> > switch (operation)
> > {
> > case CMD_INSERT:
> > - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> > - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> > + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> > opstr = "append";
> > break;
> > case CMD_DELETE:
> > + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> > + opstr = "delete";
> > + break;
> > case CMD_UPDATE:
> > - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> > - opstr = "write";
> > + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> > + opstr = "update";
> > break;
> > default:
> > elog(ERROR, "ExecCheckPerms: bogus operation %d",
> > @@ -508,8 +510,9 @@
> > StrNCpy(rname.data,
> > ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> > NAMEDATALEN);
> > - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> > - opstr = "write";
> > + /* is it the right thing to do ? */
> > + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> > + opstr = "write"; /* unused ? */
> > if (!ok)
> > elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> > }
> > diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> > --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> > +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> > @@ -1694,11 +1694,11 @@
> >
> > privileges: ALL PRIVILEGES
> > {
> > - $$ = aclmakepriv("rwaR",0);
> > + $$ = aclmakepriv("raduR",0);
> > }
> > | ALL
> > {
> > - $$ = aclmakepriv("rwaR",0);
> > + $$ = aclmakepriv("raduR",0);
> > }
> > | operation_commalist
> > {
> > @@ -1726,11 +1726,11 @@
> > }
> > | UPDATE
> > {
> > - $$ = ACL_MODE_WR_CHR;
> > + $$ = ACL_MODE_UP_CHR;
> > }
> > | DELETE
> > {
> > - $$ = ACL_MODE_WR_CHR;
> > + $$ = ACL_MODE_DE_CHR;
> > }
> > | RULE
> > {
> > diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> > --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> > +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> > @@ -29,236 +29,236 @@
> > RuleStmt *rstmt;
> > InsertStmt *astmt;
> > } YYSTYPE;
> > -#define ABSOLUTE 257
> > -#define ACTION 258
> > -#define ADD 259
> > -#define ALL 260
> > -#define ALTER 261
> > -#define AND 262
> > -#define ANY 263
> > -#define AS 264
> > -#define ASC 265
> > -#define BEGIN_TRANS 266
> > -#define BETWEEN 267
> > -#define BOTH 268
> > -#define BY 269
> > -#define CASCADE 270
> > -#define CASE 271
> > -#define CAST 272
> > -#define CHAR 273
> > -#define CHARACTER 274
> > -#define CHECK 275
> > -#define CLOSE 276
> > -#define COALESCE 277
> > -#define COLLATE 278
> > -#define COLUMN 279
> > -#define COMMIT 280
> > -#define CONSTRAINT 281
> > -#define CREATE 282
> > -#define CROSS 283
> > -#define CURRENT 284
> > -#define CURRENT_DATE 285
> > -#define CURRENT_TIME 286
> > -#define CURRENT_TIMESTAMP 287
> > -#define CURRENT_USER 288
> > -#define CURSOR 289
> > -#define DAY_P 290
> > -#define DECIMAL 291
> > -#define DECLARE 292
> > -#define DEFAULT 293
> > -#define DELETE 294
> > -#define DESC 295
> > -#define DISTINCT 296
> > -#define DOUBLE 297
> > -#define DROP 298
> > -#define ELSE 299
> > -#define END_TRANS 300
> > -#define EXCEPT 301
> > -#define EXECUTE 302
> > -#define EXISTS 303
> > -#define EXTRACT 304
> > -#define FALSE_P 305
> > -#define FETCH 306
> > -#define FLOAT 307
> > -#define FOR 308
> > -#define FOREIGN 309
> > -#define FROM 310
> > -#define FULL 311
> > -#define GLOBAL 312
> > -#define GRANT 313
> > -#define GROUP 314
> > -#define HAVING 315
> > -#define HOUR_P 316
> > -#define IN 317
> > -#define INNER_P 318
> > -#define INSENSITIVE 319
> > -#define INSERT 320
> > -#define INTERSECT 321
> > -#define INTERVAL 322
> > -#define INTO 323
> > -#define IS 324
> > -#define ISOLATION 325
> > -#define JOIN 326
> > -#define KEY 327
> > -#define LANGUAGE 328
> > -#define LEADING 329
> > -#define LEFT 330
> > -#define LEVEL 331
> > -#define LIKE 332
> > -#define LOCAL 333
> > -#define MATCH 334
> > -#define MINUTE_P 335
> > -#define MONTH_P 336
> > -#define NAMES 337
> > -#define NATIONAL 338
> > -#define NATURAL 339
> > -#define NCHAR 340
> > -#define NEXT 341
> > -#define NO 342
> > -#define NOT 343
> > -#define NULLIF 344
> > -#define NULL_P 345
> > -#define NUMERIC 346
> > -#define OF 347
> > -#define ON 348
> > -#define ONLY 349
> > -#define OPTION 350
> > -#define OR 351
> > -#define ORDER 352
> > -#define OUTER_P 353
> > -#define PARTIAL 354
> > -#define POSITION 355
> > -#define PRECISION 356
> > -#define PRIMARY 357
> > -#define PRIOR 358
> > -#define PRIVILEGES 359
> > -#define PROCEDURE 360
> > -#define PUBLIC 361
> > -#define READ 362
> > -#define REFERENCES 363
> > -#define RELATIVE 364
> > -#define REVOKE 365
> > -#define RIGHT 366
> > -#define ROLLBACK 367
> > -#define SCROLL 368
> > -#define SECOND_P 369
> > -#define SELECT 370
> > -#define SET 371
> > -#define SUBSTRING 372
> > -#define TABLE 373
> > -#define TEMP 374
> > -#define TEMPORARY 375
> > -#define THEN 376
> > -#define TIME 377
> > -#define TIMESTAMP 378
> > -#define TIMEZONE_HOUR 379
> > -#define TIMEZONE_MINUTE 380
> > -#define TO 381
> > -#define TRAILING 382
> > -#define TRANSACTION 383
> > -#define TRIM 384
> > -#define TRUE_P 385
> > -#define UNION 386
> > -#define UNIQUE 387
> > -#define UPDATE 388
> > -#define USER 389
> > -#define USING 390
> > -#define VALUES 391
> > -#define VARCHAR 392
> > -#define VARYING 393
> > -#define VIEW 394
> > -#define WHEN 395
> > -#define WHERE 396
> > -#define WITH 397
> > -#define WORK 398
> > -#define YEAR_P 399
> > -#define ZONE 400
> > -#define TRIGGER 401
> > -#define COMMITTED 402
> > -#define SERIALIZABLE 403
> > -#define TYPE_P 404
> > -#define ABORT_TRANS 405
> > -#define ACCESS 406
> > -#define AFTER 407
> > -#define AGGREGATE 408
> > -#define ANALYZE 409
> > -#define BACKWARD 410
> > -#define BEFORE 411
> > -#define BINARY 412
> > -#define CACHE 413
> > -#define CLUSTER 414
> > -#define COPY 415
> > -#define CREATEDB 416
> > -#define CREATEUSER 417
> > -#define CYCLE 418
> > -#define DATABASE 419
> > -#define DELIMITERS 420
> > -#define DO 421
> > -#define EACH 422
> > -#define ENCODING 423
> > -#define EXCLUSIVE 424
> > -#define EXPLAIN 425
> > -#define EXTEND 426
> > -#define FORWARD 427
> > -#define FUNCTION 428
> > -#define HANDLER 429
> > -#define INCREMENT 430
> > -#define INDEX 431
> > -#define INHERITS 432
> > -#define INSTEAD 433
> > -#define ISNULL 434
> > -#define LANCOMPILER 435
> > -#define LIMIT 436
> > -#define LISTEN 437
> > -#define LOAD 438
> > -#define LOCATION 439
> > -#define LOCK_P 440
> > -#define MAXVALUE 441
> > -#define MINVALUE 442
> > -#define MODE 443
> > -#define MOVE 444
> > -#define NEW 445
> > -#define NOCREATEDB 446
> > -#define NOCREATEUSER 447
> > -#define NONE 448
> > -#define NOTHING 449
> > -#define NOTIFY 450
> > -#define NOTNULL 451
> > -#define OFFSET 452
> > -#define OIDS 453
> > -#define OPERATOR 454
> > -#define PASSWORD 455
> > -#define PROCEDURAL 456
> > -#define RENAME 457
> > -#define RESET 458
> > -#define RETURNS 459
> > -#define ROW 460
> > -#define RULE 461
> > -#define SEQUENCE 462
> > -#define SERIAL 463
> > -#define SETOF 464
> > -#define SHARE 465
> > -#define SHOW 466
> > -#define START 467
> > -#define STATEMENT 468
> > -#define STDIN 469
> > -#define STDOUT 470
> > -#define TRUSTED 471
> > -#define UNLISTEN 472
> > -#define UNTIL 473
> > -#define VACUUM 474
> > -#define VALID 475
> > -#define VERBOSE 476
> > -#define VERSION 477
> > -#define IDENT 478
> > -#define SCONST 479
> > -#define Op 480
> > -#define ICONST 481
> > -#define PARAM 482
> > -#define FCONST 483
> > -#define OP 484
> > -#define UMINUS 485
> > -#define TYPECAST 486
> > +#define ABSOLUTE 258
> > +#define ACTION 259
> > +#define ADD 260
> > +#define ALL 261
> > +#define ALTER 262
> > +#define AND 263
> > +#define ANY 264
> > +#define AS 265
> > +#define ASC 266
> > +#define BEGIN_TRANS 267
> > +#define BETWEEN 268
> > +#define BOTH 269
> > +#define BY 270
> > +#define CASCADE 271
> > +#define CASE 272
> > +#define CAST 273
> > +#define CHAR 274
> > +#define CHARACTER 275
> > +#define CHECK 276
> > +#define CLOSE 277
> > +#define COALESCE 278
> > +#define COLLATE 279
> > +#define COLUMN 280
> > +#define COMMIT 281
> > +#define CONSTRAINT 282
> > +#define CREATE 283
> > +#define CROSS 284
> > +#define CURRENT 285
> > +#define CURRENT_DATE 286
> > +#define CURRENT_TIME 287
> > +#define CURRENT_TIMESTAMP 288
> > +#define CURRENT_USER 289
> > +#define CURSOR 290
> > +#define DAY_P 291
> > +#define DECIMAL 292
> > +#define DECLARE 293
> > +#define DEFAULT 294
> > +#define DELETE 295
> > +#define DESC 296
> > +#define DISTINCT 297
> > +#define DOUBLE 298
> > +#define DROP 299
> > +#define ELSE 300
> > +#define END_TRANS 301
> > +#define EXCEPT 302
> > +#define EXECUTE 303
> > +#define EXISTS 304
> > +#define EXTRACT 305
> > +#define FALSE_P 306
> > +#define FETCH 307
> > +#define FLOAT 308
> > +#define FOR 309
> > +#define FOREIGN 310
> > +#define FROM 311
> > +#define FULL 312
> > +#define GLOBAL 313
> > +#define GRANT 314
> > +#define GROUP 315
> > +#define HAVING 316
> > +#define HOUR_P 317
> > +#define IN 318
> > +#define INNER_P 319
> > +#define INSENSITIVE 320
> > +#define INSERT 321
> > +#define INTERSECT 322
> > +#define INTERVAL 323
> > +#define INTO 324
> > +#define IS 325
> > +#define ISOLATION 326
> > +#define JOIN 327
> > +#define KEY 328
> > +#define LANGUAGE 329
> > +#define LEADING 330
> > +#define LEFT 331
> > +#define LEVEL 332
> > +#define LIKE 333
> > +#define LOCAL 334
> > +#define MATCH 335
> > +#define MINUTE_P 336
> > +#define MONTH_P 337
> > +#define NAMES 338
> > +#define NATIONAL 339
> > +#define NATURAL 340
> > +#define NCHAR 341
> > +#define NEXT 342
> > +#define NO 343
> > +#define NOT 344
> > +#define NULLIF 345
> > +#define NULL_P 346
> > +#define NUMERIC 347
> > +#define OF 348
> > +#define ON 349
> > +#define ONLY 350
> > +#define OPTION 351
> > +#define OR 352
> > +#define ORDER 353
> > +#define OUTER_P 354
> > +#define PARTIAL 355
> > +#define POSITION 356
> > +#define PRECISION 357
> > +#define PRIMARY 358
> > +#define PRIOR 359
> > +#define PRIVILEGES 360
> > +#define PROCEDURE 361
> > +#define PUBLIC 362
> > +#define READ 363
> > +#define REFERENCES 364
> > +#define RELATIVE 365
> > +#define REVOKE 366
> > +#define RIGHT 367
> > +#define ROLLBACK 368
> > +#define SCROLL 369
> > +#define SECOND_P 370
> > +#define SELECT 371
> > +#define SET 372
> > +#define SUBSTRING 373
> > +#define TABLE 374
> > +#define TEMP 375
> > +#define TEMPORARY 376
> > +#define THEN 377
> > +#define TIME 378
> > +#define TIMESTAMP 379
> > +#define TIMEZONE_HOUR 380
> > +#define TIMEZONE_MINUTE 381
> > +#define TO 382
> > +#define TRAILING 383
> > +#define TRANSACTION 384
> > +#define TRIM 385
> > +#define TRUE_P 386
> > +#define UNION 387
> > +#define UNIQUE 388
> > +#define UPDATE 389
> > +#define USER 390
> > +#define USING 391
> > +#define VALUES 392
> > +#define VARCHAR 393
> > +#define VARYING 394
> > +#define VIEW 395
> > +#define WHEN 396
> > +#define WHERE 397
> > +#define WITH 398
> > +#define WORK 399
> > +#define YEAR_P 400
> > +#define ZONE 401
> > +#define TRIGGER 402
> > +#define COMMITTED 403
> > +#define SERIALIZABLE 404
> > +#define TYPE_P 405
> > +#define ABORT_TRANS 406
> > +#define ACCESS 407
> > +#define AFTER 408
> > +#define AGGREGATE 409
> > +#define ANALYZE 410
> > +#define BACKWARD 411
> > +#define BEFORE 412
> > +#define BINARY 413
> > +#define CACHE 414
> > +#define CLUSTER 415
> > +#define COPY 416
> > +#define CREATEDB 417
> > +#define CREATEUSER 418
> > +#define CYCLE 419
> > +#define DATABASE 420
> > +#define DELIMITERS 421
> > +#define DO 422
> > +#define EACH 423
> > +#define ENCODING 424
> > +#define EXCLUSIVE 425
> > +#define EXPLAIN 426
> > +#define EXTEND 427
> > +#define FORWARD 428
> > +#define FUNCTION 429
> > +#define HANDLER 430
> > +#define INCREMENT 431
> > +#define INDEX 432
> > +#define INHERITS 433
> > +#define INSTEAD 434
> > +#define ISNULL 435
> > +#define LANCOMPILER 436
> > +#define LIMIT 437
> > +#define LISTEN 438
> > +#define LOAD 439
> > +#define LOCATION 440
> > +#define LOCK_P 441
> > +#define MAXVALUE 442
> > +#define MINVALUE 443
> > +#define MODE 444
> > +#define MOVE 445
> > +#define NEW 446
> > +#define NOCREATEDB 447
> > +#define NOCREATEUSER 448
> > +#define NONE 449
> > +#define NOTHING 450
> > +#define NOTIFY 451
> > +#define NOTNULL 452
> > +#define OFFSET 453
> > +#define OIDS 454
> > +#define OPERATOR 455
> > +#define PASSWORD 456
> > +#define PROCEDURAL 457
> > +#define RENAME 458
> > +#define RESET 459
> > +#define RETURNS 460
> > +#define ROW 461
> > +#define RULE 462
> > +#define SEQUENCE 463
> > +#define SERIAL 464
> > +#define SETOF 465
> > +#define SHARE 466
> > +#define SHOW 467
> > +#define START 468
> > +#define STATEMENT 469
> > +#define STDIN 470
> > +#define STDOUT 471
> > +#define TRUSTED 472
> > +#define UNLISTEN 473
> > +#define UNTIL 474
> > +#define VACUUM 475
> > +#define VALID 476
> > +#define VERBOSE 477
> > +#define VERSION 478
> > +#define IDENT 479
> > +#define SCONST 480
> > +#define Op 481
> > +#define ICONST 482
> > +#define PARAM 483
> > +#define FCONST 484
> > +#define OP 485
> > +#define UMINUS 486
> > +#define TYPECAST 487
> >
> >
> > extern YYSTYPE yylval;
> > diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c
postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> > --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> > +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> > @@ -601,7 +601,8 @@
> >
> > if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> > (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> > - ACL_WR : ACL_RD)))
> > + /* if nextval and setval are atomic, which I don't know, update should be enough */
> > + ACL_UP : ACL_RD)))
> > != ACLCHECK_OK)
> > elog(ERROR, "%s.%s: %s",
> > seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> > diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> > --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> > +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> > @@ -228,8 +228,15 @@
> > case CMD_INSERT:
> > reqperm = ACL_AP;
> > break;
> > + case CMD_DELETE:
> > + reqperm = ACL_DE;
> > + break;
> > + case CMD_UPDATE:
> > + reqperm = ACL_UP;
> > + break;
> > default:
> > - reqperm = ACL_WR;
> > + /* is it The Right Thing To Do (tm) ? */
> > + reqperm = ACL_AP | ACL_DE | ACL_UP;
> > break;
> > }
> > else
> > diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> > --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> > +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> > @@ -2282,8 +2282,15 @@
> > case CMD_INSERT:
> > reqperm = ACL_AP;
> > break;
> > + case CMD_DELETE:
> > + reqperm = ACL_DE;
> > + break;
> > + case CMD_UPDATE:
> > + reqperm = ACL_UP;
> > + break;
> > default:
> > - reqperm = ACL_WR;
> > + /* is it The Right Thing To Do (tm) ? */
> > + reqperm = ACL_AP | ACL_DE | ACL_UP;
> > break;
> > }
> >
> > diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> > diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> > --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> > +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> > @@ -154,8 +154,11 @@
> > case ACL_MODE_RD_CHR:
> > aip->ai_mode |= ACL_RD;
> > break;
> > - case ACL_MODE_WR_CHR:
> > - aip->ai_mode |= ACL_WR;
> > + case ACL_MODE_DE_CHR:
> > + aip->ai_mode |= ACL_DE;
> > + break;
> > + case ACL_MODE_UP_CHR:
> > + aip->ai_mode |= ACL_UP;
> > break;
> > case ACL_MODE_RU_CHR:
> > aip->ai_mode |= ACL_RU;
> > @@ -272,7 +275,7 @@
> > if (!aip)
> > aip = &default_aclitem;
> >
> > - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> > + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> > if (!out)
> > elog(ERROR, "aclitemout: palloc failed");
> > *p = '\0';
> > @@ -605,9 +608,8 @@
> > int i;
> > int l;
> >
> > - Assert(strlen(old_privlist) < 5);
> > - priv = palloc(5); /* at most "rwaR" */ ;
> > -
> > + Assert(strlen(old_privlist) < 6);
> > + priv = palloc(6); /* at most "arduR" */ ;
> > if (old_privlist == NULL || old_privlist[0] == '\0')
> > {
> > priv[0] = new_priv;
> > @@ -619,7 +621,7 @@
> >
> > l = strlen(old_privlist);
> >
> > - if (l == 4)
> > + if (l == 5)
> > { /* can't add any more privileges */
> > return priv;
> > }
> > diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> > --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> > +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> > @@ -54,9 +54,10 @@
> > #define ACL_NO 0 /* no permissions */
> > #define ACL_AP (1<<0) /* append */
> > #define ACL_RD (1<<1) /* read */
> > -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> > -#define ACL_RU (1<<3) /* place rules */
> > -#define N_ACL_MODES 4
> > +#define ACL_DE (1<<2) /* delete */
> > +#define ACL_UP (1<<3) /* update/replace */
> > +#define ACL_RU (1<<4) /* place rules */
> > +#define N_ACL_MODES 5
> >
> > #define ACL_MODECHG_ADD 1
> > #define ACL_MODECHG_DEL 2
> > @@ -65,7 +66,8 @@
> > /* change this line if you want to set the default acl permission */
> > #define ACL_WORLD_DEFAULT (ACL_NO)
> > /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> > -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> > +
> > +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
> >
> > /*
> > * AclItem
> > @@ -118,10 +120,12 @@
> > #define ACL_MODECHG_ADD_CHR '+'
> > #define ACL_MODECHG_DEL_CHR '-'
> > #define ACL_MODECHG_EQL_CHR '='
> > -#define ACL_MODE_STR "arwR" /* list of valid characters */
> > +
> > +#define ACL_MODE_STR "arduR" /* list of valid characters */
> > #define ACL_MODE_AP_CHR 'a'
> > #define ACL_MODE_RD_CHR 'r'
> > -#define ACL_MODE_WR_CHR 'w'
> > +#define ACL_MODE_DE_CHR 'd'
> > +#define ACL_MODE_UP_CHR 'u'
> > #define ACL_MODE_RU_CHR 'R'
> >
> > /* result codes for pg_aclcheck */
> >
>
>
>
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
I tried to apply this patch to the current tree, but unfortunately,
changes made in permission handling prevent it from being applied.
Seems we were too far into testing to apply this long ago, and now we
are too far away from the original patch to apply it now. If you are
still intersted, we would like to get this patch against the current
source tree.
Sorry this got lost in the patch process for so long.
> Hi,
>
> first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> nor version dependent AFAIK.
>
> I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> fact that update and insert are considered the same thing when you modify
> permissions with grant and revoke. (Maybe it was the wrong place to post
> it.)
>
> for example a "grant delete" also grants "update" which is completely
> wrong. More importantly the user is not informed, and this could lead to
> VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> update existing records, have the permission to delete all records...
>
> I've read postgresql documentation, especially the grant and revoke
> manpages, and I've found no mention of this bug, which is IMHO a Big
> Mistake (tm).
>
> attached to this message you'll find a patch for version 6.5.2 wich
> differentiate delete and update, because before they were considered as
> "write". The patch only modifies .c .y and .h files, but no documentation.
>
> the new acl rights look like: arRdu
> a for append
> r for read
> R for rules
> d for delete
> u for update
>
> instead of: arwR
> a for append
> r for read
> w for update AND delete
> R for rules
>
> This patch seems to work at least with what I've tested, you'll find a
> test session at the end of this message.
>
> I hope this patch will help and that it will be easy to incorporate it in
> 7.0, which I haven't the time to do for now.
>
> And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> user's acl in the database, and the deleted user id being reused, I've not
> done anything, but I consider this a major problem. Please consider it for
> a next version.
>
> Because I'm not an expert, I suggest you remove gram.c before applying the
> patch, in order for this file to be generated again from gram.y, but maybe
> this is not necessary.
>
> I'd be very pleased if some people could test this more than I can,
> because I don't use postgresql intensively with special permissions.
>
> I'm not sure for some parts of the patch, especially in execMain.c
> so if a postgresql hacker could examine it, this would be fine.
>
> dump of test session:
> ---------------------
>
> ------- CUT -------
>
> template1=> create database db;
> CREATEDB
> template1=> create user john;
> CREATE USER
> template1=> \connect db
> connecting to new database: db
> db=> create table t (id INT4, name TEXT);
> CREATE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | |
> +----------+--------------------------+
> db=> grant all on t to john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=arduR"} |
> +----------+--------------------------+
> db=> \connect db john
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18560 1
> db=> update t set name = 'yyy' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|yyy
> (1 row)
>
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18561 1
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke update on t from john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=ardR"} |
> +----------+--------------------------+
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (2, 'yyy');
> INSERT 18592 1
> db=> update t set name='modified by john' where id=2;
> ERROR: t: Permission denied.
> db=> delete from t where id=2;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> 1|xxx
> (1 row)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke insert on t from john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=rdR"} |
> +----------+--------------------------+
> db=> insert into t (id, name) values (3, 'I try to insert something');
> ERROR: t: Permission denied.
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18624 1
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> update t set name='john' where id =1;
> ERROR: t: Permission denied.
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke delete on t from john;
> CHANGE
> db=> grant update on t to john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> delete from t;
> ERROR: t: Permission denied.
> db=> update t set name='john' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|john
> (1 row)
>
> ------- CUT -------
>
> Thank you for reading.
>
> bye,
>
> Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Content-Description: the 6.5.2 patch
> diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> @@ -381,7 +381,7 @@
> * pg_database table, there is still additional permissions
> * checking in dbcommands.c
> */
> - if ((mode & ACL_WR) || (mode & ACL_AP))
> + if (mode & ACL_AP)
> return ACLCHECK_OK;
> }
>
> @@ -390,7 +390,7 @@
> * pg_shadow.usecatupd is set. (This is to let superusers protect
> * themselves from themselves.)
> */
> - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> + if ((mode & ACL_AP) &&
> !allowSystemTableMods && IsSystemRelationName(relname) &&
> !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> {
> diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> @@ -524,7 +524,9 @@
> if (lockstmt->mode == AccessShareLock)
> aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> else
> - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> + /* do we really need to have all these permissions at the same time ? */
> + /* shouldn't we test lockstmt->mode first ? */
> + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
>
> if (aclresult != ACLCHECK_OK)
> elog(ERROR, "LOCK TABLE: permission denied");
> diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> @@ -242,7 +242,8 @@
> FILE *fp;
> Relation rel;
> extern char *UserName; /* defined in global.c */
> - const AclMode required_access = from ? ACL_WR : ACL_RD;
> + /* why should we need other permissions than APPEND ? */
> + const AclMode required_access = from ? ACL_AP : ACL_RD;
> int result;
>
> rel = heap_openr(relname);
> diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c postgresql-6.5.2-patched/src/backend/commands/sequence.c
> --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> @@ -314,7 +314,8 @@
> Form_pg_sequence seq;
>
> #ifndef NO_SECURITY
> - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE permission ? */
> + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> seqname, seqname);
> #endif
> diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> @@ -115,7 +115,7 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> @@ -227,7 +227,8 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> @@ -329,11 +330,12 @@
> BeginTransactionBlock();
>
> /*
> - * Make sure the user attempting to create a user can delete from the
> + * Make sure the user attempting to delete a user can delete from the
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than DELETE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c postgresql-6.5.2-patched/src/backend/executor/execMain.c
> --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> @@ -464,14 +464,16 @@
> switch (operation)
> {
> case CMD_INSERT:
> - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> opstr = "append";
> break;
> case CMD_DELETE:
> + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> + opstr = "delete";
> + break;
> case CMD_UPDATE:
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> + opstr = "update";
> break;
> default:
> elog(ERROR, "ExecCheckPerms: bogus operation %d",
> @@ -508,8 +510,9 @@
> StrNCpy(rname.data,
> ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> NAMEDATALEN);
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + /* is it the right thing to do ? */
> + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> + opstr = "write"; /* unused ? */
> if (!ok)
> elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> }
> diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> @@ -1694,11 +1694,11 @@
>
> privileges: ALL PRIVILEGES
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | ALL
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | operation_commalist
> {
> @@ -1726,11 +1726,11 @@
> }
> | UPDATE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_UP_CHR;
> }
> | DELETE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_DE_CHR;
> }
> | RULE
> {
> diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> @@ -29,236 +29,236 @@
> RuleStmt *rstmt;
> InsertStmt *astmt;
> } YYSTYPE;
> -#define ABSOLUTE 257
> -#define ACTION 258
> -#define ADD 259
> -#define ALL 260
> -#define ALTER 261
> -#define AND 262
> -#define ANY 263
> -#define AS 264
> -#define ASC 265
> -#define BEGIN_TRANS 266
> -#define BETWEEN 267
> -#define BOTH 268
> -#define BY 269
> -#define CASCADE 270
> -#define CASE 271
> -#define CAST 272
> -#define CHAR 273
> -#define CHARACTER 274
> -#define CHECK 275
> -#define CLOSE 276
> -#define COALESCE 277
> -#define COLLATE 278
> -#define COLUMN 279
> -#define COMMIT 280
> -#define CONSTRAINT 281
> -#define CREATE 282
> -#define CROSS 283
> -#define CURRENT 284
> -#define CURRENT_DATE 285
> -#define CURRENT_TIME 286
> -#define CURRENT_TIMESTAMP 287
> -#define CURRENT_USER 288
> -#define CURSOR 289
> -#define DAY_P 290
> -#define DECIMAL 291
> -#define DECLARE 292
> -#define DEFAULT 293
> -#define DELETE 294
> -#define DESC 295
> -#define DISTINCT 296
> -#define DOUBLE 297
> -#define DROP 298
> -#define ELSE 299
> -#define END_TRANS 300
> -#define EXCEPT 301
> -#define EXECUTE 302
> -#define EXISTS 303
> -#define EXTRACT 304
> -#define FALSE_P 305
> -#define FETCH 306
> -#define FLOAT 307
> -#define FOR 308
> -#define FOREIGN 309
> -#define FROM 310
> -#define FULL 311
> -#define GLOBAL 312
> -#define GRANT 313
> -#define GROUP 314
> -#define HAVING 315
> -#define HOUR_P 316
> -#define IN 317
> -#define INNER_P 318
> -#define INSENSITIVE 319
> -#define INSERT 320
> -#define INTERSECT 321
> -#define INTERVAL 322
> -#define INTO 323
> -#define IS 324
> -#define ISOLATION 325
> -#define JOIN 326
> -#define KEY 327
> -#define LANGUAGE 328
> -#define LEADING 329
> -#define LEFT 330
> -#define LEVEL 331
> -#define LIKE 332
> -#define LOCAL 333
> -#define MATCH 334
> -#define MINUTE_P 335
> -#define MONTH_P 336
> -#define NAMES 337
> -#define NATIONAL 338
> -#define NATURAL 339
> -#define NCHAR 340
> -#define NEXT 341
> -#define NO 342
> -#define NOT 343
> -#define NULLIF 344
> -#define NULL_P 345
> -#define NUMERIC 346
> -#define OF 347
> -#define ON 348
> -#define ONLY 349
> -#define OPTION 350
> -#define OR 351
> -#define ORDER 352
> -#define OUTER_P 353
> -#define PARTIAL 354
> -#define POSITION 355
> -#define PRECISION 356
> -#define PRIMARY 357
> -#define PRIOR 358
> -#define PRIVILEGES 359
> -#define PROCEDURE 360
> -#define PUBLIC 361
> -#define READ 362
> -#define REFERENCES 363
> -#define RELATIVE 364
> -#define REVOKE 365
> -#define RIGHT 366
> -#define ROLLBACK 367
> -#define SCROLL 368
> -#define SECOND_P 369
> -#define SELECT 370
> -#define SET 371
> -#define SUBSTRING 372
> -#define TABLE 373
> -#define TEMP 374
> -#define TEMPORARY 375
> -#define THEN 376
> -#define TIME 377
> -#define TIMESTAMP 378
> -#define TIMEZONE_HOUR 379
> -#define TIMEZONE_MINUTE 380
> -#define TO 381
> -#define TRAILING 382
> -#define TRANSACTION 383
> -#define TRIM 384
> -#define TRUE_P 385
> -#define UNION 386
> -#define UNIQUE 387
> -#define UPDATE 388
> -#define USER 389
> -#define USING 390
> -#define VALUES 391
> -#define VARCHAR 392
> -#define VARYING 393
> -#define VIEW 394
> -#define WHEN 395
> -#define WHERE 396
> -#define WITH 397
> -#define WORK 398
> -#define YEAR_P 399
> -#define ZONE 400
> -#define TRIGGER 401
> -#define COMMITTED 402
> -#define SERIALIZABLE 403
> -#define TYPE_P 404
> -#define ABORT_TRANS 405
> -#define ACCESS 406
> -#define AFTER 407
> -#define AGGREGATE 408
> -#define ANALYZE 409
> -#define BACKWARD 410
> -#define BEFORE 411
> -#define BINARY 412
> -#define CACHE 413
> -#define CLUSTER 414
> -#define COPY 415
> -#define CREATEDB 416
> -#define CREATEUSER 417
> -#define CYCLE 418
> -#define DATABASE 419
> -#define DELIMITERS 420
> -#define DO 421
> -#define EACH 422
> -#define ENCODING 423
> -#define EXCLUSIVE 424
> -#define EXPLAIN 425
> -#define EXTEND 426
> -#define FORWARD 427
> -#define FUNCTION 428
> -#define HANDLER 429
> -#define INCREMENT 430
> -#define INDEX 431
> -#define INHERITS 432
> -#define INSTEAD 433
> -#define ISNULL 434
> -#define LANCOMPILER 435
> -#define LIMIT 436
> -#define LISTEN 437
> -#define LOAD 438
> -#define LOCATION 439
> -#define LOCK_P 440
> -#define MAXVALUE 441
> -#define MINVALUE 442
> -#define MODE 443
> -#define MOVE 444
> -#define NEW 445
> -#define NOCREATEDB 446
> -#define NOCREATEUSER 447
> -#define NONE 448
> -#define NOTHING 449
> -#define NOTIFY 450
> -#define NOTNULL 451
> -#define OFFSET 452
> -#define OIDS 453
> -#define OPERATOR 454
> -#define PASSWORD 455
> -#define PROCEDURAL 456
> -#define RENAME 457
> -#define RESET 458
> -#define RETURNS 459
> -#define ROW 460
> -#define RULE 461
> -#define SEQUENCE 462
> -#define SERIAL 463
> -#define SETOF 464
> -#define SHARE 465
> -#define SHOW 466
> -#define START 467
> -#define STATEMENT 468
> -#define STDIN 469
> -#define STDOUT 470
> -#define TRUSTED 471
> -#define UNLISTEN 472
> -#define UNTIL 473
> -#define VACUUM 474
> -#define VALID 475
> -#define VERBOSE 476
> -#define VERSION 477
> -#define IDENT 478
> -#define SCONST 479
> -#define Op 480
> -#define ICONST 481
> -#define PARAM 482
> -#define FCONST 483
> -#define OP 484
> -#define UMINUS 485
> -#define TYPECAST 486
> +#define ABSOLUTE 258
> +#define ACTION 259
> +#define ADD 260
> +#define ALL 261
> +#define ALTER 262
> +#define AND 263
> +#define ANY 264
> +#define AS 265
> +#define ASC 266
> +#define BEGIN_TRANS 267
> +#define BETWEEN 268
> +#define BOTH 269
> +#define BY 270
> +#define CASCADE 271
> +#define CASE 272
> +#define CAST 273
> +#define CHAR 274
> +#define CHARACTER 275
> +#define CHECK 276
> +#define CLOSE 277
> +#define COALESCE 278
> +#define COLLATE 279
> +#define COLUMN 280
> +#define COMMIT 281
> +#define CONSTRAINT 282
> +#define CREATE 283
> +#define CROSS 284
> +#define CURRENT 285
> +#define CURRENT_DATE 286
> +#define CURRENT_TIME 287
> +#define CURRENT_TIMESTAMP 288
> +#define CURRENT_USER 289
> +#define CURSOR 290
> +#define DAY_P 291
> +#define DECIMAL 292
> +#define DECLARE 293
> +#define DEFAULT 294
> +#define DELETE 295
> +#define DESC 296
> +#define DISTINCT 297
> +#define DOUBLE 298
> +#define DROP 299
> +#define ELSE 300
> +#define END_TRANS 301
> +#define EXCEPT 302
> +#define EXECUTE 303
> +#define EXISTS 304
> +#define EXTRACT 305
> +#define FALSE_P 306
> +#define FETCH 307
> +#define FLOAT 308
> +#define FOR 309
> +#define FOREIGN 310
> +#define FROM 311
> +#define FULL 312
> +#define GLOBAL 313
> +#define GRANT 314
> +#define GROUP 315
> +#define HAVING 316
> +#define HOUR_P 317
> +#define IN 318
> +#define INNER_P 319
> +#define INSENSITIVE 320
> +#define INSERT 321
> +#define INTERSECT 322
> +#define INTERVAL 323
> +#define INTO 324
> +#define IS 325
> +#define ISOLATION 326
> +#define JOIN 327
> +#define KEY 328
> +#define LANGUAGE 329
> +#define LEADING 330
> +#define LEFT 331
> +#define LEVEL 332
> +#define LIKE 333
> +#define LOCAL 334
> +#define MATCH 335
> +#define MINUTE_P 336
> +#define MONTH_P 337
> +#define NAMES 338
> +#define NATIONAL 339
> +#define NATURAL 340
> +#define NCHAR 341
> +#define NEXT 342
> +#define NO 343
> +#define NOT 344
> +#define NULLIF 345
> +#define NULL_P 346
> +#define NUMERIC 347
> +#define OF 348
> +#define ON 349
> +#define ONLY 350
> +#define OPTION 351
> +#define OR 352
> +#define ORDER 353
> +#define OUTER_P 354
> +#define PARTIAL 355
> +#define POSITION 356
> +#define PRECISION 357
> +#define PRIMARY 358
> +#define PRIOR 359
> +#define PRIVILEGES 360
> +#define PROCEDURE 361
> +#define PUBLIC 362
> +#define READ 363
> +#define REFERENCES 364
> +#define RELATIVE 365
> +#define REVOKE 366
> +#define RIGHT 367
> +#define ROLLBACK 368
> +#define SCROLL 369
> +#define SECOND_P 370
> +#define SELECT 371
> +#define SET 372
> +#define SUBSTRING 373
> +#define TABLE 374
> +#define TEMP 375
> +#define TEMPORARY 376
> +#define THEN 377
> +#define TIME 378
> +#define TIMESTAMP 379
> +#define TIMEZONE_HOUR 380
> +#define TIMEZONE_MINUTE 381
> +#define TO 382
> +#define TRAILING 383
> +#define TRANSACTION 384
> +#define TRIM 385
> +#define TRUE_P 386
> +#define UNION 387
> +#define UNIQUE 388
> +#define UPDATE 389
> +#define USER 390
> +#define USING 391
> +#define VALUES 392
> +#define VARCHAR 393
> +#define VARYING 394
> +#define VIEW 395
> +#define WHEN 396
> +#define WHERE 397
> +#define WITH 398
> +#define WORK 399
> +#define YEAR_P 400
> +#define ZONE 401
> +#define TRIGGER 402
> +#define COMMITTED 403
> +#define SERIALIZABLE 404
> +#define TYPE_P 405
> +#define ABORT_TRANS 406
> +#define ACCESS 407
> +#define AFTER 408
> +#define AGGREGATE 409
> +#define ANALYZE 410
> +#define BACKWARD 411
> +#define BEFORE 412
> +#define BINARY 413
> +#define CACHE 414
> +#define CLUSTER 415
> +#define COPY 416
> +#define CREATEDB 417
> +#define CREATEUSER 418
> +#define CYCLE 419
> +#define DATABASE 420
> +#define DELIMITERS 421
> +#define DO 422
> +#define EACH 423
> +#define ENCODING 424
> +#define EXCLUSIVE 425
> +#define EXPLAIN 426
> +#define EXTEND 427
> +#define FORWARD 428
> +#define FUNCTION 429
> +#define HANDLER 430
> +#define INCREMENT 431
> +#define INDEX 432
> +#define INHERITS 433
> +#define INSTEAD 434
> +#define ISNULL 435
> +#define LANCOMPILER 436
> +#define LIMIT 437
> +#define LISTEN 438
> +#define LOAD 439
> +#define LOCATION 440
> +#define LOCK_P 441
> +#define MAXVALUE 442
> +#define MINVALUE 443
> +#define MODE 444
> +#define MOVE 445
> +#define NEW 446
> +#define NOCREATEDB 447
> +#define NOCREATEUSER 448
> +#define NONE 449
> +#define NOTHING 450
> +#define NOTIFY 451
> +#define NOTNULL 452
> +#define OFFSET 453
> +#define OIDS 454
> +#define OPERATOR 455
> +#define PASSWORD 456
> +#define PROCEDURAL 457
> +#define RENAME 458
> +#define RESET 459
> +#define RETURNS 460
> +#define ROW 461
> +#define RULE 462
> +#define SEQUENCE 463
> +#define SERIAL 464
> +#define SETOF 465
> +#define SHARE 466
> +#define SHOW 467
> +#define START 468
> +#define STATEMENT 469
> +#define STDIN 470
> +#define STDOUT 471
> +#define TRUSTED 472
> +#define UNLISTEN 473
> +#define UNTIL 474
> +#define VACUUM 475
> +#define VALID 476
> +#define VERBOSE 477
> +#define VERSION 478
> +#define IDENT 479
> +#define SCONST 480
> +#define Op 481
> +#define ICONST 482
> +#define PARAM 483
> +#define FCONST 484
> +#define OP 485
> +#define UMINUS 486
> +#define TYPECAST 487
>
>
> extern YYSTYPE yylval;
> diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> @@ -601,7 +601,8 @@
>
> if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> - ACL_WR : ACL_RD)))
> + /* if nextval and setval are atomic, which I don't know, update should be enough */
> + ACL_UP : ACL_RD)))
> != ACLCHECK_OK)
> elog(ERROR, "%s.%s: %s",
> seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> @@ -228,8 +228,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
> else
> diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> @@ -2282,8 +2282,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
>
> diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> @@ -154,8 +154,11 @@
> case ACL_MODE_RD_CHR:
> aip->ai_mode |= ACL_RD;
> break;
> - case ACL_MODE_WR_CHR:
> - aip->ai_mode |= ACL_WR;
> + case ACL_MODE_DE_CHR:
> + aip->ai_mode |= ACL_DE;
> + break;
> + case ACL_MODE_UP_CHR:
> + aip->ai_mode |= ACL_UP;
> break;
> case ACL_MODE_RU_CHR:
> aip->ai_mode |= ACL_RU;
> @@ -272,7 +275,7 @@
> if (!aip)
> aip = &default_aclitem;
>
> - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> if (!out)
> elog(ERROR, "aclitemout: palloc failed");
> *p = '\0';
> @@ -605,9 +608,8 @@
> int i;
> int l;
>
> - Assert(strlen(old_privlist) < 5);
> - priv = palloc(5); /* at most "rwaR" */ ;
> -
> + Assert(strlen(old_privlist) < 6);
> + priv = palloc(6); /* at most "arduR" */ ;
> if (old_privlist == NULL || old_privlist[0] == '\0')
> {
> priv[0] = new_priv;
> @@ -619,7 +621,7 @@
>
> l = strlen(old_privlist);
>
> - if (l == 4)
> + if (l == 5)
> { /* can't add any more privileges */
> return priv;
> }
> diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> @@ -54,9 +54,10 @@
> #define ACL_NO 0 /* no permissions */
> #define ACL_AP (1<<0) /* append */
> #define ACL_RD (1<<1) /* read */
> -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> -#define ACL_RU (1<<3) /* place rules */
> -#define N_ACL_MODES 4
> +#define ACL_DE (1<<2) /* delete */
> +#define ACL_UP (1<<3) /* update/replace */
> +#define ACL_RU (1<<4) /* place rules */
> +#define N_ACL_MODES 5
>
> #define ACL_MODECHG_ADD 1
> #define ACL_MODECHG_DEL 2
> @@ -65,7 +66,8 @@
> /* change this line if you want to set the default acl permission */
> #define ACL_WORLD_DEFAULT (ACL_NO)
> /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> +
> +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
>
> /*
> * AclItem
> @@ -118,10 +120,12 @@
> #define ACL_MODECHG_ADD_CHR '+'
> #define ACL_MODECHG_DEL_CHR '-'
> #define ACL_MODECHG_EQL_CHR '='
> -#define ACL_MODE_STR "arwR" /* list of valid characters */
> +
> +#define ACL_MODE_STR "arduR" /* list of valid characters */
> #define ACL_MODE_AP_CHR 'a'
> #define ACL_MODE_RD_CHR 'r'
> -#define ACL_MODE_WR_CHR 'w'
> +#define ACL_MODE_DE_CHR 'd'
> +#define ACL_MODE_UP_CHR 'u'
> #define ACL_MODE_RU_CHR 'R'
>
> /* result codes for pg_aclcheck */
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
On Mon, 2 Oct 2000, Bruce Momjian wrote: > I tried to apply this patch to the current tree, but unfortunately, > changes made in permission handling prevent it from being applied. > > Seems we were too far into testing to apply this long ago, and now we > are too far away from the original patch to apply it now. If you are > still intersted, we would like to get this patch against the current > source tree. Hi, of course I'm still interested. however, as you've already been warned months ago, I don't have any time to do it again for 7.x, I'm sorry. Maybe next year or around December, but now it's impossible. sorry, but not my fault. bye, Jerome Alet
This has been fixed in 7.2beta:
* -Permission to DELETE table also allows UPDATE (Peter E)
---------------------------------------------------------------------------
> Hi,
>
> first I'm sorry to not fill the form, I'm too lazy, and it's not platform
> nor version dependent AFAIK.
>
> I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
> fact that update and insert are considered the same thing when you modify
> permissions with grant and revoke. (Maybe it was the wrong place to post
> it.)
>
> for example a "grant delete" also grants "update" which is completely
> wrong. More importantly the user is not informed, and this could lead to
> VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
> update existing records, have the permission to delete all records...
>
> I've read postgresql documentation, especially the grant and revoke
> manpages, and I've found no mention of this bug, which is IMHO a Big
> Mistake (tm).
>
> attached to this message you'll find a patch for version 6.5.2 wich
> differentiate delete and update, because before they were considered as
> "write". The patch only modifies .c .y and .h files, but no documentation.
>
> the new acl rights look like: arRdu
> a for append
> r for read
> R for rules
> d for delete
> u for update
>
> instead of: arwR
> a for append
> r for read
> w for update AND delete
> R for rules
>
> This patch seems to work at least with what I've tested, you'll find a
> test session at the end of this message.
>
> I hope this patch will help and that it will be easy to incorporate it in
> 7.0, which I haven't the time to do for now.
>
> And for the bug report I posted on Feb 23rd on "drop user" which keeps the
> user's acl in the database, and the deleted user id being reused, I've not
> done anything, but I consider this a major problem. Please consider it for
> a next version.
>
> Because I'm not an expert, I suggest you remove gram.c before applying the
> patch, in order for this file to be generated again from gram.y, but maybe
> this is not necessary.
>
> I'd be very pleased if some people could test this more than I can,
> because I don't use postgresql intensively with special permissions.
>
> I'm not sure for some parts of the patch, especially in execMain.c
> so if a postgresql hacker could examine it, this would be fine.
>
> dump of test session:
> ---------------------
>
> ------- CUT -------
>
> template1=> create database db;
> CREATEDB
> template1=> create user john;
> CREATE USER
> template1=> \connect db
> connecting to new database: db
> db=> create table t (id INT4, name TEXT);
> CREATE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | |
> +----------+--------------------------+
> db=> grant all on t to john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=arduR"} |
> +----------+--------------------------+
> db=> \connect db john
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18560 1
> db=> update t set name = 'yyy' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|yyy
> (1 row)
>
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18561 1
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke update on t from john;
> CHANGE
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=ardR"} |
> +----------+--------------------------+
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> insert into t (id, name) values (2, 'yyy');
> INSERT 18592 1
> db=> update t set name='modified by john' where id=2;
> ERROR: t: Permission denied.
> db=> delete from t where id=2;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> 1|xxx
> (1 row)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke insert on t from john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> \z
> Database = db
> +----------+--------------------------+
> | Relation | Grant/Revoke Permissions |
> +----------+--------------------------+
> | t | {"=","john=rdR"} |
> +----------+--------------------------+
> db=> insert into t (id, name) values (3, 'I try to insert something');
> ERROR: t: Permission denied.
> db=> delete from t;
> DELETE 1
> db=> select * from t;
> id|name
> --+----
> (0 rows)
>
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> insert into t (id, name) values (1, 'xxx');
> INSERT 18624 1
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> update t set name='john' where id =1;
> ERROR: t: Permission denied.
> db=> \connect db postgres
> connecting to new database: db as user: postgres
> db=> revoke delete on t from john;
> CHANGE
> db=> grant update on t to john;
> CHANGE
> db=> \connect db john;
> connecting to new database: db as user: john
> db=> delete from t;
> ERROR: t: Permission denied.
> db=> update t set name='john' where id=1;
> UPDATE 1
> db=> select * from t;
> id|name
> --+----
> 1|john
> (1 row)
>
> ------- CUT -------
>
> Thank you for reading.
>
> bye,
>
> Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
> Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
> 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
Content-Description: the 6.5.2 patch
> diff -urbw postgresql-6.5.2/src/backend/catalog/aclchk.c postgresql-6.5.2-patched/src/backend/catalog/aclchk.c
> --- postgresql-6.5.2/src/backend/catalog/aclchk.c Mon Aug 2 07:56:53 1999
> +++ postgresql-6.5.2-patched/src/backend/catalog/aclchk.c Wed Mar 1 16:39:44 2000
> @@ -381,7 +381,7 @@
> * pg_database table, there is still additional permissions
> * checking in dbcommands.c
> */
> - if ((mode & ACL_WR) || (mode & ACL_AP))
> + if (mode & ACL_AP)
> return ACLCHECK_OK;
> }
>
> @@ -390,7 +390,7 @@
> * pg_shadow.usecatupd is set. (This is to let superusers protect
> * themselves from themselves.)
> */
> - if (((mode & ACL_WR) || (mode & ACL_AP)) &&
> + if ((mode & ACL_AP) &&
> !allowSystemTableMods && IsSystemRelationName(relname) &&
> !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
> {
> diff -urbw postgresql-6.5.2/src/backend/commands/command.c postgresql-6.5.2-patched/src/backend/commands/command.c
> --- postgresql-6.5.2/src/backend/commands/command.c Mon Aug 2 07:56:57 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/command.c Wed Mar 1 16:30:23 2000
> @@ -524,7 +524,9 @@
> if (lockstmt->mode == AccessShareLock)
> aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_RD);
> else
> - aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL_WR);
> + /* do we really need to have all these permissions at the same time ? */
> + /* shouldn't we test lockstmt->mode first ? */
> + aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), (ACL_AP | ACL_DE | ACL_UP));
>
> if (aclresult != ACLCHECK_OK)
> elog(ERROR, "LOCK TABLE: permission denied");
> diff -urbw postgresql-6.5.2/src/backend/commands/copy.c postgresql-6.5.2-patched/src/backend/commands/copy.c
> --- postgresql-6.5.2/src/backend/commands/copy.c Sat Jul 3 02:32:39 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/copy.c Wed Mar 1 16:30:35 2000
> @@ -242,7 +242,8 @@
> FILE *fp;
> Relation rel;
> extern char *UserName; /* defined in global.c */
> - const AclMode required_access = from ? ACL_WR : ACL_RD;
> + /* why should we need other permissions than APPEND ? */
> + const AclMode required_access = from ? ACL_AP : ACL_RD;
> int result;
>
> rel = heap_openr(relname);
> diff -urbw postgresql-6.5.2/src/backend/commands/sequence.c postgresql-6.5.2-patched/src/backend/commands/sequence.c
> --- postgresql-6.5.2/src/backend/commands/sequence.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/sequence.c Wed Mar 1 16:31:05 2000
> @@ -314,7 +314,8 @@
> Form_pg_sequence seq;
>
> #ifndef NO_SECURITY
> - if (pg_aclcheck(seqname, getpgusername(), ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE permission ? */
> + if (pg_aclcheck(seqname, getpgusername(), ACL_UP) != ACLCHECK_OK)
> elog(ERROR, "%s.setval: you don't have permissions to set sequence %s",
> seqname, seqname);
> #endif
> diff -urbw postgresql-6.5.2/src/backend/commands/user.c postgresql-6.5.2-patched/src/backend/commands/user.c
> --- postgresql-6.5.2/src/backend/commands/user.c Mon Aug 2 07:56:59 1999
> +++ postgresql-6.5.2-patched/src/backend/commands/user.c Wed Mar 1 16:31:38 2000
> @@ -115,7 +115,7 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_AP | ACL_DE | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
> @@ -227,7 +227,8 @@
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than UPDATE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_UP) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
> @@ -329,11 +330,12 @@
> BeginTransactionBlock();
>
> /*
> - * Make sure the user attempting to create a user can delete from the
> + * Make sure the user attempting to delete a user can delete from the
> * pg_shadow relation.
> */
> pg_shadow = GetPgUserName();
> - if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
> + /* why should we need more than DELETE ? */
> + if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_DE) != ACLCHECK_OK)
> {
> UserAbortTransactionBlock();
> elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
> diff -urbw postgresql-6.5.2/src/backend/executor/execMain.c postgresql-6.5.2-patched/src/backend/executor/execMain.c
> --- postgresql-6.5.2/src/backend/executor/execMain.c Thu Jun 17 17:15:49 1999
> +++ postgresql-6.5.2-patched/src/backend/executor/execMain.c Wed Mar 1 18:31:31 2000
> @@ -464,14 +464,16 @@
> switch (operation)
> {
> case CMD_INSERT:
> - ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK) ||
> - ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> + ok = ((aclcheck_result = CHECK(ACL_AP)) == ACLCHECK_OK);
> opstr = "append";
> break;
> case CMD_DELETE:
> + ok = ((aclcheck_result = CHECK(ACL_DE)) == ACLCHECK_OK);
> + opstr = "delete";
> + break;
> case CMD_UPDATE:
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + ok = ((aclcheck_result = CHECK(ACL_UP)) == ACLCHECK_OK);
> + opstr = "update";
> break;
> default:
> elog(ERROR, "ExecCheckPerms: bogus operation %d",
> @@ -508,8 +510,9 @@
> StrNCpy(rname.data,
> ((Form_pg_class) GETSTRUCT(htup))->relname.data,
> NAMEDATALEN);
> - ok = ((aclcheck_result = CHECK(ACL_WR)) == ACLCHECK_OK);
> - opstr = "write";
> + /* is it the right thing to do ? */
> + ok = ((aclcheck_result = CHECK((ACL_AP | ACL_DE | ACL_UP))) == ACLCHECK_OK);
> + opstr = "write"; /* unused ? */
> if (!ok)
> elog(ERROR, "%s: %s", rname.data, aclcheck_error_strings[aclcheck_result]);
> }
> diff -urbw postgresql-6.5.2/src/backend/parser/gram.y postgresql-6.5.2-patched/src/backend/parser/gram.y
> --- postgresql-6.5.2/src/backend/parser/gram.y Tue Sep 14 08:07:35 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/gram.y Wed Mar 1 16:33:34 2000
> @@ -1694,11 +1694,11 @@
>
> privileges: ALL PRIVILEGES
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | ALL
> {
> - $$ = aclmakepriv("rwaR",0);
> + $$ = aclmakepriv("raduR",0);
> }
> | operation_commalist
> {
> @@ -1726,11 +1726,11 @@
> }
> | UPDATE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_UP_CHR;
> }
> | DELETE
> {
> - $$ = ACL_MODE_WR_CHR;
> + $$ = ACL_MODE_DE_CHR;
> }
> | RULE
> {
> diff -urbw postgresql-6.5.2/src/backend/parser/parse.h postgresql-6.5.2-patched/src/backend/parser/parse.h
> --- postgresql-6.5.2/src/backend/parser/parse.h Thu Sep 16 02:23:39 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse.h Wed Mar 1 18:34:46 2000
> @@ -29,236 +29,236 @@
> RuleStmt *rstmt;
> InsertStmt *astmt;
> } YYSTYPE;
> -#define ABSOLUTE 257
> -#define ACTION 258
> -#define ADD 259
> -#define ALL 260
> -#define ALTER 261
> -#define AND 262
> -#define ANY 263
> -#define AS 264
> -#define ASC 265
> -#define BEGIN_TRANS 266
> -#define BETWEEN 267
> -#define BOTH 268
> -#define BY 269
> -#define CASCADE 270
> -#define CASE 271
> -#define CAST 272
> -#define CHAR 273
> -#define CHARACTER 274
> -#define CHECK 275
> -#define CLOSE 276
> -#define COALESCE 277
> -#define COLLATE 278
> -#define COLUMN 279
> -#define COMMIT 280
> -#define CONSTRAINT 281
> -#define CREATE 282
> -#define CROSS 283
> -#define CURRENT 284
> -#define CURRENT_DATE 285
> -#define CURRENT_TIME 286
> -#define CURRENT_TIMESTAMP 287
> -#define CURRENT_USER 288
> -#define CURSOR 289
> -#define DAY_P 290
> -#define DECIMAL 291
> -#define DECLARE 292
> -#define DEFAULT 293
> -#define DELETE 294
> -#define DESC 295
> -#define DISTINCT 296
> -#define DOUBLE 297
> -#define DROP 298
> -#define ELSE 299
> -#define END_TRANS 300
> -#define EXCEPT 301
> -#define EXECUTE 302
> -#define EXISTS 303
> -#define EXTRACT 304
> -#define FALSE_P 305
> -#define FETCH 306
> -#define FLOAT 307
> -#define FOR 308
> -#define FOREIGN 309
> -#define FROM 310
> -#define FULL 311
> -#define GLOBAL 312
> -#define GRANT 313
> -#define GROUP 314
> -#define HAVING 315
> -#define HOUR_P 316
> -#define IN 317
> -#define INNER_P 318
> -#define INSENSITIVE 319
> -#define INSERT 320
> -#define INTERSECT 321
> -#define INTERVAL 322
> -#define INTO 323
> -#define IS 324
> -#define ISOLATION 325
> -#define JOIN 326
> -#define KEY 327
> -#define LANGUAGE 328
> -#define LEADING 329
> -#define LEFT 330
> -#define LEVEL 331
> -#define LIKE 332
> -#define LOCAL 333
> -#define MATCH 334
> -#define MINUTE_P 335
> -#define MONTH_P 336
> -#define NAMES 337
> -#define NATIONAL 338
> -#define NATURAL 339
> -#define NCHAR 340
> -#define NEXT 341
> -#define NO 342
> -#define NOT 343
> -#define NULLIF 344
> -#define NULL_P 345
> -#define NUMERIC 346
> -#define OF 347
> -#define ON 348
> -#define ONLY 349
> -#define OPTION 350
> -#define OR 351
> -#define ORDER 352
> -#define OUTER_P 353
> -#define PARTIAL 354
> -#define POSITION 355
> -#define PRECISION 356
> -#define PRIMARY 357
> -#define PRIOR 358
> -#define PRIVILEGES 359
> -#define PROCEDURE 360
> -#define PUBLIC 361
> -#define READ 362
> -#define REFERENCES 363
> -#define RELATIVE 364
> -#define REVOKE 365
> -#define RIGHT 366
> -#define ROLLBACK 367
> -#define SCROLL 368
> -#define SECOND_P 369
> -#define SELECT 370
> -#define SET 371
> -#define SUBSTRING 372
> -#define TABLE 373
> -#define TEMP 374
> -#define TEMPORARY 375
> -#define THEN 376
> -#define TIME 377
> -#define TIMESTAMP 378
> -#define TIMEZONE_HOUR 379
> -#define TIMEZONE_MINUTE 380
> -#define TO 381
> -#define TRAILING 382
> -#define TRANSACTION 383
> -#define TRIM 384
> -#define TRUE_P 385
> -#define UNION 386
> -#define UNIQUE 387
> -#define UPDATE 388
> -#define USER 389
> -#define USING 390
> -#define VALUES 391
> -#define VARCHAR 392
> -#define VARYING 393
> -#define VIEW 394
> -#define WHEN 395
> -#define WHERE 396
> -#define WITH 397
> -#define WORK 398
> -#define YEAR_P 399
> -#define ZONE 400
> -#define TRIGGER 401
> -#define COMMITTED 402
> -#define SERIALIZABLE 403
> -#define TYPE_P 404
> -#define ABORT_TRANS 405
> -#define ACCESS 406
> -#define AFTER 407
> -#define AGGREGATE 408
> -#define ANALYZE 409
> -#define BACKWARD 410
> -#define BEFORE 411
> -#define BINARY 412
> -#define CACHE 413
> -#define CLUSTER 414
> -#define COPY 415
> -#define CREATEDB 416
> -#define CREATEUSER 417
> -#define CYCLE 418
> -#define DATABASE 419
> -#define DELIMITERS 420
> -#define DO 421
> -#define EACH 422
> -#define ENCODING 423
> -#define EXCLUSIVE 424
> -#define EXPLAIN 425
> -#define EXTEND 426
> -#define FORWARD 427
> -#define FUNCTION 428
> -#define HANDLER 429
> -#define INCREMENT 430
> -#define INDEX 431
> -#define INHERITS 432
> -#define INSTEAD 433
> -#define ISNULL 434
> -#define LANCOMPILER 435
> -#define LIMIT 436
> -#define LISTEN 437
> -#define LOAD 438
> -#define LOCATION 439
> -#define LOCK_P 440
> -#define MAXVALUE 441
> -#define MINVALUE 442
> -#define MODE 443
> -#define MOVE 444
> -#define NEW 445
> -#define NOCREATEDB 446
> -#define NOCREATEUSER 447
> -#define NONE 448
> -#define NOTHING 449
> -#define NOTIFY 450
> -#define NOTNULL 451
> -#define OFFSET 452
> -#define OIDS 453
> -#define OPERATOR 454
> -#define PASSWORD 455
> -#define PROCEDURAL 456
> -#define RENAME 457
> -#define RESET 458
> -#define RETURNS 459
> -#define ROW 460
> -#define RULE 461
> -#define SEQUENCE 462
> -#define SERIAL 463
> -#define SETOF 464
> -#define SHARE 465
> -#define SHOW 466
> -#define START 467
> -#define STATEMENT 468
> -#define STDIN 469
> -#define STDOUT 470
> -#define TRUSTED 471
> -#define UNLISTEN 472
> -#define UNTIL 473
> -#define VACUUM 474
> -#define VALID 475
> -#define VERBOSE 476
> -#define VERSION 477
> -#define IDENT 478
> -#define SCONST 479
> -#define Op 480
> -#define ICONST 481
> -#define PARAM 482
> -#define FCONST 483
> -#define OP 484
> -#define UMINUS 485
> -#define TYPECAST 486
> +#define ABSOLUTE 258
> +#define ACTION 259
> +#define ADD 260
> +#define ALL 261
> +#define ALTER 262
> +#define AND 263
> +#define ANY 264
> +#define AS 265
> +#define ASC 266
> +#define BEGIN_TRANS 267
> +#define BETWEEN 268
> +#define BOTH 269
> +#define BY 270
> +#define CASCADE 271
> +#define CASE 272
> +#define CAST 273
> +#define CHAR 274
> +#define CHARACTER 275
> +#define CHECK 276
> +#define CLOSE 277
> +#define COALESCE 278
> +#define COLLATE 279
> +#define COLUMN 280
> +#define COMMIT 281
> +#define CONSTRAINT 282
> +#define CREATE 283
> +#define CROSS 284
> +#define CURRENT 285
> +#define CURRENT_DATE 286
> +#define CURRENT_TIME 287
> +#define CURRENT_TIMESTAMP 288
> +#define CURRENT_USER 289
> +#define CURSOR 290
> +#define DAY_P 291
> +#define DECIMAL 292
> +#define DECLARE 293
> +#define DEFAULT 294
> +#define DELETE 295
> +#define DESC 296
> +#define DISTINCT 297
> +#define DOUBLE 298
> +#define DROP 299
> +#define ELSE 300
> +#define END_TRANS 301
> +#define EXCEPT 302
> +#define EXECUTE 303
> +#define EXISTS 304
> +#define EXTRACT 305
> +#define FALSE_P 306
> +#define FETCH 307
> +#define FLOAT 308
> +#define FOR 309
> +#define FOREIGN 310
> +#define FROM 311
> +#define FULL 312
> +#define GLOBAL 313
> +#define GRANT 314
> +#define GROUP 315
> +#define HAVING 316
> +#define HOUR_P 317
> +#define IN 318
> +#define INNER_P 319
> +#define INSENSITIVE 320
> +#define INSERT 321
> +#define INTERSECT 322
> +#define INTERVAL 323
> +#define INTO 324
> +#define IS 325
> +#define ISOLATION 326
> +#define JOIN 327
> +#define KEY 328
> +#define LANGUAGE 329
> +#define LEADING 330
> +#define LEFT 331
> +#define LEVEL 332
> +#define LIKE 333
> +#define LOCAL 334
> +#define MATCH 335
> +#define MINUTE_P 336
> +#define MONTH_P 337
> +#define NAMES 338
> +#define NATIONAL 339
> +#define NATURAL 340
> +#define NCHAR 341
> +#define NEXT 342
> +#define NO 343
> +#define NOT 344
> +#define NULLIF 345
> +#define NULL_P 346
> +#define NUMERIC 347
> +#define OF 348
> +#define ON 349
> +#define ONLY 350
> +#define OPTION 351
> +#define OR 352
> +#define ORDER 353
> +#define OUTER_P 354
> +#define PARTIAL 355
> +#define POSITION 356
> +#define PRECISION 357
> +#define PRIMARY 358
> +#define PRIOR 359
> +#define PRIVILEGES 360
> +#define PROCEDURE 361
> +#define PUBLIC 362
> +#define READ 363
> +#define REFERENCES 364
> +#define RELATIVE 365
> +#define REVOKE 366
> +#define RIGHT 367
> +#define ROLLBACK 368
> +#define SCROLL 369
> +#define SECOND_P 370
> +#define SELECT 371
> +#define SET 372
> +#define SUBSTRING 373
> +#define TABLE 374
> +#define TEMP 375
> +#define TEMPORARY 376
> +#define THEN 377
> +#define TIME 378
> +#define TIMESTAMP 379
> +#define TIMEZONE_HOUR 380
> +#define TIMEZONE_MINUTE 381
> +#define TO 382
> +#define TRAILING 383
> +#define TRANSACTION 384
> +#define TRIM 385
> +#define TRUE_P 386
> +#define UNION 387
> +#define UNIQUE 388
> +#define UPDATE 389
> +#define USER 390
> +#define USING 391
> +#define VALUES 392
> +#define VARCHAR 393
> +#define VARYING 394
> +#define VIEW 395
> +#define WHEN 396
> +#define WHERE 397
> +#define WITH 398
> +#define WORK 399
> +#define YEAR_P 400
> +#define ZONE 401
> +#define TRIGGER 402
> +#define COMMITTED 403
> +#define SERIALIZABLE 404
> +#define TYPE_P 405
> +#define ABORT_TRANS 406
> +#define ACCESS 407
> +#define AFTER 408
> +#define AGGREGATE 409
> +#define ANALYZE 410
> +#define BACKWARD 411
> +#define BEFORE 412
> +#define BINARY 413
> +#define CACHE 414
> +#define CLUSTER 415
> +#define COPY 416
> +#define CREATEDB 417
> +#define CREATEUSER 418
> +#define CYCLE 419
> +#define DATABASE 420
> +#define DELIMITERS 421
> +#define DO 422
> +#define EACH 423
> +#define ENCODING 424
> +#define EXCLUSIVE 425
> +#define EXPLAIN 426
> +#define EXTEND 427
> +#define FORWARD 428
> +#define FUNCTION 429
> +#define HANDLER 430
> +#define INCREMENT 431
> +#define INDEX 432
> +#define INHERITS 433
> +#define INSTEAD 434
> +#define ISNULL 435
> +#define LANCOMPILER 436
> +#define LIMIT 437
> +#define LISTEN 438
> +#define LOAD 439
> +#define LOCATION 440
> +#define LOCK_P 441
> +#define MAXVALUE 442
> +#define MINVALUE 443
> +#define MODE 444
> +#define MOVE 445
> +#define NEW 446
> +#define NOCREATEDB 447
> +#define NOCREATEUSER 448
> +#define NONE 449
> +#define NOTHING 450
> +#define NOTIFY 451
> +#define NOTNULL 452
> +#define OFFSET 453
> +#define OIDS 454
> +#define OPERATOR 455
> +#define PASSWORD 456
> +#define PROCEDURAL 457
> +#define RENAME 458
> +#define RESET 459
> +#define RETURNS 460
> +#define ROW 461
> +#define RULE 462
> +#define SEQUENCE 463
> +#define SERIAL 464
> +#define SETOF 465
> +#define SHARE 466
> +#define SHOW 467
> +#define START 468
> +#define STATEMENT 469
> +#define STDIN 470
> +#define STDOUT 471
> +#define TRUSTED 472
> +#define UNLISTEN 473
> +#define UNTIL 474
> +#define VACUUM 475
> +#define VALID 476
> +#define VERBOSE 477
> +#define VERSION 478
> +#define IDENT 479
> +#define SCONST 480
> +#define Op 481
> +#define ICONST 482
> +#define PARAM 483
> +#define FCONST 484
> +#define OP 485
> +#define UMINUS 486
> +#define TYPECAST 487
>
>
> extern YYSTYPE yylval;
> diff -urbw postgresql-6.5.2/src/backend/parser/parse_func.c postgresql-6.5.2-patched/src/backend/parser/parse_func.c
> --- postgresql-6.5.2/src/backend/parser/parse_func.c Fri Jun 18 00:21:40 1999
> +++ postgresql-6.5.2-patched/src/backend/parser/parse_func.c Wed Mar 1 16:33:53 2000
> @@ -601,7 +601,8 @@
>
> if ((aclcheck_result = pg_aclcheck(seqrel, GetPgUserName(),
> (((funcid == F_NEXTVAL) || (funcid == F_SETVAL)) ?
> - ACL_WR : ACL_RD)))
> + /* if nextval and setval are atomic, which I don't know, update should be enough */
> + ACL_UP : ACL_RD)))
> != ACLCHECK_OK)
> elog(ERROR, "%s.%s: %s",
> seqrel, funcname, aclcheck_error_strings[aclcheck_result]);
> diff -urbw postgresql-6.5.2/src/backend/rewrite/locks.c postgresql-6.5.2-patched/src/backend/rewrite/locks.c
> --- postgresql-6.5.2/src/backend/rewrite/locks.c Sun Feb 14 00:17:44 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/locks.c Wed Mar 1 16:34:20 2000
> @@ -228,8 +228,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
> else
> diff -urbw postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c
postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c
> --- postgresql-6.5.2/src/backend/rewrite/rewriteHandler.c Sun Jul 11 19:54:30 1999
> +++ postgresql-6.5.2-patched/src/backend/rewrite/rewriteHandler.c Wed Mar 1 16:35:01 2000
> @@ -2282,8 +2282,15 @@
> case CMD_INSERT:
> reqperm = ACL_AP;
> break;
> + case CMD_DELETE:
> + reqperm = ACL_DE;
> + break;
> + case CMD_UPDATE:
> + reqperm = ACL_UP;
> + break;
> default:
> - reqperm = ACL_WR;
> + /* is it The Right Thing To Do (tm) ? */
> + reqperm = ACL_AP | ACL_DE | ACL_UP;
> break;
> }
>
> diff -urbw postgresql-6.5.2/src/backend/storage/file/fd.c postgresql-6.5.2-patched/src/backend/storage/file/fd.c
> diff -urbw postgresql-6.5.2/src/backend/utils/adt/acl.c postgresql-6.5.2-patched/src/backend/utils/adt/acl.c
> --- postgresql-6.5.2/src/backend/utils/adt/acl.c Mon Aug 2 07:24:49 1999
> +++ postgresql-6.5.2-patched/src/backend/utils/adt/acl.c Wed Mar 1 16:35:53 2000
> @@ -154,8 +154,11 @@
> case ACL_MODE_RD_CHR:
> aip->ai_mode |= ACL_RD;
> break;
> - case ACL_MODE_WR_CHR:
> - aip->ai_mode |= ACL_WR;
> + case ACL_MODE_DE_CHR:
> + aip->ai_mode |= ACL_DE;
> + break;
> + case ACL_MODE_UP_CHR:
> + aip->ai_mode |= ACL_UP;
> break;
> case ACL_MODE_RU_CHR:
> aip->ai_mode |= ACL_RU;
> @@ -272,7 +275,7 @@
> if (!aip)
> aip = &default_aclitem;
>
> - p = out = palloc(strlen("group =arwR ") + 1 + NAMEDATALEN);
> + p = out = palloc(strlen("group =arRdu ") + 1 + NAMEDATALEN);
> if (!out)
> elog(ERROR, "aclitemout: palloc failed");
> *p = '\0';
> @@ -605,9 +608,8 @@
> int i;
> int l;
>
> - Assert(strlen(old_privlist) < 5);
> - priv = palloc(5); /* at most "rwaR" */ ;
> -
> + Assert(strlen(old_privlist) < 6);
> + priv = palloc(6); /* at most "arduR" */ ;
> if (old_privlist == NULL || old_privlist[0] == '\0')
> {
> priv[0] = new_priv;
> @@ -619,7 +621,7 @@
>
> l = strlen(old_privlist);
>
> - if (l == 4)
> + if (l == 5)
> { /* can't add any more privileges */
> return priv;
> }
> diff -urbw postgresql-6.5.2/src/include/utils/acl.h postgresql-6.5.2-patched/src/include/utils/acl.h
> --- postgresql-6.5.2/src/include/utils/acl.h Fri Jul 30 19:07:22 1999
> +++ postgresql-6.5.2-patched/src/include/utils/acl.h Wed Mar 1 16:40:50 2000
> @@ -54,9 +54,10 @@
> #define ACL_NO 0 /* no permissions */
> #define ACL_AP (1<<0) /* append */
> #define ACL_RD (1<<1) /* read */
> -#define ACL_WR (1<<2) /* write (append/delete/replace) */
> -#define ACL_RU (1<<3) /* place rules */
> -#define N_ACL_MODES 4
> +#define ACL_DE (1<<2) /* delete */
> +#define ACL_UP (1<<3) /* update/replace */
> +#define ACL_RU (1<<4) /* place rules */
> +#define N_ACL_MODES 5
>
> #define ACL_MODECHG_ADD 1
> #define ACL_MODECHG_DEL 2
> @@ -65,7 +66,8 @@
> /* change this line if you want to set the default acl permission */
> #define ACL_WORLD_DEFAULT (ACL_NO)
> /* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
> -#define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU)
> +
> +#define ACL_OWNER_DEFAULT (ACL_AP|ACL_RD|ACL_RU|ACL_DE|ACL_UP)
>
> /*
> * AclItem
> @@ -118,10 +120,12 @@
> #define ACL_MODECHG_ADD_CHR '+'
> #define ACL_MODECHG_DEL_CHR '-'
> #define ACL_MODECHG_EQL_CHR '='
> -#define ACL_MODE_STR "arwR" /* list of valid characters */
> +
> +#define ACL_MODE_STR "arduR" /* list of valid characters */
> #define ACL_MODE_AP_CHR 'a'
> #define ACL_MODE_RD_CHR 'r'
> -#define ACL_MODE_WR_CHR 'w'
> +#define ACL_MODE_DE_CHR 'd'
> +#define ACL_MODE_UP_CHR 'u'
> #define ACL_MODE_RU_CHR 'R'
>
> /* result codes for pg_aclcheck */
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026