Обсуждение: permission prob: granted, but still denied

Поиск

Список

Период

Сортировка

permission prob: granted, but still denied

От

s-psql@rhythm.cx

Дата:

30 октября 2002 г., 04:49:11

Hello, I'm having some sort of permission problem on my database, running
version 7.2.3.

The user in question is 'webauth', who does not own any of the objects in
question, nor is a superuser. He has been GRANTed ALL to a table 'websess' as
well as a table websess references, 'cscuser'. However, when that user
attempts to INSERT a valid row into table websess,

    ERROR: cscuser: Permission denied.

is returned. Does anyone know why that occurs? The owner of the database &
tables is able to insert rows just fine.

Here is a sample session:

% psql csclub webauth
csclub=> INSERT INTO websess(hash,userid,created)
         VALUES('ef7a3c41a798d535aba526d5c0dfe251',2,'2002-10-19');
ERROR:  cscuser: Permission denied.
csclub=> \z
           Access privileges for database "csclub"
          Table          |         Access privileges
-------------------------+------------------------------------
...
 websess                 | {=,webauth=arwdRxt}
 cscuser                 | {=,webauth=arwdRxt}
 cscuser_userid_seq     | {=,webauth=arwdRxt}
...


Thanks for any help.

Re: permission prob: granted, but still denied

От

Richard Huxton

Дата:

30 октября 2002 г., 11:16:54

On Wednesday 30 Oct 2002 6:53 am, s-psql@rhythm.cx wrote:
> Hello, I'm having some sort of permission problem on my database, running
> version 7.2.3.
>
> The user in question is 'webauth', who does not own any of the objects in
> question, nor is a superuser. He has been GRANTed ALL to a table 'websess'
> as well as a table websess references, 'cscuser'. However, when that user
> attempts to INSERT a valid row into table websess,
>
>     ERROR: cscuser: Permission denied.
>
> is returned. Does anyone know why that occurs? The owner of the database &
> tables is able to insert rows just fine.

Do you have any serial types or sequences on the table? If so, you'll need to
check permissions on those too.

--
  Richard Huxton
  Archonet Ltd

Re: permission prob: granted, but still denied

От

s-psql@rhythm.cx

Дата:

30 октября 2002 г., 15:50:08

On Wed, Oct 30, 2002 at 10:02:38AM +0000, Richard Huxton wrote:
> On Wednesday 30 Oct 2002 6:53 am, s-psql@rhythm.cx wrote:
> > Hello, I'm having some sort of permission problem on my database, running
> > version 7.2.3.
> >
> > The user in question is 'webauth', who does not own any of the objects in
> > question, nor is a superuser. He has been GRANTed ALL to a table 'websess'
> > as well as a table websess references, 'cscuser'. However, when that user
> > attempts to INSERT a valid row into table websess,
> >
> >     ERROR: cscuser: Permission denied.
> >
> > is returned. Does anyone know why that occurs? The owner of the database &
> > tables is able to insert rows just fine.
>
> Do you have any serial types or sequences on the table? If so, you'll need to
> check permissions on those too.
>

Yep, all relevant sequences have permissions too:

csclub=> \z
           Access privileges for database "csclub"
          Table          |         Access privileges
-------------------------+------------------------------------
...
 cscuser                 | {=,webauth=arwdRxt}
 cscuser_userid_seq      | {=,csclub=arwdRxt,webauth=arwdRxt}
 websess                 | {=,webauth=arwdRxt}
csclub=> INSERT INTO websess(hash,userid,created)
         VALUES('abde',1,'2002-09-20');
ERROR:  cscuser: Permission denied.


Anyone have any other ideas?

Re: permission prob: granted, but still denied

От

Stephan Szabo

Дата:

30 октября 2002 г., 16:19:09

On Wed, 30 Oct 2002 s-psql@rhythm.cx wrote:

> On Wed, Oct 30, 2002 at 10:02:38AM +0000, Richard Huxton wrote:
> > On Wednesday 30 Oct 2002 6:53 am, s-psql@rhythm.cx wrote:
> > > Hello, I'm having some sort of permission problem on my database, running
> > > version 7.2.3.
> > >
> > > The user in question is 'webauth', who does not own any of the objects in
> > > question, nor is a superuser. He has been GRANTed ALL to a table 'websess'
> > > as well as a table websess references, 'cscuser'. However, when that user
> > > attempts to INSERT a valid row into table websess,
> > >
> > >     ERROR: cscuser: Permission denied.
> > >
> > > is returned. Does anyone know why that occurs? The owner of the database &
> > > tables is able to insert rows just fine.
> >
> > Do you have any serial types or sequences on the table? If so, you'll need to
> > check permissions on those too.
> >
>
> Yep, all relevant sequences have permissions too:
>
> csclub=> \z
>            Access privileges for database "csclub"
>           Table          |         Access privileges
> -------------------------+------------------------------------
> ...
>  cscuser                 | {=,webauth=arwdRxt}
>  cscuser_userid_seq      | {=,csclub=arwdRxt,webauth=arwdRxt}
>  websess                 | {=,webauth=arwdRxt}
> csclub=> INSERT INTO websess(hash,userid,created)
>          VALUES('abde',1,'2002-09-20');
> ERROR:  cscuser: Permission denied.
>
> Anyone have any other ideas?

I'd guess it was the foreign key constraint check that was doing it,
but that should be does as the owner of the other table. Try turning
on query logging and seeing if you can get a better idea of what's
happening.

Re: permission prob: granted, but still denied

От

Richard Huxton

Дата:

30 октября 2002 г., 16:45:03

On Wednesday 30 Oct 2002 5:54 pm, s-psql@rhythm.cx wrote:
> On Wed, Oct 30, 2002 at 10:02:38AM +0000, Richard Huxton wrote:
> > Do you have any serial types or sequences on the table? If so, you'll
> > need to check permissions on those too.
>
> Yep, all relevant sequences have permissions too:
>
> csclub=> \z
>            Access privileges for database "csclub"
>           Table          |         Access privileges
> -------------------------+------------------------------------
> ...
>  cscuser                 | {=,webauth=arwdRxt}
>  cscuser_userid_seq      | {=,csclub=arwdRxt,webauth=arwdRxt}
>  websess                 | {=,webauth=arwdRxt}
> csclub=> INSERT INTO websess(hash,userid,created)
>          VALUES('abde',1,'2002-09-20');
> ERROR:  cscuser: Permission denied.
>
>
> Anyone have any other ideas?

Well - assuming it is connecting as 'webauth' the only thing I can suggest is
to dump the schema, edit it down to a minimum, reload it to a test db and see
if that pins down the problem.

- Richard Huxton

Re: permission prob: granted, but still denied

От

Tom Lane

Дата:

30 октября 2002 г., 17:06:14

s-psql@rhythm.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)?

            regards, tom lane

Re: permission prob: granted, but still denied

От

Richard Huxton

Дата:

30 октября 2002 г., 17:15:07

On Wednesday 30 Oct 2002 6:19 pm, Stephan Szabo wrote:
> On Wed, 30 Oct 2002 s-psql@rhythm.cx wrote:

> > > > objects in question, nor is a superuser. He has been GRANTed ALL to a
> > > > table 'websess' as well as a table websess references, 'cscuser'.

> >            Access privileges for database "csclub"
> >           Table          |         Access privileges
> > -------------------------+------------------------------------
> > ...
> >  cscuser                 | {=,webauth=arwdRxt}
> >  cscuser_userid_seq      | {=,csclub=arwdRxt,webauth=arwdRxt}
> >  websess                 | {=,webauth=arwdRxt}
> > csclub=> INSERT INTO websess(hash,userid,created)
> >          VALUES('abde',1,'2002-09-20');
> > ERROR:  cscuser: Permission denied.
> >
> > Anyone have any other ideas?
>
> I'd guess it was the foreign key constraint check that was doing it,
> but that should be does as the owner of the other table. Try turning
> on query logging and seeing if you can get a better idea of what's
> happening.

Surely if webauth is granted all permissions on both tables that wouldn't
matter?

--
  Richard Huxton

Re: permission prob: granted, but still denied

От

s-psql@rhythm.cx

Дата:

30 октября 2002 г., 18:08:58

On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> s-psql@rhythm.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
:checkForWritetrue :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
:resjunkfalse } :expr { EXPR :typeOid 1043  :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST
:consttype1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 97 98 100 101 ] } { CONST
:consttype23 :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
-10865 ] }}) :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823  :subquery <> :alias <> :eref { ATTR
:relnamewebsess :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
:constlen4 :constbyval true :constisnull false :constvalue  4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST
:consttype23 :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
:ressortgroupref0 :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
:reskey0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false
:constisnullfalse :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
:inFromCltrue :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
:varattno1 :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
:constlen4 :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname cscuser :relid 16814  :subquery <> :alias <> :eref { ATTR
:relnamecscuser :attrs ( "userid"   "username"   "password"   "enabled"   "memberid"   "comment" )} :inh false
:inFromCltrue :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
:varattno1 :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
:constlen4 :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
:vartype23 :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
:opresulttype16 } :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
:checkForWritetrue :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
:resjunkfalse } :expr { EXPR :typeOid 1043  :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST
:consttype1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 97 98 100 101 ] } { CONST
:consttype23 :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
-10865 ] }}) :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823  :subquery <> :alias <> :eref { ATTR
:relnamewebsess :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
:constlen4 :constbyval true :constisnull false :constvalue  4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST
:consttype23 :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
:ressortgroupref0 :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
:reskey0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false
:constisnullfalse :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
IMMEDIATEFOR 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
IMMEDIATEFOR 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
EACHROW 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
EACHROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'websess', 'cscuser', 'UNSPECIFIED', 'userid',
'userid');

Re: permission prob: granted, but still denied

От

Stephan Szabo

Дата:

30 октября 2002 г., 19:11:13

On Wed, 30 Oct 2002, Richard Huxton wrote:

> On Wednesday 30 Oct 2002 6:19 pm, Stephan Szabo wrote:
> > On Wed, 30 Oct 2002 s-psql@rhythm.cx wrote:
>
> > > > > objects in question, nor is a superuser. He has been GRANTed ALL to a
> > > > > table 'websess' as well as a table websess references, 'cscuser'.
>
> > >            Access privileges for database "csclub"
> > >           Table          |         Access privileges
> > > -------------------------+------------------------------------
> > > ...
> > >  cscuser                 | {=,webauth=arwdRxt}
> > >  cscuser_userid_seq      | {=,csclub=arwdRxt,webauth=arwdRxt}
> > >  websess                 | {=,webauth=arwdRxt}
> > > csclub=> INSERT INTO websess(hash,userid,created)
> > >          VALUES('abde',1,'2002-09-20');
> > > ERROR:  cscuser: Permission denied.
> > >
> > > Anyone have any other ideas?
> >
> > I'd guess it was the foreign key constraint check that was doing it,
> > but that should be does as the owner of the other table. Try turning
> > on query logging and seeing if you can get a better idea of what's
> > happening.
>
> Surely if webauth is granted all permissions on both tables that wouldn't
> matter?

webauth's permissions shouldn't be used on the triggered select under any
circumstances right now.  AFAIK it always runs as the owner of the other
table.

Re: permission prob: granted, but still denied

От

Stephan Szabo

Дата:

30 октября 2002 г., 19:17:29

On Wed, 30 Oct 2002 s-psql@rhythm.cx wrote:

> On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> > s-psql@rhythm.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.

Well, it seems to say that it is the foreign key select.

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

Who is the owner of cscuser?  If you log in as that user, does a
select 1 from cscuser where userid=<some userid> for update of cscuser
succeed?

Re: permission prob: granted, but still denied

От

s-psql@rhythm.cx

Дата:

30 октября 2002 г., 20:06:14

On Wed, Oct 30, 2002 at 01:17:23PM -0800, Stephan Szabo wrote:
> On Wed, 30 Oct 2002 s-psql@rhythm.cx wrote:
>
> > On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> > > s-psql@rhythm.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.
>
> Well, it seems to say that it is the foreign key select.
>
> > 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}
>
> Who is the owner of cscuser?  If you log in as that user, does a
> select 1 from cscuser where userid=<some userid> for update of cscuser
> succeed?
>
>

You lead me to the solution.

The owner of table cscuser is 'csclub', who did not have permissions to
cscuser (accidentally revoked them). Earlier I tried "select 1 from cscuser
where userid=<some userid> for update of cscuser" as webauth and it
succeeded, and at that point I was lost.

I just now put one and one together thanks to your post about fkey triggers
being executed as the table owner. I granted all to the table owner, and it
all works now.

Thanks for your help.

-Steve

Re: permission prob: granted, but still denied

От

Steve Benson

Дата:

04 ноября 2002 г., 18:12:44

On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> s-psql@rhythm.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
:checkForWritetrue :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
:resjunkfalse } :expr { EXPR :typeOid 1043  :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST
:consttype1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 97 98 100 101 ] } { CONST
:consttype23 :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
-10865 ] }}) :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823  :subquery <> :alias <> :eref { ATTR
:relnamewebsess :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
:constlen4 :constbyval true :constisnull false :constvalue  4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST
:consttype23 :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
:ressortgroupref0 :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
:reskey0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false
:constisnullfalse :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
:inFromCltrue :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
:varattno1 :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
:constlen4 :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname cscuser :relid 16814  :subquery <> :alias <> :eref { ATTR
:relnamecscuser :attrs ( "userid"   "username"   "password"   "enabled"   "memberid"   "comment" )} :inh false
:inFromCltrue :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
:varattno1 :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
:constlen4 :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
:vartype23 :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
:opresulttype16 } :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
:checkForWritetrue :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
:resjunkfalse } :expr { EXPR :typeOid 1043  :opType func :oper { FUNC :funcid 669 :functype 1043 } :args ({ CONST
:consttype1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 97 98 100 101 ] } { CONST
:consttype23 :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
-10865 ] }}) :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
:hasAggsfalse :hasSubLinks false :rtable ({ RTE :relname websess :relid 16823  :subquery <> :alias <> :eref { ATTR
:relnamewebsess :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
:constlen4 :constbyval true :constisnull false :constvalue  4 [ 36 0 0 0 ] })}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype23 :restypmod -1 :resname userid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST
:consttype23 :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
:ressortgroupref0 :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
:reskey0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1114 :constlen 8 :constbyval false
:constisnullfalse :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
IMMEDIATEFOR 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
IMMEDIATEFOR 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
EACHROW 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
EACHROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'websess', 'cscuser', 'UNSPECIFIED', 'userid',
'userid');

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: permission prob: granted, but still denied