Thread: Cannot insert into temp tables

Cannot insert into temp tables

From

Peter Eisentraut

Date:

30 July 1999, 16:22:47

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.

Re: [HACKERS] Cannot insert into temp tables

From

Bruce Momjian

Date:

30 July 1999, 17:13:50

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

Re: [HACKERS] Cannot insert into temp tables

From

"Oliver Elphick"

Date:

31 July 1999, 16:27:03

Bruce Momjian wrote:
... >OK, you have good points.  usecatupd should not be set by default.  >Making changes to the system tables can mess
thingsup for everyone.  >Initdb will give the postgres superuser permissions, but now createuser >and the SQL command
CREATEUSER 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
currenttree.
 

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
ID32B8FAA1                ========================================    "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

Re: [HACKERS] Cannot insert into temp tables

From

Bruce Momjian

Date:

27 September 1999, 15:45:04

> 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:


OK, I have committed a fix for this.  I now give update catalog rights
to anyone who has createdb or super-user rights.  This is in the current
tree only.  Let me know if you see any additional problems.


--  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,
Pennsylvania19026