Thread: has_table_priviledge
postgresql'rs-
i can't, for the live of me, figure out why this does not work?
grib_tables=# \d btrel
Table "btrel"
Column | Type | Modifiers
---------+------+-----------
relname | text | not null
Primary key: btrel_pkey
Triggers: RI_ConstraintTrigger_5481788,
RI_ConstraintTrigger_5481786,
RI_ConstraintTrigger_4557550,
RI_ConstraintTrigger_4557548
grib_tables=# select has_table_priviledge('ahoward',"btrel",'update');
ERROR: Attribute 'btrel' not found
grib_tables=# select has_table_priviledge('ahoward','btrel','update');
ERROR: Function 'has_table_priviledge(unknown, unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
grib_tables=# select has_table_priviledge('ahoward',btrel,'update');
ERROR: Attribute 'btrel' not found
am i doing something completely stupid here?
-a
--
====================================
| Ara Howard
| NOAA Forecast Systems Laboratory
| Information and Technology Services
| Data Systems Group
| R/FST 325 Broadway
| Boulder, CO 80305-3328
| Email: ara.t.howard@fsl.noaa.gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================
ahoward <ahoward@fsl.noaa.gov> writes:
> grib_tables=# select has_table_priviledge('ahoward','btrel','update');
> ERROR: Function 'has_table_priviledge(unknown, unknown, unknown)' does not exist
Right syntax, wrong function name --- there's no "d" in "privilege".
regards, tom lane
On Thu, 24 Apr 2003, Tom Lane wrote:
> ahoward <ahoward@fsl.noaa.gov> writes:
> > grib_tables=# select has_table_priviledge('ahoward','btrel','update');
> > ERROR: Function 'has_table_priviledge(unknown, unknown, unknown)' does not exist
>
> Right syntax, wrong function name --- there's no "d" in "privilege".
>
> regards, tom lane
;-)
long day... the error message sure wasn't helpfull though, i finally figured
that one out... any idea why this doesn't work?
create table vtrcs_co_l
(
relname text,
who name default current_user,
time timestamp(0) without time zone default current_timestamp,
primary key (relname),
foreign key (relname)
references btrel (relname),
constraint c0 check
(has_table_privilege(username, relname, 'update'))
);
it creates the table, but an insert of
insert into vtrcs_co_l values('parameter');
fails with
ERROR: text_oid: error in "parameter": can't parse "parameter"
which is very odd since
select has_table_privilege('parameter', current_user, current_timestamp);
does not. incidentally
insert into vtrcs_co_l values('parameter', current_user, current_timestamp);
fails as well with the same error.
thanks.
-a
--
====================================
| Ara Howard
| NOAA Forecast Systems Laboratory
| Information and Technology Services
| Data Systems Group
| R/FST 325 Broadway
| Boulder, CO 80305-3328
| Email: ara.t.howard@fsl.noaa.gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================
ahoward <ahoward@fsl.noaa.gov> writes:
> insert into vtrcs_co_l values('parameter');
> fails with
> ERROR: text_oid: error in "parameter": can't parse "parameter"
Not here. As best I can reproduce your example (it doesn't work as
given, I'm assuming you meant "who" not "username") I get
regression=# insert into vtrcs_co_l values('parameter');
ERROR: Relation "parameter" does not exist
which is about what I'd expect ...
regards, tom lane
On Thu, 24 Apr 2003, Tom Lane wrote:
> ahoward <ahoward@fsl.noaa.gov> writes:
> > insert into vtrcs_co_l values('parameter');
> > fails with
> > ERROR: text_oid: error in "parameter": can't parse "parameter"
>
> Not here. As best I can reproduce your example (it doesn't work as
> given, I'm assuming you meant "who" not "username") I get
yes.
> regression=# insert into vtrcs_co_l values('parameter');
> ERROR: Relation "parameter" does not exist
>
> which is about what I'd expect ...
something still doesn't seem right... this is the simplest possible demo of the problem:
~ > cat advisory_lock.sql
create table advisory_lock
(
relname text,
constraint c check
(has_table_privilege(user, relname, 'update'))
);
insert into advisory_lock values ('pg_class');
~ > psql -f lock.sql
CREATE
psql:lock.sql:7: ERROR: text_oid: error in "pg_class": can't parse "pg_class"
shouldn't this work?
-a
--
====================================
| Ara Howard
| NOAA Forecast Systems Laboratory
| Information and Technology Services
| Data Systems Group
| R/FST 325 Broadway
| Boulder, CO 80305-3328
| Email: ara.t.howard@fsl.noaa.gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================
ahoward <ahoward@fsl.noaa.gov> writes:
> create table advisory_lock
> (
> relname text,
> constraint c check
> (has_table_privilege(user, relname, 'update'))
> );
> insert into advisory_lock values ('pg_class');
> psql:lock.sql:7: ERROR: text_oid: error in "pg_class": can't parse "pg_class"
> shouldn't this work?
It does work, for me. What PG version are you using, exactly? How did
you build it? I see no problem using 7.3 branch tip (so it could be
that the problem is fixed by some post-7.3.2 patch, but I can't think
of any that might be relevant).
Can anyone else try this case?
regards, tom lane
On Thu, 24 Apr 2003, Tom Lane wrote:
> ahoward <ahoward@fsl.noaa.gov> writes:
> > create table advisory_lock
> > (
> > relname text,
> > constraint c check
> > (has_table_privilege(user, relname, 'update'))
> > );
> > insert into advisory_lock values ('pg_class');
>
> > psql:lock.sql:7: ERROR: text_oid: error in "pg_class": can't parse "pg_class"
>
> > shouldn't this work?
>
> It does work, for me. What PG version are you using, exactly? How did
> you build it? I see no problem using 7.3 branch tip (so it could be
> that the problem is fixed by some post-7.3.2 patch, but I can't think
> of any that might be relevant).
>
> Can anyone else try this case?
It seemed to work for me on a 7.3.1 machine I tried it on.
On Thu, 24 Apr 2003, Tom Lane wrote:
> ahoward <ahoward@fsl.noaa.gov> writes:
> > create table advisory_lock
> > (
> > relname text,
> > constraint c check
> > (has_table_privilege(user, relname, 'update'))
> > );
> > insert into advisory_lock values ('pg_class');
>
> > psql:lock.sql:7: ERROR: text_oid: error in "pg_class": can't parse "pg_class"
>
> > shouldn't this work?
>
> It does work, for me. What PG version are you using, exactly? How did
> you build it? I see no problem using 7.3 branch tip (so it could be
> that the problem is fixed by some post-7.3.2 patch, but I can't think
> of any that might be relevant).
version
-----------------------------------------------------------
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
it seems to work for me on 7.3.2 though...
strange.
-a
--
====================================
| Ara Howard
| NOAA Forecast Systems Laboratory
| Information and Technology Services
| Data Systems Group
| R/FST 325 Broadway
| Boulder, CO 80305-3328
| Email: ara.t.howard@fsl.noaa.gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================
ahoward <ahoward@fsl.noaa.gov> writes:
> On Thu, 24 Apr 2003, Tom Lane wrote:
>> It does work, for me. What PG version are you using, exactly?
> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
Ah. I see it too on 7.2. It looks like the 7.2 parser is choosing
has_table_privilege(name, oid, text) in preference to
has_table_privilege(name, name, text). I can get it to work on 7.2 by
putting in an explicit cast, ie, making the constraint read
constraint c check
(has_table_privilege(user, relname::name, 'update'))
Probably the reason 7.3 doesn't misbehave is that its function is
declared has_table_privilege(name, text, text). That affects the
decision because 'text' is a preferred type and 'name' isn't.
regards, tom lane
On Thu, 24 Apr 2003, Tom Lane wrote: > Ah. I see it too on 7.2. It looks like the 7.2 parser is choosing > has_table_privilege(name, oid, text) in preference to > has_table_privilege(name, name, text). I can get it to work on 7.2 by > putting in an explicit cast, ie, making the constraint read > constraint c check > (has_table_privilege(user, relname::name, 'update')) thanks alot tom - that did it for me. i'm really glad to have that solved since i was otherwise going to have to do it at the application layer which felt hackish. in case you hadn't already guessed, i was putting together a advisory locking scheme for certain classes of tables (bi-temporal ones), such that transient processes could obtain a lock on one invocation, but release it in another. AFAIK there is no way to do this with the normal locking facilities since any lock expires when the transaction/session ends (guess you could have a lock daemon...). anyhow, this facility allows things like cgi's to carry locks across processes. seems like others would have come up against this problem before... > Probably the reason 7.3 doesn't misbehave is that its function is declared > has_table_privilege(name, text, text). That affects the decision because > 'text' is a preferred type and 'name' isn't. i'll upgrade ASAP. thanks again for the help. -a -- ==================================== | Ara Howard | NOAA Forecast Systems Laboratory | Information and Technology Services | Data Systems Group | R/FST 325 Broadway | Boulder, CO 80305-3328 | Email: ara.t.howard@fsl.noaa.gov | Phone: 303-497-7238 | Fax: 303-497-7259 ====================================