[GENERAL] A security problem (newbie)

[Paulo da Silva]

Hello,

I need to grant access to some records of ts table to
user psergio.

I created 2 views. psergio should be allowed to use select
only on one of them. But I removed access to the whole table.
So, although I grant access to va, it is denied because the
ts table is not acessible!

How can I turn around this?

TIA
-- 
Paulo ([EMAIL PROTECTED])

CREATE TABLE ts (
dep text,
descr   text
);

INSERT INTO ts VALUES ('A','aa');
INSERT INTO ts VALUES ('B','bb');
INSERT INTO ts VALUES ('B','xx');
INSERT INTO ts VALUES ('A','yy');

create view va as select * from ts where dep='A';
create view vb as select * from ts where dep='B';

revoke all on ts from psergio;
revoke all on va from psergio;
revoke all on vb from psergio;

grant select on va to psergio;

select * from va;
-- Denied because ts is not acessible!!!

select * from vb;

drop table ts;
drop view va;
drop view vb;

[GENERAL] Re: [SQL] FW: indexing functions

[Gene Selkov Jr.]

> > Is it possible to manipulate the way an index is created by using
> > functions? 

Yes, it is possible:

\h create index
Command: create index
Description: construct an index
Syntax:
CREATE [UNIQUE] INDEX indexname ON class_name [USING access_method]
( attr1 [type_class1], ...attrN | funcname(attr1, ...) [type_class] );
  

You are responsible for providing the function in any acceptable form
(the only one I am familiar with is a c-coded shared object)

> > I would like to store SGML data in text fields that include
> > a lot of entities (like ü). To enable the user to search the
> > ü character as u I would like to index those entities as the
> > character without the diacritical marks. Is this possible?

Not without the transformation you apply before indexing. It could be
a user-defined function called from the CREATE INDEX instruction
above, or any filter applied externally before the data are loaded to
postgres.

If the data you are talking about are just words with character
entities, it would be a reasonable investment to write a user-defined
function. If the goal is to be able to run queries related to the
structure of the SGML documents, these will have to be split into a
number of tables representing their structure (assuming all records
adhere to the same model), and at this point, character filters can
also be applied. The records themselves will have to be stored as
blobs in a postgres database or as individual files outside.

--Gene

Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file

[Gene Selkov Jr.]

> I also think the ticks that Michael is talking about are required.  You can
> usually find more information about the "copy" error in the server.log
> (don't know where this is located on your system).

The file name must be enclosed in single closing quotes ('\047'). And
by the way, psql (if built with readline library) allows emacs-style
command line editing and can complete file names like bash does (I am
also wondering if it's possible to improve it to complete SGL keywords
and table names). I always use completion to make sure the file exists
and is accessible:

copy ENTRY from '/u1
w
copy ENTRY from '/u1/w

copy ENTRY from '/u1/www/
 to see choices


';

BTW, what did you mean by '\copy'?


--Gene

[GENERAL] A security problem (newbie)

[Paulo da Silva]

Hello,

I'm sorry if this is the 2nd post. I'm not sure if
it was sent the 1st time.

I need to grant access to some records of ts table to
user psergio.

I created 2 views. psergio should be allowed to use select
only on one of them. But I removed access to the whole table.
So, although I grant access to va, it is denied because the
ts table is not acessible!

How can I turn around this?

TIA
-- 
Paulo ([EMAIL PROTECTED])

CREATE TABLE ts (
dep text,
descr   text
);

INSERT INTO ts VALUES ('A','aa');
INSERT INTO ts VALUES ('B','bb');
INSERT INTO ts VALUES ('B','xx');
INSERT INTO ts VALUES ('A','yy');

create view va as select * from ts where dep='A';
create view vb as select * from ts where dep='B';

revoke all on ts from psergio;
revoke all on va from psergio;
revoke all on vb from psergio;

grant select on va to psergio;

select * from va;
-- Denied because ts is not acessible!!!

select * from vb;

drop table ts;
drop view va;
drop view vb;

[GENERAL] Broken Pipe?

[Chucka]

After an upgrade to redhat 5.2 from 5.0 ,when i start psql i get the message "Broken 
Pipe"?

What does this mean?

chuck

[GENERAL] selects on datetime

[Scot Brady]

Title: selects on datetime





Hello all,


I have a table w/ a field of type datetime. I would like to do something like:
select count(jobs) for every day of a particular month and year.


my current select statement is:
Select count(job_id) from jobs where date_part('month',sub_dt)=2 and date_part('year',sub_dt)=1999 and date_
part('day',sub_dt)=?";


I then execute the statement filling the placeholder with days 1..31


There has to be a better way to do this since the select has to go through every row in the table. Any ideas?


thanks,
scot b.