Cannot insert into temp tables
I mentioned this the other day on another list. I want to reiterate it
here because I can't seem to get anywhere.
I create a temporary table
=> create temp table foo (bar text);
CREATE
=> insert into foo values ('hi');
ERROR: pg_temp.29112.0: Permission denied.
This apparently happens if and only if the user that executes this has
pg_shadow.usecatupd = 'f'.
I have tried this with the 6.5.1 source rpm bundle, fresh after initdb and
also with a 6.5.0 tar ball installation -- same result. (both on RH Linux
5.2-ish)
A potential reason that this has gone unnoticed so far is that when you
create a user thus:
=> create user joe;
the usecatupd defaults to true (why?).
Also this does not have anything to do with superuser status, the ability
to create and use regular tables, the ability to create databases, the
datatypes in the temp table, any hba stuff, or anything else I could think
of.
Anyone got a clue?
Regards,
Peter
--
Peter Eisentraut
PathWay Computing, Inc.
I mentioned this the other day on another list. I want to reiterate it
here because I can't seem to get anywhere.I create a temporary table
=> create temp table foo (bar text);
CREATE
=> insert into foo values ('hi');
ERROR: pg_temp.29112.0: Permission denied.This apparently happens if and only if the user that executes this has
pg_shadow.usecatupd = 'f'.I have tried this with the 6.5.1 source rpm bundle, fresh after initdb and
also with a 6.5.0 tar ball installation -- same result. (both on RH Linux
5.2-ish)A potential reason that this has gone unnoticed so far is that when you
create a user thus:
=> create user joe;
the usecatupd defaults to true (why?).Also this does not have anything to do with superuser status, the ability
to create and use regular tables, the ability to create databases, the
datatypes in the temp table, any hba stuff, or anything else I could think
of.
OK, you have good points. usecatupd should not be set by default.
Making changes to the system tables can mess things up for everyone.
Initdb will give the postgres superuser permissions, but now createuser
and the SQL command CREATE USER will not give this permission. Also, I
have fixed the code so temp tables, which are acutally named pg_temp,
can be updated by normal users without usecatupd permissions.
Attached is a patch. I will apply it to the current tree.
--
Bruce Momjian | http://www.op.net/~candle
maillist@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 19026Attachments:
/bjm/xtext/plainDownload
? src/log
? src/config.log
? src/config.cache
? src/config.status
? src/GNUmakefile
? src/Makefile.global
? src/Makefile.custom
? src/backend/fmgr.h
? src/backend/parse.h
? src/backend/postgres
? src/backend/global1.bki.source
? src/backend/local1_template1.bki.source
? src/backend/global1.description
? src/backend/local1_template1.description
? src/backend/bootstrap/bootparse.c
? src/backend/bootstrap/bootstrap_tokens.h
? src/backend/bootstrap/bootscanner.c
? src/backend/catalog/genbki.sh
? src/backend/catalog/global1.bki.source
? src/backend/catalog/global1.description
? src/backend/catalog/local1_template1.bki.source
? src/backend/catalog/local1_template1.description
? src/backend/port/Makefile
? src/backend/utils/Gen_fmgrtab.sh
? src/backend/utils/fmgr.h
? src/backend/utils/fmgrtab.c
? src/bin/cleardbdir/cleardbdir
? src/bin/createdb/createdb
? src/bin/createlang/createlang
? src/bin/createuser/createuser
? src/bin/destroydb/destroydb
? src/bin/destroylang/destroylang
? src/bin/destroyuser/destroyuser
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_dump/Makefile
? src/bin/pg_dump/pg_dump
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/pg_version/Makefile
? src/bin/pg_version/pg_version
? src/bin/pgtclsh/mkMakefile.tcldefs.sh
? src/bin/pgtclsh/mkMakefile.tkdefs.sh
? src/bin/pgtclsh/Makefile.tkdefs
? src/bin/pgtclsh/Makefile.tcldefs
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/Makefile
? src/bin/psql/psql
? src/include/version.h
? src/include/config.h
? src/interfaces/ecpg/lib/Makefile
? src/interfaces/ecpg/lib/libecpg.so.3.0.0
? src/interfaces/ecpg/lib/libecpg.so.3.0.1
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgtcl/Makefile
? src/interfaces/libpgtcl/libpgtcl.so.2.0
? src/interfaces/libpq/Makefile
? src/interfaces/libpq/libpq.so.2.0
? src/interfaces/libpq++/Makefile
? src/interfaces/libpq++/libpq++.so.3.0
? src/interfaces/odbc/GNUmakefile
? src/interfaces/odbc/Makefile.global
? src/lextest/lex.yy.c
? src/lextest/lextest
? src/pl/plpgsql/src/Makefile
? src/pl/plpgsql/src/mklang.sql
? src/pl/plpgsql/src/pl_gram.c
? src/pl/plpgsql/src/pl.tab.h
? src/pl/plpgsql/src/pl_scan.c
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/pl/tcl/mkMakefile.tcldefs.sh
? src/pl/tcl/Makefile.tcldefs
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.26
diff -c -r1.26 aclchk.c
*** src/backend/catalog/aclchk.c 1999/07/17 20:16:47 1.26
--- src/backend/catalog/aclchk.c 1999/07/30 17:58:38
***************
*** 392,397 ****
--- 392,398 ----
*/
if (((mode & ACL_WR) || (mode & ACL_AP)) &&
!allowSystemTableMods && IsSystemRelationName(relname) &&
+ strncmp(relname,"pg_temp.", strlen("pg_temp.")) != 0 &&
!((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
{
elog(DEBUG, "pg_aclcheck: catalog update to \"%s\": permission denied",
Index: src/backend/commands/user.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.32
diff -c -r1.32 user.c
*** src/backend/commands/user.c 1999/07/17 20:16:54 1.32
--- src/backend/commands/user.c 1999/07/30 17:58:38
***************
*** 169,175 ****
snprintf(sql, SQL_LENGTH,
"insert into %s (usename,usesysid,usecreatedb,usetrace,"
"usesuper,usecatupd,passwd,valuntil) "
! "values('%s',%d,'%c','t','%c','t',%s%s%s,%s%s%s)",
ShadowRelationName,
stmt->user,
max_id + 1,
--- 169,175 ----
snprintf(sql, SQL_LENGTH,
"insert into %s (usename,usesysid,usecreatedb,usetrace,"
"usesuper,usecatupd,passwd,valuntil) "
! "values('%s',%d,'%c','f','%c','f',%s%s%s,%s%s%s)",
ShadowRelationName,
stmt->user,
max_id + 1,
Index: src/bin/createuser/createuser.sh
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/bin/createuser/createuser.sh,v
retrieving revision 1.11
diff -c -r1.11 createuser.sh
*** src/bin/createuser/createuser.sh 1999/01/31 05:04:25 1.11
--- src/bin/createuser/createuser.sh 1999/07/30 17:58:45
***************
*** 218,224 ****
QUERY="insert into pg_shadow \
(usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd) \
values \
! ('$NEWUSER', $SYSID, '$CANCREATE', 't', '$CANADDUSER','t')"
RES=`$PSQL -c "$QUERY" template1`
--- 218,224 ----
QUERY="insert into pg_shadow \
(usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd) \
values \
! ('$NEWUSER', $SYSID, '$CANCREATE', 'f', '$CANADDUSER','f')"
RES=`$PSQL -c "$QUERY" template1`
Bruce Momjian wrote:
...
OK, you have good points. usecatupd should not be set by default.
Making changes to the system tables can mess things up for everyone.
Initdb will give the postgres superuser permissions, but now createuser
and the SQL command CREATE USER will not give this permission. Also, I
have fixed the code so temp tables, which are acutally named pg_temp,
can be updated by normal users without usecatupd permissions.Attached is a patch. I will apply it to the current tree.
Bruce, this change has some other implications. I tested
the effect of the patch by altering the rights of my own account (setting
usecatupd to false). I cannot now create other users: although usesuper is
true, the attempt to update pg_shadow with the new user's row fails:
olly@linda$ createuser fred
Enter user's postgres ID -> 999
Is user "fred" allowed to create databases (y/n) n
Is user "fred" a superuser? (y/n) n
ERROR: pg_shadow: Permission denied.
createuser: fred was NOT added successfully
so I think your change needs to be extended to allow pg_shadow to be
updated when a user is created; in this case, usesuper should
override usecatupd.
On the other hand, a user with usecreatedb true is able to modify
pg_database outside the context of a create database command. This also
seems to be undesirable. I think that create user, alter user and create
database should work even though the user does not have usecatupd, but the
user should be able to change the affected tables only through those
commands and not by direct manipulation, unless he has usecatupd in
addition to other privileges.
I regret that I can only point out these problems rather than provide
a fix...
--
Vote against SPAM: http://www.politik-digital.de/spam/
========================================
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Have not I commanded thee? Be strong and of a good
courage; be not afraid, neither be thou dismayed; for
the LORD thy God is with thee whithersoever thou
goest." Joshua 1:9