Re: permission prob: granted, but still denied

On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> s-psql(at)rhythm(dot)cx writes:
> > Hello, I'm having some sort of permission problem on my database, running
> > version 7.2.3.
>
> Curious. What exactly is the connection between the two tables?
> Standard foreign-key reference, or something else? Could we see the
> full schemas for both tables (ideally from pg_dump -s -t)?
>

The relationship is a standard foreign key, with websess referencing
cscuser. I am enclosing the schema for both tables below.

Stephan Szabo requested I turn on query logging (is that synonymous with
increasing postmaster's debugging level?). I increased the debug level all
the way to 5, however I didn't see anything telling in the resulting log.
I'm enclosing that as well if it helps anyone.

If it increases readability for anyone, I put the schema & log output on my
website: http://rhythm.cx/~steve/pg/

Richard Huxton also suggested I check the permissions on related sequences -
they are ok. Here is a listing of permissions relevant to this problem
(webauth is the user with the Permission Denied problem):

cscuser | {=,webauth=arwdRxt}
cscuser_userid_seq | {=,csclub=arwdRxt,webauth=arwdRxt}
major | {=,csclub=arwdRxt,webauth=arwdRxt}
major_majorid_seq | {=,csclub=arwdRxt,webauth=arwdRxt}
member | {=,csclub=arwdRxt,webauth=arwdRxt}
member_memberid_seq | {=,csclub=arwdRxt,webauth=arwdRxt}
websess | {=,webauth=arwdRxt}

The schema, summarized: websess has a foreign key to cscuser. cscuser has a
foreign key to member. member has a foreign key to major. There are 24 other
tables in this database, but there are no fkeys to any other tables within
these tables. All referential integrity constraints are satisfied by the
following INSERT statements. These same commands succeed for another user.

The first time the command is executed on a connection (by user webauth):

csclub=> INSERT INTO websess(hash,userid,created) VALUES('abde',1,'2002-09-20');
ERROR: cscuser: Permission denied.

DEBUG: StartTransactionCommand
DEBUG: query: INSERT INTO websess(hash,userid,created) VALUES('abde',1,'2002-09-20');
DEBUG: parse tree: { QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823 :subquery <> :alias <> :eref { ATTR :relname websess :attrs ( "hash" "userid" "created" )} :inh false :inFromCl false :checkForRead false :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 1043 :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] } { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: rewritten parse tree:
DEBUG: { QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823 :subquery <> :alias <> :eref { ATTR :relname websess :attrs ( "hash" "userid" "created" )} :inh false :inFromCl false :checkForRead false :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 1043 :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] } { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: plan: { RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] }} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :resconstantqual <>}
DEBUG: ProcessQuery
DEBUG: query: SELECT 1 FROM ONLY "cscuser" WHERE "userid" = $1 FOR UPDATE OF "cscuser"
DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname cscuser :relid 16814 :subquery <> :alias <> :eref { ATTR :relname cscuser :attrs ( "userid" "username" "password" "enabled" "memberid" "comment" )} :inh false :inFromCl true :checkForRead true :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype 23 })}} :rowMarks ( 1) :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: rewritten parse tree:
DEBUG: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname cscuser :relid 16814 :subquery <> :alias <> :eref { ATTR :relname cscuser :attrs ( "userid" "username" "password" "enabled" "memberid" "comment" )} :inh false :inFromCl true :checkForRead true :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype 23 })}} :rowMarks ( 1) :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: plan: { INDEXSCAN :startup_cost 0.00 :total_cost 4.82 :rows 1 :width 6 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 27 :restypmod -1 :resname ctid1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk true } :expr { VAR :varno 1 :varattno -1 :vartype 27 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno -1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 16816) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype 23 })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype 23 })})) :indxorderdir 1 }
ERROR: cscuser: Permission denied.
DEBUG: AbortCurrentTransaction

The second and all subsequent times the command is executed on a given
connection (by user webauth):

csclub=> INSERT INTO websess(hash,userid,created) VALUES('abde',1,'2002-09-20');
ERROR: cscuser: Permission denied.

DEBUG: StartTransactionCommand
DEBUG: query: INSERT INTO websess(hash,userid,created) VALUES('abde',1,'2002-09-20');
DEBUG: parse tree: { QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823 :subquery <> :alias <> :eref { ATTR :relname websess :attrs ( "hash" "userid" "created" )} :inh false :inFromCl false :checkForRead false :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 1043 :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] } { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: rewritten parse tree:
DEBUG: { QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823 :subquery <> :alias <> :eref { ATTR :relname websess :attrs ( "hash" "userid" "created" )} :inh false :inFromCl false :checkForRead false :checkForWrite true :checkAsUser 0}) :jointree { FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 1043 :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] } { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: plan: { RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 36 :resname hash :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 97 98 100 101 ] }} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1114 :restypmod -1 :resname created :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 0 -122 116 -108 65 ] }}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :resconstantqual <>}
DEBUG: ProcessQuery
ERROR: cscuser: Permission denied.
DEBUG: AbortCurrentTransaction

###################################################################
### schema for websess ############################################
###################################################################

--
-- Selected TOC Entries:
--
\connect - csclub

--
-- TOC Entry ID 2 (OID 16823)
--
-- Name: websess Type: TABLE Owner: csclub
--

CREATE TABLE "websess" (
"hash" character varying(32) NOT NULL,
"userid" integer NOT NULL,
"created" timestamp without time zone NOT NULL,
CONSTRAINT "websess_hash" CHECK ((hash ~ '^[a-fA-F0-9]+$'::text)),
Constraint "websess_pkey" Primary Key ("hash")
);

--
-- TOC Entry ID 3 (OID 16823)
--
-- Name: websess Type: ACL Owner:
--

REVOKE ALL on "websess" from PUBLIC;
GRANT ALL on "websess" to "webauth";

--
-- TOC Entry ID 4 (OID 16827)
--
-- Name: "RI_ConstraintTrigger_16826" Type: TRIGGER Owner: csclub
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "websess" FROM "cscuser" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'websess', 'cscuser', 'UNSPECIFIED', 'userid', 'userid');

###################################################################
### schema for cscuser ############################################
###################################################################

--
-- Selected TOC Entries:
--
\connect - csclub

--
-- TOC Entry ID 2 (OID 16814)
--
-- Name: cscuser Type: TABLE Owner: csclub
--

CREATE TABLE "cscuser" (
"userid" integer DEFAULT nextval('"cscuser_userid_seq"'::text) NOT NULL,
"username" character varying(12) NOT NULL,
"password" character varying(12) NOT NULL,
"enabled" boolean DEFAULT 't'::bool NOT NULL,
"memberid" integer,
"comment" character varying(40),
CONSTRAINT "cscuser_comment" CHECK ((("comment" ~ '^[[:print:]]+$'::text) AND (NOT ("comment" ~ '[<>]'::text)))),
CONSTRAINT "cscuser_password" CHECK (("password" ~ '^[[:print:]]+$'::text)),
CONSTRAINT "cscuser_username" CHECK ((username ~ '^[a-z]+[a-z0-9]+$'::text)),
Constraint "cscuser_pkey" Primary Key ("userid")
);

--
-- TOC Entry ID 3 (OID 16814)
--
-- Name: cscuser Type: ACL Owner:
--

REVOKE ALL on "cscuser" from PUBLIC;
GRANT ALL on "cscuser" to "webauth";

--
-- TOC Entry ID 4 (OID 16818)
--
-- Name: "RI_ConstraintTrigger_16817" Type: TRIGGER Owner: csclub
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "cscuser" FROM "member" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'cscuser', 'member', 'UNSPECIFIED', 'memberid', 'memberid');

--
-- TOC Entry ID 5 (OID 16829)
--
-- Name: "RI_ConstraintTrigger_16828" Type: TRIGGER Owner: csclub
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "cscuser" FROM "websess" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'websess', 'cscuser', 'UNSPECIFIED', 'userid', 'userid');

--
-- TOC Entry ID 6 (OID 16831)
--
-- Name: "RI_ConstraintTrigger_16830" Type: TRIGGER Owner: csclub
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "cscuser" FROM "websess" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'websess', 'cscuser', 'UNSPECIFIED', 'userid', 'userid');