has_table_priviledge

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