Re: [GENERAL] More Rule creation problems (and nowhere near 8K)
Neil Burrows wrote: > Hi, > > I am having a problem trying to create a rule on a view and keep getting > > ERROR: DefineQueryRewrite: rule plan string too big. > > I've looked though the mailing list and docs and have seen mentions that > the limit of a rule is 8192 characters. The rule (see below) is no more > than 600 characters as it is and the error still appears even when all the > whitespace is removed. If I read the previous comments on this topic correctly, it's not the length of your query that matters, but rather the length of what your query gets translated into... a very annoying bug, indeed. This is the number one barrier to my ODBC use as it makes views pretty much unusable. Cheers, Ed Loehr
[GENERAL] Postgresql back-end - INTEGRATED - Excell Spreedsheets
Hello All, I am looking for opinions and suggestions on setting up integration between a back-end PostgreSQL database engine hosted on a web server and a client using Excel spreadsheets to store and print internal product information and pricing. Here's the scenario: I am using a Linux web server, Apache, PostgreSQL, PHP3. The client's secretaries input all product information currently into Excel spreadsheets, at there office location on there workstations, dial-up Internet connection, they will not at the current time switch to complete web integration. Each month they update product pricing and new products additions into the excel spreadsheet. On the client's web site we have a back-end database with the same product and pricing information. The problem, I need to develop a way for the client to upload an exported excel spreadsheet in tab-deliminated format to the server and rebuild or update the PostgreSQl database on the web to reflect the changes. My first thought on the situation would be to write the proper scripts to allow the client to upload the file to the web server, then click on a button via a web browser that would run a compiled PHP command to grad the uploaded file, put it into a variable and run the command psql pg_dump and rebuild the database with the updated information. This seems inefficient however, along with the problem of the server running as 'nobody' and I am wondering if a more efficient ways exists and if you all would have any suggestions? Thank you everyone, Blake S.
[GENERAL] More Rule creation problems (and nowhere near 8K)
Hi,
I am having a problem trying to create a rule on a view and keep getting
ERROR: DefineQueryRewrite: rule plan string too big.
I've looked though the mailing list and docs and have seen mentions that
the limit of a rule is 8192 characters. The rule (see below) is no more
than 600 characters as it is and the error still appears even when all the
whitespace is removed.
Is this correct and if so what is the actual limit for the length of a
rule?
I am currently using PostgreSQL 6.5.3.
Thanks.
--
-- Rule to INSERT & add correct date
--
CREATE RULE customer_insert AS ON INSERT TO customers
DO INSTEAD INSERT INTO customer_table
VALUES (NEXTVAL('customer_seq'), new.company, new.forename,
new.surname,LOWER(new.username), new.password, new.address1,
new.address2, new.address3, new.town, new.postcode, new.day_phone,
new.evening_phone, new.fax, new.mobile, new.package,
current_datetime(), current_datetime(), new.suspended,
new.suspended_date, new.deleted, new.deleted_date, new.comments);
Regards,
---[ Neil Burrows ]-
E-mail: [EMAIL PROTECTED] | You're only young once, but
Web : http://www.remo.demon.co.uk/ | you can stay immature forever
---< PGP Key available from http://www.remo.demon.co.uk/pgp/ >
Re: [GENERAL] cgi with postgres
On Fri, 14 Jan 2000, Jeff MacDonald wrote:
> hey folks,
>
> this is a security issue i'd like to get some info
> on, i'm sure it's more with cgi than postgres, but
> heck.
>
> issue: how to secure cgi's that access postgres
>
> problem: passwords for postgres database are stored
> in plain text in scripts. (lets assume, perl,
> not a compiled language)
>
> points:
> make cgi dir 711
> big deal, they can get the name of the file
> from the web, and copy it.
>
> set an obscure cgi script alias in apache
> big deal, they can read the cgi conf file.
Side point ... why isn't the apache conf file secure? Only user root
needs to be able to read it, no?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] problem with date range
have a query like so: select crimeid, areaid, sum( CASE when dateof='1-8-2000' then total else 0 end) as crimes1 from stats GROUP BY crimeid, areaid; This works and gives me results like I want like so: crimeid|areaid|crimes1 ---+--+--- 4| 2| 0 5| 2| 0 5| 3| 20 6| 2| 0 7| 2| 0 8| 2| 0 9| 2| 0 10| 2| 0 11| 2| 0 12| 2| 0 But what I really need would be the same query as above but give me results which show total crimes for the Current year to date, and also total crimes for 'last week' Im wanting 'last week' to be the last complete week. Say today is 01/14/2000 the last complete week would be 01/02/2000 --> 01/08/2000 Any suggestions would be appreciated. Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] GRANT ALL ON * TO username?
I'd like to grant read-access to every single DB object in a database for one user. I'd hoped for GRANT SELECT ON * TO but the '*' syntax is wrong and I don't see an alternative in the docs... How is this done? Cheers, Ed Loehr
Re: [GENERAL] cgi with postgres
* Jeff MacDonald <[EMAIL PROTECTED]> [000114 14:07] wrote: > alfred, that seems like a very reasonable solution, > > in regard to the other chaps responce, i'm not worried > about web users anyway, cause they can't see the perl > source. it's users on the system i'd like to protect > against. I'm not sure what you mean, but there is a problem, unless you execute the scripts as a user other than the default cgi user then you may run into problems because then people can craft a cgi and run it through the server to gain access to the 700 dir, you'll either need some sort of setuid (to a special user, not root) or use some sort of cgiwrapper. -Alfred > > On Fri, 14 Jan 2000, Alfred Perlstein wrote: > > > * Jeff MacDonald <[EMAIL PROTECTED]> [000114 13:38] wrote: > > > hey folks, > > > > > > this is a security issue i'd like to get some info > > > on, i'm sure it's more with cgi than postgres, but > > > heck. > > > > > > issue: how to secure cgi's that access postgres > > > > > > problem: passwords for postgres database are stored > > > in plain text in scripts. (lets assume, perl, > > > not a compiled language) > > > > > > points: > > > make cgi dir 711 > > > big deal, they can get the name of the file > > > from the web, and copy it. > > > > how about sourcing a conf file that's in a 700 dir? > > > > > > > > set an obscure cgi script alias in apache > > > big deal, they can read the cgi conf file. > > > > > > this is assuming they already have an account > > > on the machine, something that cannot be ruled > > > out. > > > > > > question in short: how to make perl accessing databases > > > more secure, so any jack can't modify a database. > > > > > > thanks in advance. > > > > > > Jeff MacDonald > > > [EMAIL PROTECTED] > > > > > > > -- > > -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] > > > > Jeff MacDonald > [EMAIL PROTECTED] > > === > So long as the Universe had a beginning, we can suppose it had a > creator, but if the Universe is completly self contained , having > no boundry or edge, it would neither be created nor destroyed > It would simply be. > === > -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
[GENERAL] New To List
Hello All, I'm not really sure how joining the list works but I wanted to say hello. I am new to databases and also to Postgres. Started reading the book up on the site. Very helpful. I do have a question though. I am trying to take a DB written for MySql and convert the format of statements over to Postgres for creation. I can't seem to find a value 'blob' in PG. Is there something similar?? Also if anyone can help me with this (see below). I can't figure out how to make a primary, unique key. Here's the sql file: CREATE TABLE session ( sid varchar(255) NOT NULL, val blob NOT NULL, changed varchar(14) NOT NULL, PRIMARY KEY (sid), UNIQUE sid_2 (sid) ); Again any help or pointers in the right direction is greatly appreciated. Kind regards, Harry Hoffman Product Systems Specialist Restaurants Unlimited Inc. (206) 634-3082 x. 270
Re: [GENERAL] cgi with postgres
alfred, that seems like a very reasonable solution, in regard to the other chaps responce, i'm not worried about web users anyway, cause they can't see the perl source. it's users on the system i'd like to protect against. On Fri, 14 Jan 2000, Alfred Perlstein wrote: > * Jeff MacDonald <[EMAIL PROTECTED]> [000114 13:38] wrote: > > hey folks, > > > > this is a security issue i'd like to get some info > > on, i'm sure it's more with cgi than postgres, but > > heck. > > > > issue: how to secure cgi's that access postgres > > > > problem: passwords for postgres database are stored > > in plain text in scripts. (lets assume, perl, > > not a compiled language) > > > > points: > > make cgi dir 711 > > big deal, they can get the name of the file > > from the web, and copy it. > > how about sourcing a conf file that's in a 700 dir? > > > > > set an obscure cgi script alias in apache > > big deal, they can read the cgi conf file. > > > > this is assuming they already have an account > > on the machine, something that cannot be ruled > > out. > > > > question in short: how to make perl accessing databases > > more secure, so any jack can't modify a database. > > > > thanks in advance. > > > > Jeff MacDonald > > [EMAIL PROTECTED] > > > > -- > -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] > Jeff MacDonald [EMAIL PROTECTED] === So long as the Universe had a beginning, we can suppose it had a creator, but if the Universe is completly self contained , having no boundry or edge, it would neither be created nor destroyed It would simply be. ===
Re: [GENERAL] cgi with postgres
Jeff MacDonald wrote: > > hey folks, > > this is a security issue i'd like to get some info > on, i'm sure it's more with cgi than postgres, but > heck. > > issue: how to secure cgi's that access postgres > > problem: passwords for postgres database are stored > in plain text in scripts. (lets assume, perl, > not a compiled language) > > points: > make cgi dir 711 > big deal, they can get the name of the file > from the web, and copy it. > > set an obscure cgi script alias in apache > big deal, they can read the cgi conf file. > > this is assuming they already have an account > on the machine, something that cannot be ruled > out. > > question in short: how to make perl accessing databases > more secure, so any jack can't modify a database. > > thanks in advance. > > Jeff MacDonald > [EMAIL PROTECTED] I'm not sure if this is definitive - hackers are very clever, but this SHOULD do it: 1. httpd runs as user 'nobody' 2. 'nobody' doesn't have a shell account (shell = /bin/false) 3. no one can 'su' to 'nobody', except root (obviously) 4. pg_hba.conf allows only local connections (127.0.0.1) 5. the postmaster isn't running with -i 6. 'nobody' owns the database and has granted privileges to only those other users as appropriate 7. The password (as added protection) is in some file readable by user 'nobody' only. Hope that helps, Mike Mascari
Re: [GENERAL] cgi with postgres
* Jeff MacDonald <[EMAIL PROTECTED]> [000114 13:38] wrote: > hey folks, > > this is a security issue i'd like to get some info > on, i'm sure it's more with cgi than postgres, but > heck. > > issue: how to secure cgi's that access postgres > > problem: passwords for postgres database are stored > in plain text in scripts. (lets assume, perl, > not a compiled language) > > points: > make cgi dir 711 > big deal, they can get the name of the file > from the web, and copy it. how about sourcing a conf file that's in a 700 dir? > > set an obscure cgi script alias in apache > big deal, they can read the cgi conf file. > > this is assuming they already have an account > on the machine, something that cannot be ruled > out. > > question in short: how to make perl accessing databases > more secure, so any jack can't modify a database. > > thanks in advance. > > Jeff MacDonald > [EMAIL PROTECTED] > -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
Re: [GENERAL] cgi with postgres
On Fri, Jan 14, 2000 at 04:55:02PM -0400, Jeff MacDonald wrote: > this is a security issue i'd like to get some info > on, i'm sure it's more with cgi than postgres, but > heck. > First off, if the server is set up correctly a casual user should not be able to browse the cgi-bin directory and see your code. I'm not sure what server you are creating your scripts on, but if it is Apache and mod_perl is available to you then this is even better. You can create a handler in mod_perl for a "pseudo-directory" and hide your code that way. However, as I said in my first paragraph this should not be necessary as normally web browsers can't browse the cgi-bin directory anyway and your cgi-script should just send back to the browser html code and not the perl code itself. Unless something is very very wrong -- -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Peter L. Berghold[EMAIL PROTECTED] "Linux renders ships http://www.berghold.net NT renders ships useless"
[GENERAL] cgi with postgres
hey folks, this is a security issue i'd like to get some info on, i'm sure it's more with cgi than postgres, but heck. issue: how to secure cgi's that access postgres problem: passwords for postgres database are stored in plain text in scripts. (lets assume, perl, not a compiled language) points: make cgi dir 711 big deal, they can get the name of the file from the web, and copy it. set an obscure cgi script alias in apache big deal, they can read the cgi conf file. this is assuming they already have an account on the machine, something that cannot be ruled out. question in short: how to make perl accessing databases more secure, so any jack can't modify a database. thanks in advance. Jeff MacDonald [EMAIL PROTECTED] === So long as the Universe had a beginning, we can suppose it had a creator, but if the Universe is completly self contained , having no boundry or edge, it would neither be created nor destroyed It would simply be. ===
Re: [GENERAL] How to get number of the week from datetime?
"Hojdar Karel Ing." wrote:
> Hi,
>
> how I can get some agregates (avg, min, max) for whole week (in specified
> year)?
> For example from table with two columns : time datetime and value float8.
> And I want to get average of value based on whole weeks. If I try to use
> date_part('week',time) - Postgresql doesn't recognize word 'week'.
>
> Thanks Karel
>
>
select date_part('dow',current_date);
If you are looking for a function to calculate the no. week of the year...
try the attached function.
José
-- ISO-8601 assigns a number to each week of the year.
-- A week that lies partly in one year and partly in another is
-- assigned a number in the year in which most of its days lie.
-- This means that:
-- Week 1 of any year is the week that contains 4 January.
-- or equivalently
-- Week 1 of any year is the week that contains the first Thrusday
-- in January .
-- If the week is 0 that means that first week is less than 4 days.
-- returns the week number of the year (1 - 53)...
drop function week(date);
create function week(date) returns int2 as
'declare
p int2;
i int2;
weekint4;
yeartext;
start date;
difftimespan;
maisint2;
txt text;
begin
year:= date_part(''year'',$1);
if textlen(year) = 1 then
year:= ''000'' || year;
end if;
if textlen(year) = 2 then
year:= ''00'' || year;
end if;
start:= year || ''-01-01'';
week:= date_part(''dow'',start);
if week > 3 or week = 0 then
mais:= 0;
else
mais:= 1;
end if;
week:= date_part(''dow'',start);
diff:= date_part(''epoch'',$1) - date_part(''epoch'',start);
txt:= diff;
p:= textpos(txt,'' '');
if p = 0 then
i:= 0;
else
i:= substr(txt,1,p - 1);
end if;
return (i + week) / 7 + mais;
end;
' language 'plpgsql';
select week('1997-01-01'), week('1997-12-31');
select week('1998-01-01'), week('1998-12-31');
select week('1999-01-01'), week('1999-12-31');
select week(current_date);
Re: Oids vs Serial fields (was Re: [GENERAL] searching oid's)
> Adriaan Joubert wrote: > > > > Yes oids get dumped with the -o flag. That is why I said automatically. Fact > > remains that you cannot manipulate oids. Should you ever want to copy a table into > > an exisiting system you would have to do a new initdb to make sure that the oids > > in your table are not in use. And if anything ever gets corrupted it is much > > harder to recover and fix it, as you have no control over the oid values that the > > system assigns. I would definitely recommend a separate serial value, and I > > believe this is also what is recommended in the postgres docs. > > You make some good points, but where is this recommended in the docs? I'd like > to see more of "serial vs oid" if there is something. > > Bruce's book doesn't recommend one over the other, though it does mention some > oid limitations. It doesn't mention that the sequence for a serial value isn't > dropped when its table is, btw. Is there some issue in the comparison I missed? That is a good point about the sequence not being dropped. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Date format
Someone posted a message here asking how you set the date style. We went
through
- environment variable PGDATESTYLE
- SET DateStyle TO
- the -e flag to the backend
but of course if you are using ISO it isn't necessary!! There is no
ambiguity in
test=> create table tab (t datetime);
CREATE
test=> insert into tab values('2000-1-2');
INSERT 416553 1
test=> select * from tab;
t
--
Sun 02 Jan 00:00:00 2000 GMT
(1 row)
The -e exists because there would be ambiguity between European/US, but with
year first, it must be -mm-dd (at least for sufficiently large years).
Sorry I can't remember your email address.
Cheers,
Patrick
