Thread: Couple simple(?) questions...
Good time of the day!
I'm a real novice to PostgreSQL, so please be patient with me :))
I've run into few questions I can't find answers to... Will appreciate
any responce.
1. Is it possible to SELECT from two databases? Something like
SELECT db1.table1.field, db2.table1.fields ...
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?
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.
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)?
PS. Will appreciate any points to docs on security of web access to
PostgreSQL.
Best regards,
Dmitri ( mailto:dmitri@listsoft.ru )
http://www.listsoft.com/
On Sun, Nov 26, 2000 at 04:56:21AM +0300, some SMTP stream spewed forth: > Good time of the day! > > I'm a real novice to PostgreSQL, so please be patient with me :)) > I've run into few questions I can't find answers to... Will appreciate > any responce. > > 1. Is it possible to SELECT from two databases? Something like > SELECT db1.table1.field, db2.table1.fields ... Nyet. Not too beink done until I think 7.1 (?). http://www.postgresql.org/docs/todo.html Under "Exotic Features" -- Exotic feature? Hm. gh
Dmitri Touretsky wrote: > 1. Is it possible to SELECT from two databases? Something like > SELECT db1.table1.field, db2.table1.fields ... No. So I use the following table naming convention: dbname1_tablename1 dbname1_tablename2 ... dbname1_tablename5 dbname2_tablename1 dbname2_tablename2 ... dbname2_tablename5 This provides me with easier logical groupings. > 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? Well, perhaps you could try making a compound field out of id and string1, or use a sub-select. It really depends on what you're trying to do... (get entries with unique id numbers _and_ unigue string1? Unique id numbers _or_ unique string1? Return unique string1 fields, which you generate from unique id fields?) > 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. Well, either web user can, or cannot. You rules seem to indicate that even if the web user was submitting bad data, table2 would *still* be updated. If the web user was overloading table1 in a hacking attempt, anotther script would still be updating with bad data. If you need to secure table2, you should not allow the system to automatically pass any data through, at all. > 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)? Well, you could have a different (non web) script perform sanity checks, and then update the tables (say, a perl script), but if their data is being used to update it, then it doesn't really matter what user id is involved. You can also grant update-only priviges on a table. You can also keep two versions of the data, "web" (dirty) and "admin" (clean). You can also just put this data in it's own table (table 3), which you use with table 1 and table 2. > PS. Will appreciate any points to docs on security of web access to > PostgreSQL. Well, it's not pretty. Unless you web user is changing its username for the connection, you're always connecting as one user, usually "www" or "nobody" or "httpd". You might want to specify how you're connecting to it over the web, as PHP has different issues from a Perl CGI, etc. (The connection type, and how it's handled, can have a big impact.) Are you using DBD/DBI? PHP? Something else? -Ronabop -- Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine, which is currently in MacOS land. Your bopping may vary.
On Sat, Nov 25, 2000 at 08:11:31PM -0700, some SMTP stream spewed forth: > Dmitri Touretsky wrote: > > 1. Is it possible to SELECT from two databases? Something like > > SELECT db1.table1.field, db2.table1.fields ... > > No. So I use the following table naming convention: > dbname1_tablename1 > dbname1_tablename2 > ... > dbname1_tablename5 > dbname2_tablename1 > dbname2_tablename2 > ... > dbname2_tablename5 > > This provides me with easier logical groupings. Yes, but removes any "physical" seperation of the db1 and db2 tables. e.g. You would have to resort to some trickery to backup only one set of tables. My point being simply to affirm that such a "feature" would be very much welcomed -- as I believe it is in 7.1.(?) > *snip* gh
Dmitri Touretsky <dmitri@listsoft.ru> writes:
> 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?
That doesn't seem very well defined. Your SELECT DISTINCT implies
that there may be multiple entries with the same string1 value, but
perhaps different ord values. Which ord value are you expecting that
string1 value to be sorted by?
Most likely you'll find that you need to do the SELECT DISTINCT into
a temp table, and then SELECT from the temp table with the desired
output ordering. In 7.1 it'll be possible to do this in a single
query using subselect-in-FROM, but in 7.0 that feature isn't there.
regards, tom lane
Приветствую! >> 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 )