Re[2]: Couple simple(?) questions...

Приветствую!

>> 2. There is a table of the following structure:
>> CREATE TABLE test (
>> id int4,
>> string1 text,
>> string2 text,
>> ord int2);
>> I need to get a list of different "string1" strings ordered by
>> "ord". But SELECT DISTINCT id, string1 ... ORDER BY ord" doesn't
>> work. Is there any way to get it?

RC> Well, perhaps you could try making a compound field out of id and
RC> string1, or use a sub-select. It really depends on what you're trying
RC> to do... (get entries with unique id numbers _and_ unigue string1? Unique
RC> id numbers _or_ unique string1? Return unique string1 fields, which
RC> you generate from unique id fields?)

Sorry, my fault. I need to get a list of DISTINCT string1 along with
corresponding ids, and this list should be ordered according to the
value of ord. I know how to do that with intermediate tables, but may
be there is a way to put it a single query?..

>> 3. I have two tables, say table1 and table2. Need to let web users
>> insert data into one of the tables. So far it's easy. Hard point:
>> web-user should be able insert rows into table1 and at the same time
>> update related rows in table2. And I need to maximally secure table2.

RC> Well, either web user can, or cannot. You rules seem to indicate that
RC> even if the web user was submitting bad data, table2 would *still* be
RC> updated. If the web user was overloading table1 in a hacking attempt,
RC> anotther script would still be updating with bad data. If you need
RC> to secure table2, you should not allow the system to automatically
RC> pass any data through, at all.

The matter is that I need to update a single field in the second
table and don't let web users access all other fields. But I can't
set a diffrent access rights to different fields in the table, do I?
Again, I can do that by creating two tables instead of the table2, but
it would be much simplier to update table2 under different user (if
it is possible at all) - I have quite a bit of such places in my
database :(

>> I've tried to create a view based on table1 and a set of rules, but
>> rules are applied with web username. So if I grant web users right to
>> update table2 than I can't protect this table; and if I don't grant
>> them those rights than I don't see a way to update rows in table2.
>> Question: is there a way to run rule on behalf of different user?
>> Something like setusername(user)?

RC> Well, you could have a different (non web) script perform sanity checks,
RC> and then update the tables (say, a perl script), but if their
RC> data is being used to update it, then it doesn't really matter
RC> what user id is involved.

That's how I've implemented it right now, but I thought that setting
rules would be "better habit" :))

RC> You can also grant update-only priviges
RC> on a table.

In this case they'll be able to update fields they shouldn't have
access to... My major concern is that I _must have_ network access to
the database with password security, as well as access through CGI
scripts. If user somehow get a text of the script then he'll see a
username/password and will be able to connect to the database.
If he have an update right on the table he can update _all_ the
data there...

>> PS. Will appreciate any points to docs on security of web access to
>> PostgreSQL.

RC> Well, it's not pretty. Unless you web user is changing its username
RC> for the connection, you're always connecting as one user, usually "www"
RC> or "nobody" or "httpd". You might want to specify how you're connecting
RC> to it over the web, as PHP has different issues from a Perl CGI, etc.
RC> (The connection type, and how it's handled, can have a big impact.)
RC> Are you using DBD/DBI? PHP? Something else?

Currently I'm using Perl CGI, but have plans switching to PHP...
Actually it's not matter - as I've mentioned above I'm a complete
novice to using databases over the net, so I'm just studying
everything from nearly zero...

Best regards,
Dmitri ( mailto:dmitri(at)listsoft(dot)ru )