Re[2]: Couple simple(?) questions... - Mailing list pgsql-novice
From | Dmitri Touretsky |
---|---|
Subject | Re[2]: Couple simple(?) questions... |
Date | |
Msg-id | 1941899336.20001126083749@listsoft.ru Whole thread Raw |
In response to | Re: Couple simple(?) questions... (Ron Chmara <ron@Opus1.COM>) |
List | pgsql-novice |
Приветствую! >> 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@listsoft.ru )
pgsql-novice by date: