Re: [GENERAL] Errors with run_build.pl - 8.3RC2
On Tue, Jan 22, 2008 at 11:52:08AM +, cinu wrote: > Even though these errors are existing, at the end the > latest version is getting downloaded and when I do a > regression testing it goes through. Can anyone give me Regression tests have to test error handling too, so some errors might exactly be what you should see. > ERROR: duplicate key value violates unique constraint > "test_pkey" > STATEMENT: insert into test ( i , j ) values ( 7 , > 12 ) > ERROR: more than one row returned by a subquery used > as an expression > STATEMENT: select i from test where j = ( select j > from test) > LOG: unexpected EOF on client connection > ERROR: relation "nonexistant" does not exist > STATEMENT: select * from nonexistant These errors are supposed to be there. Comments in the code even tell you why. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing PGSQL
On 22 jan, 22:46, [EMAIL PROTECTED] ("Jamiil Abduqadir") wrote:
> I am trying to install PosgreSQL using postgresql-8.3-dev1 on my WindowsXP
> machine, but I get a message that reads
>
> Fail to create a temporary directory
>
> Does anyone know why I am getting this error message?
>
> Thank in advance
> --
> Happiness has many doors, and when one of them closes another opens, yet we
> spent so much time looking at the one that is shut that we don't see the
> one that just opened.
It seems to be a windows error, maybe in your temporary path.
1) Verify your TMP and TEMP environment variables (values must
reference valid directory entries);
2) Is there sufficient disk space for extract files from the
PostgreSQL package into TMP and TEMP path reference on the environment
vars?
3) Also check out your user settings. For installation, you must log
in with an administrative role (in Windows).
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Postgresql + digital signature
On 1/23/08, Luis Alberto Pérez Paz <[EMAIL PROTECTED]> wrote: > I'm working in a project which is using postgres (great database!, I love > it) > > We're in a stage where I need to implement a mechanism to prevent the data > modification. > > I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If there's a > modification, the signature doesn't verify. > > > However before start I need your help to know: > > Is there in postgres something functionality like this? > Does any know if there's something similar another database system? There is hmac() in pgcrypto, basically digest() with key. It should be enough if you are ok with symmeric keys. For public keys there is also pgp_pub_encrypt/decrypt but not sign/verify. You emulate them with digest() + pub_encrypt, but that would be ugly, you are better off doing proper sign/verity in client. Another path would be to look for PLs that have module for sign+verify - I'd guess that both plpythonu and plperlu should have those. -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql source build instructions for ubuntu 7.04
On 22 jan, 05:02, [EMAIL PROTECTED] (Julio Cesar Sánchez González) wrote: > Jon Hancock wrote: > > The INSTALL file for postgresql 8.3rc1 lists the following install > > instructions: > > > ./configure > > gmake > > su > > gmake install > > adduser postgres > > mkdir /usr/local/pgsql/data > > chown postgres /usr/local/pgsql/data > > su - postgres > > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data > > /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & > > /usr/local/pgsql/bin/createdb test > > /usr/local/pgsql/bin/psql test > > > I get to the 4th step and see I need gmake. I use make instead. > > Things seem ok. > > > Then all is well, until line 10: /usr/local/pgsql/bin/initdb -D /usr/ > > local/pgsql/data > > The bin directory is not accessible to anyone but root due to the > > permissions of the install. Should I have used a different umask for > > the make/install process? > > > As I'm new to pg, what should I change the permissions to? Should I > > make the entire bin directory executable by anyone? > > > Does anyone have experience with installing from source on ubuntu > > 7.04? > > > thanks, Jon By the way, if you want compile PostgreSQL and all of its features on Ubuntu, you'll need to do some sets and install some extra packages (make sure your universe and multiverse repositories are working fine). 1) Installing all necessary packages for PostgreSQL: sudo apt-get install g++ libreadline5-dev flex bison libzzip-dev libio- zlib-perl zlib1g-dev zlib-bin zlibc zziplib-bin libperl-dev python-dev build-essential 2) Expect libperl to work fine (assume your right version of installed libperl, mine is 5.8); sudo ln -s /usr/lib/libperl.so.5.8 libperl.so 3) Expand files sudo tar -xjvf postgresql-8.2.5.tar.bz2 4) Access the source dir cd postgresql-8.2.5 5) Adding group and user sudo groupadd postgres sudo adduser -g postgres postgres 6) Configure (please fill the options with your preferences) sudo ./configure --prefix=/usr/local/postgres --bindir=/usr/bin -- sysconfdir=/etc/postgres --with-perl --with-python 7) Do make! sudo make sudo make install 8) Create postgresql folder and set the owner to postgres group and user sudo mkdir /usr/local/postgres/data sudo chown postgres:postgres /usr/local/postgres/data 9) Change the current user to postgres sudo su postgres 10) Start a new cluster (choose your enconding, mine is latin1) initdb -E latin1 -D /usr/local/postgres/data And there you go! If you get any errors, please fell free to contact me on (adamitj at gmail dot com). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Tips for upgrading from 7.4
I'm working with someone who is about to upgrade from (I believe) pg 7.4 to pg 8.1. What would be the best resource to read to look for "gotchas"? Release notes? At the moment, we don't care about performance problems, only things that might break. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [OT] RAID controllers blocking one another?
Am 2008-01-18 16:54:17, schrieb Hannes Dorbath: > Sean Davis wrote: > >150-200MB/s writing and somewhat faster for reading > > That actually seems dead slow. Whatever RAID level you configured, there > is no sane way for it to be that slow. Is this a RAID 5/6 array? Did you > forgot to align your file system to stripe boundaries? I have had such rates on a 3w85xx with 4+1 WD Raptor WD740 in Raid-5 (the OS was on a Raid-1 with 2+1 WD360) The 3w95xx shoud do more, but with the new Hitachi 1 TByte I do not get more then 140 MBit. It seems, the drives are not so performant. Thanks, Greetings and nice Day Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSN LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
[GENERAL] pg_xlog and standby
hello everybody:
im trying to reconfigure a warm-standby server. the problem is that
for some reason, one day the standby server stopped recovering the
archives. this leaded to a full disk on that server, so i turned off
(commented) the archive_command on the main server.
i want to restart the procedure described in
http://www.postgresql.org/docs/8.1/interactive/backup-online.html#BACKUP-PITR-RECOVERY
but i dont know how to "safely clean" the main server $DATA/pg_xlog/
dir.
with "safely clean" i mean how do i know which archives can i delete
(or move somewhere) without disrupting the normal operation of the
server.
im using postgres 8.2.5 from source on debian etch.
thanks in advance!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Tips for upgrading from 7.4
Rick Schumeyer <[EMAIL PROTECTED]> writes: > I'm working with someone who is about to upgrade from (I believe) pg 7.4 > to pg 8.1. > What would be the best resource to read to look for "gotchas"? Release > notes? At the moment, we don't care about performance problems, only > things that might break. Reading the release notes is good, but you really really should test the application(s) against a test 8.1 installation before you go live ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vacuum, dead rows, usual solutions didn't help
Erik Jones wrote: On Jan 10, 2008, at 6:01 AM, Simon Riggs wrote: On Thu, 2008-01-10 at 11:18 +0100, Gábor Farkas wrote: Simon Riggs wrote: also, even if it is wrong, can an 'idle-in-transaction' connection that was opened today block the vacuuming of rows that were deleted yesterday? Yes, if the rows were deleted after the connection started. to avoid any potential misunderstandings, i will summarize the situation: 1. the vacuum-cronjob refuses to remove dead rows since 1.jan.2008. 2. i know that no postgres-process is older than 7.jan.2008. (from "ps aux | grep postgres", and except the postgres-system-processes) how can this happen? They might be different set of dead rows, just roughly the same numbers each day. Or, put another way, this is probably the same problem recurring, not one constant instance of the issue. unfortunately, i do not think that's the case, here is why: this vacuum-process is running every hour, and i have the logs from roughly 450 vacuum runs. so, for one specific table, that had these unremovable rows: the number of "removable dead rows" was between 0 and 11, and the number of "unremovable dead rows" grew by a number between 0 and 41106 every hour (it was three times zero, and the rest was between 86 and 41106). so i do not think it happened with different rows, just roughly the same number. on the good side, we changed the code for that one process, that kept being in "idle in transaction", and now the vacuuming works nicely. and this is still a mystery for me, because i understand that idle-in-transaction is wrong, but even so, a process that i start today, in my opinion simply cannot block the recovery of dead rows, that were deleted yesterday. but i'm probably misunderstanding something, so if i will have some more time for this in the future, i will read more about mvcc, and maybe start a thread here :-) thanks for all your help, gabor ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tips for upgrading from 7.4
Tom Lane wrote: Rick Schumeyer <[EMAIL PROTECTED]> writes: I'm working with someone who is about to upgrade from (I believe) pg 7.4 to pg 8.1. What would be the best resource to read to look for "gotchas"? Release notes? At the moment, we don't care about performance problems, only things that might break. Reading the release notes is good, but you really really should test the application(s) against a test 8.1 installation before you go live ... regards, tom lane We plan on testing. I'm asking if there is anything that "everyone knows" will break that we might as well fix before testing. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] vacuum, dead rows, usual solutions didn't help
=?ISO-8859-1?Q?G=E1bor_Farkas?= <[EMAIL PROTECTED]> writes: > and this is still a mystery for me, because i understand that > idle-in-transaction is wrong, but even so, a process that i start today, > in my opinion simply cannot block the recovery of dead rows, that were > deleted yesterday. Well, it's not one but two longest-transaction-lifetimes. That is, take the oldest transaction that's running now, and consider the oldest transaction that was running when it started. VACUUM can reclaim rows that were deleted by transactions that started before that one. You didn't explain your application's behavior exactly, but does that help? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_xlog and standby
On Jan 23, 2008, at 9:28 AM, Roberto Scattini wrote: hello everybody: im trying to reconfigure a warm-standby server. the problem is that for some reason, one day the standby server stopped recovering the archives. this leaded to a full disk on that server, so i turned off (commented) the archive_command on the main server. i want to restart the procedure described in http://www.postgresql.org/docs/8.1/interactive/backup- online.html#BACKUP-PITR-RECOVERY but i dont know how to "safely clean" the main server $DATA/pg_xlog/ dir. with "safely clean" i mean how do i know which archives can i delete (or move somewhere) without disrupting the normal operation of the server. im using postgres 8.2.5 from source on debian etch. thanks in advance! You don't. The main server should not be keeping archived WAL files directly in pg_xlog/. As it queues WAL files to be archived it puts them in pg_xlog/archive_status/ with file names suffixed with .ready, once they are archived that suffix changes to .done after which, at some point (I'm not sure how long/many) they are removed. Now, if you took your standby server offline, but didn't disable your archive_command then you've basically been accumulating WALs with the .ready prefix in the archive_status directory that, if you're going to start from scratch with your standby, you can safely delete. Just make sure you have a couple of WAL files successfully archived (suffix has changed to .done in the archive_status dir and you've verified that they've reached whatever directory your standby expects them to be in) before call pg_start_backup() and starting your new base backup. IMO, the most important point to be had here is DO NOT delete WALs that sit directly under pg_xlog/. Mistakes with the rest can be worked with, you could run into serious problems with your primary when deleting WALs directly under pg_xlog/. Also, do you know why your standby stopped recovering? I'd say you should make sure you know why and how, otherwise you run the risk of the same thing happening again. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tips for upgrading from 7.4
On Jan 23, 2008, at 10:26 AM, Tom Lane wrote: Reading the release notes is good, but you really really should test the application(s) against a test 8.1 installation before you go live ... be sure to run *every* query your system uses through 8.1. the most common problems you will run into are issues relating to using strings as integers which tended to work in most cases in 7.x but not in 8.x. the release notes cover such changes. pay particular attention to changes in auto typecasts. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql + digital signature
We're in a stage where I need to implement a mechanism to prevent the data modification. I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If there's a modification, the signature doesn't verify. Like all such solutions, the key (lame pun intended) is how to do you manage the keys? Obviously, when the digitally signed data is inserted, the private key must be accessible. If you then do an update and also have access to the keys, then new digitally signed data would be there. Is there no way for your application to ensure that once data is inserted, it cannot be changed? You can also grant database access with just SELECT,INSERT permissions so that an UPDATE and DELETE are not allowed. We store lots of digitally signed data as BLOBs in PG, but control this at the application level since it's the one that has access to the private key, and our application has no UPDATE/DELETE calls. Good luck, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tips for upgrading from 7.4
Vivek Khera <[EMAIL PROTECTED]> writes: > On Jan 23, 2008, at 10:26 AM, Tom Lane wrote: >> Reading the release notes is good, but you really really should test >> the >> application(s) against a test 8.1 installation before you go live ... > be sure to run *every* query your system uses through 8.1. the most > common problems you will run into are issues relating to using strings > as integers which tended to work in most cases in 7.x but not in 8.x. BTW, one other question to answer at this point, rather than down the road, is "why 8.1"? If you're going to have to engage in a testing and porting effort anyway, you might as well move to the latest branch you can. I'd strongly recommend moving to 8.2 instead of 8.1, or maybe even 8.3 if your go-live date is more than a month or two away. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tips for upgrading from 7.4
Rick Schumeyer wrote: Tom Lane wrote: Rick Schumeyer <[EMAIL PROTECTED]> writes: I'm working with someone who is about to upgrade from (I believe) pg 7.4 to pg 8.1. What would be the best resource to read to look for "gotchas"? Release notes? At the moment, we don't care about performance problems, only things that might break. Reading the release notes is good, but you really really should test the application(s) against a test 8.1 installation before you go live ... regards, tom lane We plan on testing. I'm asking if there is anything that "everyone knows" will break that we might as well fix before testing. Only to the extent that "everyone knows" the details of your PG configuration and all connecting applications. Print the release-notes chain and start reading/scribbling on it. Then expect to discover that you don't know your apps as well as you thought. We have encountered relatively few difficulties - most of ours revolved around subtle changes to both automatic and explicit casting. For example given and input of '0' you might see a change from: select ' 0 '::int; ERROR: invalid input syntax for integer: " 0 " to this: select ' 0 '::int; int4 -- 0 If you are already scrupulous about formatting and casting, you are probably OK. If you relied on "getting away with" certain loose behavior, you might find bugs. BTW, why not upgrade to the latest version? Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Postgresql + digital signature
Very interesting point of view. Yes, you're right about the manage key problem. The grant database access looks like a real solution. Thanks a lot for your advice. Best Regards, Luis Alberto Perez Paz On Jan 23, 2008 11:20 AM, David Wall <[EMAIL PROTECTED]> wrote: > > > We're in a stage where I need to implement a mechanism to prevent the > > data modification. > > > > I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If > > there's a modification, the signature doesn't verify. > Like all such solutions, the key (lame pun intended) is how to do you > manage the keys? Obviously, when the digitally signed data is inserted, > the private key must be accessible. If you then do an update and also > have access to the keys, then new digitally signed data would be there. > > Is there no way for your application to ensure that once data is > inserted, it cannot be changed? > > You can also grant database access with just SELECT,INSERT permissions > so that an UPDATE and DELETE are not allowed. > > We store lots of digitally signed data as BLOBs in PG, but control this > at the application level since it's the one that has access to the > private key, and our application has no UPDATE/DELETE calls. > > Good luck, > David > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- paz, amor y comprensión (1967-1994)
[GENERAL] Count
I have a table with four columns that will either be null or hold the value 'true'. I want to obtain the count of these columns, within a particular row, that have 'true' as a value (0 to 4). I have attempted the Select count method but it seems that I need something more. If anyone has any thoughts it would be much appreciated. Bob ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tips for upgrading from 7.4
Steve Crawford wrote: BTW, why not upgrade to the latest version? Cheers, Steve Mostly because its not my server :-) I've suggested that, we'll see. I appreciate the comments regarding type casting. I'll be sure to look out for that. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Count
Bob Pawley wrote: I have a table with four columns that will either be null or hold the value 'true'. I want to obtain the count of these columns, within a particular row, that have 'true' as a value (0 to 4). I have attempted the Select count method but it seems that I need something more. If anyone has any thoughts it would be much appreciated. Bob Something like this? create table t (id int, w bool, x bool, y bool, z bool); insert into t values (1,null,null,'t','t'), (1,null,'t','t',null), (2,'t',null,'t',null), (2,'t',null,'t',null), (3,null,'t','t','t'), (4,'t','t','t','t'); select id, sum(case when w is null then 0 else 1 end) as w, sum(case when x is null then 0 else 1 end) as x, sum(case when y is null then 0 else 1 end) as y, sum(case when z is null then 0 else 1 end) as z from t group by id order by id; id | w | x | y | z +---+---+---+--- 1 | 0 | 1 | 2 | 1 2 | 2 | 0 | 2 | 0 3 | 0 | 1 | 1 | 1 4 | 1 | 1 | 1 | 1 ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Best practices for protect applications agains Sql injection.
Hi all I interesting in the protect my applications that use postgresql as is database backend from Sql Injections attacks, can any recommend me best pratices or references to protected postgres from this kind of malicious users. Thanks in advanced José Manuel, Gutíerrez de la Concha Martínez.
Re: [GENERAL] Count
On Wed, 2008-01-23 at 10:50 -0800, Bob Pawley wrote: > I have a table with four columns that will either be null or hold the value > 'true'. > > I want to obtain the count of these columns, within a particular row, that > have 'true' as a value (0 to 4). > > I have attempted the Select count method but it seems that I need something > more. > > If anyone has any thoughts it would be much appreciated. 1. what did you do? 2. what did you see? 3. what did you expect? Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Count
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Bob Pawley > Sent: 23 janvier 2008 13:51 > To: PostgreSQL > Subject: [GENERAL] Count > > > I have a table with four columns that will either be null or hold the value > 'true'. > > I want to obtain the count of these columns, within a particular row, that > have 'true' as a value (0 to 4). > > I have attempted the Select count method but it seems that I need something > more. > > If anyone has any thoughts it would be much appreciated. > > Bob > Or something like this ? create table test ( id_test serial, c1 boolean, c2 boolean, c3 boolean, c4 boolean ); insert into test (c1,c2,c3,c4) values ( true, null, null, true),( true, true, null, true),( null, null, null, null); select id_test, (case when c1 is null then 0 else 1 end)+(case when c2 is null then 0 else 1 end)+(case when c3 is null then 0 else 1 end)+(case when c4 is null then 0 else 1 end) as total from test; id_test | total -+--- 1 | 2 2 | 3 3 | 0 Regards, Charles Simard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] ascii to utf-8
Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 encoding to maintain and support the extended character sets. In my naivety I thought it would be a relatively simple process to convert the db but I've found this to not be the case. I tried doing a dump and restore into a new database with the proper encoding, but pg_restore is getting hung up on one of the tables, our largest by far (~1gb, not huge I know). When I tried pg_restore from a command line (I was using pgAdmin, I know i'm a nub) I received this error. C:\Program Files\PostgreSQL\8.2\bin>pg_restore.exe -i -h 192.168.1.xxx -p 5432 -U foobar -d warehouse_utf8 -a -t "transaction" -v "O:\foo\bar\pg_dump_transaction.backup" pg_restore: connecting to database for restore Password: pg_restore: restoring data for table "transaction" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1706; 0 17861 TABLE DATA transaction foobar pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 I remember reading somewhere recently that I could use iconv to convert the ASCII encoded dump to UTF-8 encoding, but I'm currently on a windows box, and a windows server, so is there an easier way to do this? Also I was thinking perhaps it was possible to do an ETL type setup, where I can SELECT from the ASCII db and INSERT into the UTF-8 db. If you haven't gathered yet, I'm pretty in the dark regarding encoding issues, especially when applied to pg, so any help here would be appreciated. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql + digital signature
On 1/23/08, Luis Alberto Pérez Paz <[EMAIL PROTECTED]> wrote: > Very interesting point of view. > Yes, you're right about the manage key problem. > > The grant database access looks like a real solution. Eh, for some reason I imagined you have have some good reason why simple solutions are not enough... Btw, if you try to simply rrestrict access to your data, one good way for that is to make all data access and modification go via SECURITY DEFINER functions, so that user have no access to underlying data tables. This gives both more flexible access handling than simple GRANTs can give you and also give ability to do smooth schema upgrades without applications noticing. -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Count
Bob Pawley wrote: I have a table with four columns that will either be null or hold the value 'true'. Any reason why the NULL values aren't instead FALSE? NULL != FALSE. I want to obtain the count of these columns, within a particular row, that have 'true' as a value (0 to 4). SELECT (CASE WHEN t.col1 = TRUE THEN 1 ELSE 0 END) + (CASE WHEN t.col2 = TRUE THEN 1 ELSE 0 END) + (CASE WHEN t.col3 = TRUE THEN 1 ELSE 0 END) + (CASE WHEN t.col4 = TRUE THEN 1 ELSE 0 END) AS true_count FROM your_table AS t; I'm sure there's a more clever way than that, though. I have attempted the Select count method but it seems that I need something more. That would only be useful if you were counting across rows (aggregating). b ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best practices for protect applications agains Sql injection.
pepone.onrez wrote: Hi all I interesting in the protect my applications that use postgresql as is database backend from Sql Injections attacks, can any recommend me best pratices or references to protected postgres from this kind of malicious users. Thanks in advanced José Manuel, Gutíerrez de la Concha Martínez. SQL injection vulnerabilities are a product of the coding, not the database. In a typical sql injection vulnerability, the code (typically PHP or ASP, hopefully PHP) fails to sanitize the input of a parameter to a query (removing ; among other things), but the db is acting properly in such a situation. For example the query "SELECT * FROM users WHERE username = '$username';" is a pretty typical PHP generated query. if $username is input as foobar then the query "SELECT * FROM users WHERE username = 'foobar';" would work as intended. However if the username was "foobar'; DELETE FROM users;" then the query would become "SELECT * FROM users WHERE username = 'foobar'; DELETE FROM users;'" which is a perfectly legal query (except the last ' but it won't make much of a difference) and the db is acting as designed. It is the responsibility of the code to sanitize the input to keep this from happening by removing special characters such as ; and ' so there is no way (AFAIK) to utilize postgresql settings to protect against SQL injection. Check out this page: http://www.acunetix.com/websitesecurity/sql-injection.htm and this page: http://www.acunetix.com/websitesecurity/sql-injection2.htm for more information. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_xlog and standby
On Jan 23, 2008, at 2:18 PM, Roberto Scattini wrote: On Jan 23, 2008 2:28 PM, Erik Jones <[EMAIL PROTECTED]> wrote: You don't. The main server should not be keeping archived WAL files directly in pg_xlog/. As it queues WAL files to be archived it puts them in pg_xlog/archive_status/ with file names suffixed with .ready, once they are archived that suffix changes to .done after which, at some point (I'm not sure how long/many) they are removed. , ok. the problem that im having is that i have A LOT of archive files on pg_xlog dir, and thats because the archive_command keeps failing (the standby server had filled his disk with archives received but not proccesed), so now, i dont know how i can remove those files and start again... Now, if you took your standby server offline, but didn't disable your archive_command then you've basically been accumulating WALs with the .ready prefix in the archive_status directory that, if you're going to start from scratch with your standby, you can safely delete. Just make sure you have a couple of WAL files successfully archived (suffix has changed to .done in the archive_status dir and you've verified that they've reached whatever directory your standby expects them to be in) before call pg_start_backup() and starting your new base backup. IMO, the most important point to be had here is DO NOT delete WALs that sit directly under pg_xlog/. Mistakes with the rest can be worked with, you could run into serious problems with your primary when deleting WALs directly under pg_xlog/. yeah, i agree. but now i have aprox 40GB of archive files in pg_xlog dir in the production server. :S Watch your directory terminology. The WALs that have backed up should be in $PGDATA/pg_xlog/archive_status/ not $PGDATA/pg_xlog/. Since you are going to start from scratch with you're standby you're free to delete all of the WAL files in $PGDATA/pg_xlog/ archive_status/ but leave any files directly under $PGDATA/pg_xlog alone. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Best practices for protect applications agains Sql injection.
pepone.onrez wrote: Hi all I interesting in the protect my applications that use postgresql as is database backend from Sql Injections attacks, can any recommend me best pratices or references to protected postgres from this kind of malicious users. What are you using on the application side? For instance, with PHP, you might want to look into the PEAR MDB2 package (specifically, the prepared statements). brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_xlog and standby
On Jan 23, 2008 2:28 PM, Erik Jones <[EMAIL PROTECTED]> wrote:
>
> You don't. The main server should not be keeping archived WAL files
> directly in pg_xlog/. As it queues WAL files to be archived it puts
> them in pg_xlog/archive_status/ with file names suffixed with .ready,
> once they are archived that suffix changes to .done after which, at
> some point (I'm not sure how long/many) they are removed.
>
, ok. the problem that im having is that i have A LOT of
archive files on pg_xlog dir, and thats because the archive_command
keeps failing (the standby server had filled his disk with archives
received but not proccesed), so now, i dont know how i can remove
those files and start again...
> Now, if you took your standby server offline, but didn't disable your
> archive_command then you've basically been accumulating WALs with
> the .ready prefix in the archive_status directory that, if you're
> going to start from scratch with your standby, you can safely
> delete. Just make sure you have a couple of WAL files successfully
> archived (suffix has changed to .done in the archive_status dir and
> you've verified that they've reached whatever directory your standby
> expects them to be in) before call pg_start_backup() and starting
> your new base backup.
>
> IMO, the most important point to be had here is DO NOT delete WALs
> that sit directly under pg_xlog/. Mistakes with the rest can be
> worked with, you could run into serious problems with your primary
> when deleting WALs directly under pg_xlog/.
>
yeah, i agree. but now i have aprox 40GB of archive files in pg_xlog
dir in the production server. :S
> Also, do you know why your standby stopped recovering? I'd say you
> should make sure you know why and how, otherwise you run the risk of
> the same thing happening again.
i dont know exactly, but it is very possible that it could be an
unfinished server re-config.
>
> Erik Jones
thanks for your help!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Best practices for protect applications agains Sql injection.
In response to pepone.onrez <[EMAIL PROTECTED]>: > Hi all > > I interesting in the protect my applications that use postgresql as is > database backend from Sql Injections attacks, can any recommend me best > pratices or references to protected postgres from this kind of malicious > users. http://www.potentialtech.com/cms/node/49 -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] retry: converting ASCII to UTF-8
I didn't see this come through the first time, so I'm retrying. I apologize if this comes through twice. -- Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 encoding to maintain and support the extended character sets. In my naivety I thought it would be a relatively simple process to convert the db but I've found this to not be the case. I tried doing a dump and restore into a new database with the proper encoding, but pg_restore is getting hung up on one of the tables, our largest by far (~1gb, not huge I know). When I tried pg_restore from a command line (I was using pgAdmin, I know i'm a nub) I received this error. C:\Program Files\PostgreSQL\8.2\bin>pg_restore.exe -i -h 192.168.1.xxx -p 5432 -U foobar -d warehouse_utf8 -a -t "transaction" -v "O:\foo\bar\pg_dump_transaction.backup" pg_restore: connecting to database for restore Password: pg_restore: restoring data for table "transaction" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1706; 0 17861 TABLE DATA transaction foobar pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 I remember reading somewhere recently that I could use iconv to convert the ASCII encoded dump to UTF-8 encoding, but I'm currently on a windows box, and a windows server, so is there an easier way to do this? Also I was thinking perhaps it was possible to do an ETL type setup, where I can SELECT from the ASCII db and INSERT into the UTF-8 db. If you haven't gathered yet, I'm pretty in the dark regarding encoding issues, especially when applied to pg, so any help here would be appreciated. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ascii to utf-8
Tom Hart wrote: Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 ...snip snip pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 Try editing your dump-file and change the line which reads "SET client_encoding = 'SQL_ASCII';" to "SET client_encoding = 'LATIN1';" -- Tommy Gildseth ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ascii to utf-8
Tommy Gildseth wrote: Tom Hart wrote: Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 ...snip snip pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 Try editing your dump-file and change the line which reads "SET client_encoding = 'SQL_ASCII';" to "SET client_encoding = 'LATIN1';" I tried making the changes you specified with notepad, wordpad, gVim, vim and emacs and in each case pgAdmin (and pg_restore) complain about the dump header being corrupted. This has been kind of a pain since the file is ~ 65mb and it's difficult to load something that size into a text editor. I also did a head > file, edited the file, and then did head -n -10 >> file, but once again I had no success. Is there an easy way of doing this, or perhaps a different way of solving the problem? -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best practices for protect applications agains Sql injection.
"pepone.onrez" <[EMAIL PROTECTED]> writes: > Hi all > > I interesting in the protect my applications that use postgresql as is > database backend from Sql Injections attacks, can any recommend me best > pratices or references to protected postgres from this kind of malicious > users. I strongly urge people to adopt a policy of using prepared queries except when absolutely necessary. If all user-provided data is passed to the database as parameters to a prepared query then you should never need to worry about SQL injection. It's possible to always quote your parameters before inserting them into the query but it's much more error-prone. It's also much harder to look at a piece of code and be sure it's correct. If you religiously use prepared queries then any variables interpolated directly into the query stand out like sore thumbs. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_xlog and standby
On Wed, 2008-01-23 at 18:18 -0200, Roberto Scattini wrote: > the standby server had filled his disk with archives > received but not proccesed Sounds like your standby has fallen badly behind. You should always monitor the lag between primary and standby. You will need to take steps to ensure the lag is reduced, or you will continue to have problems with this technique. All asynchronous replication systems have a potential for falling behind the master. Fully synchronous replication techniques don't: they force the master to slow down to a manageable pace. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Best practices for protect applications agains Sql injection.
Thanks all you, i will use prepared queries for all my functions after now. BTW i using Qt-4 postgres drivers from c++ not php. I launch this question because i read that each day more are more applications are compromised with this class of attacks. Thanks again. On Jan 23, 2008 9:45 PM, brian <[EMAIL PROTECTED]> wrote: > pepone.onrez wrote: > > Hi all > > > > I interesting in the protect my applications that use postgresql as is > > database backend from Sql Injections attacks, can any recommend me best > > pratices or references to protected postgres from this kind of malicious > > users. > > > > What are you using on the application side? For instance, with PHP, you > might want to look into the PEAR MDB2 package (specifically, the > prepared statements). > > brian > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings >
Re: [GENERAL] ascii to utf-8
Tommy Gildseth wrote: Tom Hart wrote: Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 ...snip snip pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 Try editing your dump-file and change the line which reads "SET client_encoding = 'SQL_ASCII';" to "SET client_encoding = 'LATIN1';" Ok, so I figured out that head -n -10 and tail -n +10 are not the same thing, and I've got a decent file now. However when I try the restore I get this pg_restore.exe -i -h 192.168.1.xxx -p 5432 -U foobar -d warehouse_utf8 -a -t "transaction" -v "O:\foo\bar\fixed.backup" pg_restore: [archiver] out of memory Process returned exit code 1. I tried upping some of the memory settings in postgresql.conf. The server has ~2gb of RAM unused, and the file is ~65mb. Anybody have any ideas? Also, it's taking around an hour and a half for a message to go from my computer to being posted on the list. Is there a problem with the mailing list software? Thanks again for any assistance you can give me. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ascii to utf-8
Tom Hart <[EMAIL PROTECTED]> writes: > Tommy Gildseth wrote: >> Try editing your dump-file and change the line which reads "SET >> client_encoding = 'SQL_ASCII';" to "SET client_encoding = 'LATIN1';" >> > I tried making the changes you specified with notepad, wordpad, gVim, > vim and emacs and in each case pgAdmin (and pg_restore) complain about > the dump header being corrupted. You can't really manually edit a custom or tar-format archive. What you'll need to do is use pg_restore to emit a plain SQL script from the archive, then edit that, then load it via psql (NOT pg_restore). regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[Fwd: Re: [GENERAL] retry: converting ASCII to UTF-8]
Martin Gainty wrote:
character encoding is implemented at Database level not the table
http://www.postgresql.org/docs/8.2/interactive/sql-createdatabase.html
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] connlimit ] ]
vs Table
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [
.. ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS |
CONSTRAINTS } ] ... }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
HTH
M-
- Original Message -
From: "Tom Hart" <[EMAIL PROTECTED]>
To: "Postgres General List"
Sent: Wednesday, January 23, 2008 3:32 PM
Subject: [GENERAL] retry: converting ASCII to UTF-8
I think you may have misunderstood. I realize that encoding is a
database setting, which is why I originally dumped and recreated the
whole db. I tried a full restore with some success, but it kept hanging
on a specific table, a very large one. I decided to limit the dump and
restore to that table, though the from database is encoded 'SQL-ASCII'
and the to database is 'UTF8'. I must have explained it poorly. My
current problem is waiting for the third message to finally post to this
list so I can get help with my 'out of memory' error.
Thanks for your help anyway :-)
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ascii to utf-8
Tom Hart <[EMAIL PROTECTED]> writes: > Also, it's taking around an hour and a half for a message to go from my > computer to being posted on the list. Is there a problem with the > mailing list software? Yeah, every so often the PG mail servers get kinda clogged up. I pinged Marc about this instance already ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] constraints in table
Hello, How do i find the constraints on a table in SQL? my database is linux based, and I cant seem to find the command. Thanks
Re: [GENERAL] constraints in table
On 24/01/2008 00:09, Dominique Bessette - Halsema wrote: How do i find the constraints on a table in SQL? my database is linux based, and I cant seem to find the command. Thanks In psql, \d will show the constraints. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_xlog and standby
On Wed, 23 Jan 2008, Roberto Scattini wrote: the problem that im having is that i have A LOT of archive files on pg_xlog dir, and thats because the archive_command keeps failing (the standby server had filled his disk with archives received but not proccesed), so now, i dont know how i can remove those files and start again... Under normal operation the checkpoint process will look at the number of already created archive files, keep around up to (2*checkpoint_segments+1) of them for future use, and delete the rest of them. You never delete them yourself, the server will take care of that automatically once it gets to where it makes that decision. If you set checkpoint_segments to some very high number they can end up taking many GB worth of storage, increasing that parameter has at least two costs associated with it (the other being a longer recovery time). Managing old archive logs on the backup server is your problem and related tools like pg_standby help deal with that. Managing them on the primary server is that server's problem and you shouldn't touch them. You can execute a manual CHECKPOINT at the psql prompt if you want to force this reclaimation to happen (there has to have been some activity since the last checkpoint for this to work which doesn't sound like a problem on your server). -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best practices for protect applications agains Sql injection.
On Wed, 23 Jan 2008 21:34:31 + Gregory Stark <[EMAIL PROTECTED]> wrote: > "pepone.onrez" <[EMAIL PROTECTED]> writes: > > > Hi all > > > > I interesting in the protect my applications that use postgresql > > as is database backend from Sql Injections attacks, can any > > recommend me best pratices or references to protected postgres > > from this kind of malicious users. > > I strongly urge people to adopt a policy of using prepared queries > except when absolutely necessary. If all user-provided data is > passed to the database as parameters to a prepared query then you > should never need to worry about SQL injection. > > It's possible to always quote your parameters before inserting them > into the query but it's much more error-prone. It's also much > harder to look at a piece of code and be sure it's correct. If you > religiously use prepared queries then any variables interpolated > directly into the query stand out like sore thumbs. Once you've to build up prepared queries dynamically is there any tool, framework, practice that can help you to stay away from sql injection? I'd say that queries can still be built with prepackaged static parts and that real external input should just come in in forms of parameters... so a DB abstraction layer or an ORM should help too... maybe at the cost of some performance. Otherwise you build up your specialised DB AL that assemble queries from prepackaged static parts. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] constraints in table
On Thursday 24. January 2008, Dominique Bessette - Halsema wrote: >Hello, > >How do i find the constraints on a table in SQL? my database is linux >based, and I cant seem to find the command. Thanks You should really read the psql documentation: http://www.postgresql.org/docs/8.2/static/app-psql.html As for a starter, try to write "\d tablename" from the psql prompt. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] constraints in table
Dominique Bessette - Halsema asked: > > Hello, > > How do i find the constraints on a table in SQL? my database is linux > based, and I cant seem to find the command. Thanks > >From the psql prompt, \d works for me: billing=# \d work.clients Table "work.clients" Column | Type | Modifiers --++--- client_id| character varying(10) | not null client_name | character varying(60) | not null <...> source_id| integer| Indexes: "clients_pkey" PRIMARY KEY, btree (client_id) Check constraints: "clients_client_host_fee_type" CHECK (client_host_fee_type = 'P'::bpchar OR client_host_fee_type = 'M'::bpchar OR client_host_fee_type = ''::bpchar) Foreign-key constraints: "$1" FOREIGN KEY (client_status) REFERENCES client_status(client_status) "$2" FOREIGN KEY (client_brand) REFERENCES brandinginfo(branding_id) Triggers: aud_client AFTER INSERT OR DELETE OR UPDATE ON "work".clients FOR EACH ROW EXECUTE PROCEDURE "work".aud_client() rt_client BEFORE INSERT OR DELETE OR UPDATE ON "work".clients FOR EACH ROW EXECUTE PROCEDURE work_rt.rt_client() If you want to see the SQL that gets these results, invoke psql with -E: bildb-01:~/wf_progs> !! -E psql -d billing -E Welcome to psql 8.1.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit billing=# \d work.clients * QUERY ** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^clients$' AND n.nspname ~ '^work$' ORDER BY 2, 3; ** * QUERY ** SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, relhasoids , reltablespace FROM pg_catalog.pg_class WHERE oid = '21191' ** * QUERY ** SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '21191' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ** * QUERY ** SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = '21191' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname ** * QUERY ** SELECT r.conname, pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE r.conrelid = '21191' AND r.contype = 'c' ORDER BY 1 ** * QUERY ** SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid) FROM pg_catalog.pg_trigger t WHERE t.tgrelid = '21191' AND (not tgisconstraint OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend dJOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f')) ORDER BY 1 ** * QUERY ** SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = '21191' AND r.contype = 'f' ORDER BY 1 ** * QUERY ** SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '21191' ORDER BY inhseqno ASC ** This SQL may differ on different versions; this is from 8.1. HTH, Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Best practices for protect applications agains Sql injection.
On Jan 23, 2008 3:34 PM, Gregory Stark <[EMAIL PROTECTED]> wrote: > "pepone.onrez" <[EMAIL PROTECTED]> writes: > > > Hi all > > > > I interesting in the protect my applications that use postgresql as is > > database backend from Sql Injections attacks, can any recommend me best > > pratices or references to protected postgres from this kind of malicious > > users. > > I strongly urge people to adopt a policy of using prepared queries except when > absolutely necessary. If all user-provided data is passed to the database as > parameters to a prepared query then you should never need to worry about SQL > injection. > > It's possible to always quote your parameters before inserting them into the > query but it's much more error-prone. It's also much harder to look at a piece > of code and be sure it's correct. If you religiously use prepared queries then > any variables interpolated directly into the query stand out like sore thumbs. Two points. 1: Only grant the access needed to the user. i.e. if it's only going to be reading from the, then don't use an account that anything other than select privaleges. 2: I don't find use of pg_escape_string() to be all that error prone. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PGCon vs Postgresql Conference
Is there any real difference between PGCon and Postgresql Conference East/West or is it the same idea in different places? My question is actually which people should go to which conference? Thank you Sim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
