Re: Couple simple(?) questions... - Mailing list pgsql-novice

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.