Re: [HACKERS] Cannot insert into temp tables - Mailing list pgsql-hackers

> 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 19026
? 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`