Re: [GENERAL] Hiding data in postgresql
No, I have not considered encrypting or decrypting data. The reason for this is that I am trying to *secure a database* by thinking like a *malicious user / criminal*. I want to hide (for example) fraudulent data on a database where it is not easily seen by others and then build a tool to detect this hidden data. On your questions: *) What data is to remain secret? *) Who is allowed to see the secret data? *) When do they see it? *) What sacrifices are you willing to make to keep the data secret? *) Where are you going to store the key? the answers: - fraudulent data / or data that needs to be hidden. - only the malicious user - and hopefully later a detection mechanism that I aim to build. - I don't really have a preference on when they can see the data, but maybe when you export a dump. - The main purpose of hiding the data is that the normal users of the database will not easily find the hidden data. If this criteria is met, then any other sacrifices can be made. - Still need to figure that one out. Any good brainstorming ideas will help! On Mon, May 24, 2010 at 11:04 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, May 24, 2010 at 3:16 PM, Hector Beyers hqbey...@gmail.com wrote: Hi guys, does ANYONE have any tips on hiding data on a database server? This means that data is stored in places that is not necessarily picked up in the schema of the database. I am doing some research on databases and need some direction. Any help or direction will be highly appreciated. First question: Have you considered 1. encrypting data when you put it in the database and 2. decrypting it when you pull it out? Let me humbly state that the #1 problem that beginners face with security and encryption is focusing too much on the mechanics and not enough on the 'big picture' issues: *) What data is to remain secret? *) Who is allowed to see the secret data? *) When do they see it? *) What sacrifices are you willing to make to keep the data secret? *) Where are you going to store the key? Answers to those questions should get you more helpful answers. Postgres has a lot of features to hide data, some obvious (pgcrypto, grant/revoke) and some not so obvious (revoking permissions from pg_proc). Judging from your question you may be interested in some extra-special techniques...please be more specific! merlin
[GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 At the end of the installation when trying to styart the database server I get the following error meassage: Problem running post-install step. Installation may not complete correctly. Failed to start the database server And when I try to start the server manually I get the following console print out: Start DoCmd(net postgresql-8.4)... System error 1069 has occured. The service did not start due to a logon failure. Failed to start the database server. I've checked the 8.4\Data folder and it is empty. Is ther anyone who has a solution for this? Kind regards, Paul
Re: [GENERAL] Apache authorization using postgres
On 2010-05-24, John Gage jsmg...@numericable.fr wrote: Is this the latest on this subject? Debian has it it a precompiled binary package. Blastwave is/was a solaris thing AFAICT And I guessing you want it for apple. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
On 5/25/10 3:08 PM, Malm Paul wrote: Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 The One-Click installer will not upgrade 8.3.7 to 8.4.4 rather it will create a parallel 8.4.4 installation as both have different major versions. At the end of the installation when trying to styart the database server I get the following error meassage: Problem running post-install step. Installation may not complete correctly. Failed to start the database server And when I try to start the server manually I get the following console print out: Start DoCmd(net postgresql-8.4)... System error 1069 has occured. The service did not start due to a logon failure. Failed to start the database server. Please attach %TEMP%\install-postgresql.log, which will help to analyze things more clearly. I've checked the 8.4\Data folder and it is empty. Is ther anyone who has a solution for this? Kind regards, Paul -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] can't install postgres 8.4 on windows 2003 server
Hi, What happens if you register the service with pg_ctl directly as: Path to PGHOME\bin\pg_ctl.exe register -N ServiceName -U Username -P Password -D Path to DataDirectory -w On 5/24/10 11:58 PM, ritas wrote: Thanks Sachin for your response. I have attached the install-postgresql.log. Also, if it helps, I have the following error in my event viewer: pg_ctl: could not register service postgresql-8.4: error code 0 The postgres service is not listed under services. Thanks. http://old.nabble.com/file/p28660078/install-postgresql.log install-postgresql.log Sachin Srivastava-2 wrote: On 5/24/10 7:52 PM, ritas wrote: Hi, When I try to install postgres 8.4 on windows server 2003, I get the following error: A non-fatal error occurred whilst loading database modules. Please check the installation logs in C:/docssetttings/user/local settings/temp for details. The log file doesn't have any error, but it looks like it stopped in the middle. Also, it doesn't install the postgres service. I have attached the log file. Any help would be greatly appreciated. Thanks. http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log bitrock_installer_1004serverlog.log There must be a log file with name install-postgresql.log in the %TEMP% directory. -- Regards, Sachin Srivastava EnterpriseDBhttp://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
On 25 May 2010, at 11:38, Malm Paul wrote: Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully). This is not quite the first message formatted like this, I merely thought I should finally just ask. I did notice that most of the people sending messages formatted like this one don't appear to be native speakers (of English), does that have anything to do with it? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4bfba33510414354318240! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
On Tue, May 25, 2010 at 11:15 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 25 May 2010, at 11:38, Malm Paul wrote: Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully). ask microsoft folks working on outlook. Somehow they thought it is a good idea. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
On Tuesday 25. May 2010 12.15.14 Alban Hertroys wrote: I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully). At least there's a plain text fallback. Messages in HTML only are totally unreadable in a plaintext MUA, and should IMO be bounced from the list. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On Mon, May 24, 2010 at 05:04:10PM -0400, Merlin Moncure wrote: Let me humbly state that the #1 problem that beginners face with security and encryption is focusing too much on the mechanics and not enough on the 'big picture' issues: One more that OP seems to be avoiding is why would anybody want to do this anyway? There are plenty of places that will happily host data for you--most email sites give you many gigabytes of storage these days. Seems to be a solution in search of a problem to me. I think what the OP may be asking is about the presence of covert channels. There are plenty of these in PG, an attacker can do the obvious things like disguising data inside other data (steganography) or more subtle things like tuple order on disk, transaction orderings, or interactions between running queries (i.e. causing one to pause for a few milliseconds by reading/locking a table). Covert channels seem to be a fundamental fact of nature. As far as I know, though I'm not aware of any papers directly on the subject, it's *always* possible to design a new attack by exploiting the physical implementation of something. Hence any specific tool you design to look for any specific attack can always be avoided in an infinite number of ways, generally negating its purpose. You have to be much more specific in your requirements before useful analysis can be done. What can be done is to reduce the bandwidth of a specific covert channel, and beyond some threshold it *may* be possible to say that no useful data can be transmitted, but that's about it. If somebody just wants to leak a password/private key a surprisingly few number of bits will go a long way. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump cannot connect when executing by a script
Hi all, I've found in the net a lot of problems similar to mine, but not the solution for my case: when I executed pg_dump against a database from a script (that will be executed by cron) I got the following error: pg_dump: [archiver (db)] connection to database webcalendardb failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? and the line the script is executing is the following: /usr/local/bin/pg_dump -f /backup/sedeldap/postgresql//webcalendardb2010_05_25.sql -h sedeldapa -U postgres webcalendardb and of course, if executed interactively, the above line works. The pg_dump is for 8.4.0 (installed from freebsd ports). Moreover, if in the script I add the option -F t than the script complains that: pg_dump: too many command-line arguments (first is webcalendardb) and of course the following: /usr/local/bin/pg_dump -F t-f /backup/sedeldap/postgresql//webcalendardb2010_05_25.sql -h sedeldap -U postgres webcalendardb works. Any idea? The only thing I suspect is that I change the IFS in the shell script, but I also restore it back before trying to pg_dump. Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump cannot connect when executing by a script
In response to Luca Ferrari : Hi all, I've found in the net a lot of problems similar to mine, but not the solution for my case: when I executed pg_dump against a database from a script (that will be executed by cron) I got the following error: pg_dump: [archiver (db)] connection to database webcalendardb failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Your unix-scket isn't in /tmp. Start psql -h localhost and type: show unix_socket_directory; This will show you the corrent path to the unix-socket. You can use that for pg_dump with option -h /path/to/the/socket-dir Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Apache authorization using postgres
I really like the idea of using postgres, as opposed to the file system, to store user names and passwords for http authorization on apache. It is a very good fit and facilitates data collection, etc. On the other hand, there is no dichotomy, because what is stored in a file can also be stored in postgres. Ultimately, I increasingly like to push everything I can back into SQL on postgres. Make the perl cgi routines as simple as possible and have the intelligence in postgres. Yes, MacOSX 10.5. Thanks, John On May 25, 2010, at 11:41 AM, Jasen Betts wrote: On 2010-05-24, John Gage jsmg...@numericable.fr wrote: Is this the latest on this subject? Debian has it it a precompiled binary package. Blastwave is/was a solaris thing AFAICT And I guessing you want it for apple. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't install postgres 8.4 on windows 2003 server
Ritas, modules. Please check the installation logs in C:/docssetttings/user/local settings/temp for details. The log file doesn't have any error, but it that directory name for docs and settings looks strange to me. (disclaimer: using German language versions of Windows); I am used to documents and settings and no in directory names. so let me throw in a very, very wild guess: is your Programs and Files directory possibly ProgsFiles or similiar? My suggestion is to try to install PostgreSQL in a place without an in the directory name ... (just a feeling deep from my belly) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night.
Re: [GENERAL] pg_dump cannot connect when executing by a script
On Tuesday 25 May 2010 01:13:40 pm A. Kretschmer's cat walking on the keyboard wrote: Your unix-scket isn't in /tmp. Start psql -h localhost and type: show unix_socket_directory; This will show you the corrent path to the unix-socket. You can use that for pg_dump with option -h /path/to/the/socket-dir Not sure if I get it right: on the machine executing the script postgresql is not installed, only the client is. However, it seems to work specifying in the pg_dump the port to which the script must connect: -p 5432. It is interesting to know why the psql command is working fine even without such parameter and pg_dump is not. Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't install postgres 8.4 on windows 2003 server
I tried that and I get the same error: pg_ctl: could not register service postgresql-8.4: error code 0 Sachin Srivastava-2 wrote: Hi, What happens if you register the service with pg_ctl directly as: Path to PGHOME\bin\pg_ctl.exe register -N ServiceName -U Username -P Password -D Path to DataDirectory -w On 5/24/10 11:58 PM, ritas wrote: Thanks Sachin for your response. I have attached the install-postgresql.log. Also, if it helps, I have the following error in my event viewer: pg_ctl: could not register service postgresql-8.4: error code 0 The postgres service is not listed under services. Thanks. http://old.nabble.com/file/p28660078/install-postgresql.log install-postgresql.log Sachin Srivastava-2 wrote: On 5/24/10 7:52 PM, ritas wrote: Hi, When I try to install postgres 8.4 on windows server 2003, I get the following error: A non-fatal error occurred whilst loading database modules. Please check the installation logs in C:/docssetttings/user/local settings/temp for details. The log file doesn't have any error, but it looks like it stopped in the middle. Also, it doesn't install the postgres service. I have attached the log file. Any help would be greatly appreciated. Thanks. http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log bitrock_installer_1004serverlog.log There must be a log file with name install-postgresql.log in the %TEMP% directory. -- Regards, Sachin Srivastava EnterpriseDBhttp://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. -- View this message in context: http://old.nabble.com/can%27t-install-postgres-8.4-on-windows-2003-server-tp28657511p28668276.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't install postgres 8.4 on windows 2003 server
Herald, I was lazy to type the whole documents and settings folder. I thought it was obvious:) Massa, Harald Armin wrote: Ritas, modules. Please check the installation logs in C:/docssetttings/user/local settings/temp for details. The log file doesn't have any error, but it that directory name for docs and settings looks strange to me. (disclaimer: using German language versions of Windows); I am used to documents and settings and no in directory names. so let me throw in a very, very wild guess: is your Programs and Files directory possibly ProgsFiles or similiar? My suggestion is to try to install PostgreSQL in a place without an in the directory name ... (just a feeling deep from my belly) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night. -- View this message in context: http://old.nabble.com/can%27t-install-postgres-8.4-on-windows-2003-server-tp28657511p28668288.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump cannot connect when executing by a script
Luca Ferrari fluca1...@infinito.it writes: for my case: when I executed pg_dump against a database from a script (that will be executed by cron) I got the following error: pg_dump: [archiver (db)] connection to database webcalendardb failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? and the line the script is executing is the following: /usr/local/bin/pg_dump -f /backup/sedeldap/postgresql//webcalendardb2010_05_25.sql -h sedeldapa -U postgres webcalendardb Well, um, that command line can't possibly give rise to that error message. pg_dump is evidently trying to connect via Unix socket, which it absolutely won't do if given a -h host argument. Any idea? The only thing I suspect is that I change the IFS in the shell script, but I also restore it back before trying to pg_dump. I suspect that the command is being misparsed, perhaps because your fooling with IFS is having more effect than you realize. You might want to try inserting echo, or some other way of debugging exactly how the command arguments are getting divided up. Another theory: the script you're executing is not the one you think it is. We've seen more than one person waste a lot of time that way :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[OT] Re: [GENERAL] update from postgresql 8.3.7 to 8.4.4
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 At least there's a plain text fallback. Messages in HTML only are totally unreadable in a plaintext MUA, and should IMO be bounced from the list. Not totally unreadable. Good MUAs find ways to handle it. For example, my mutt[1] is more than happy to automatically pipe things through lynx -dump which works pretty well. That said, +1 to bouncing email with no text at all. [1] Technically, mutt and mailcap - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005251117 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkv76icACgkQvJuQZxSWSsjtOACgxd0BRnE73BZJ1w1zfpKZ946s x34Ani5IKIpCMzU/+Xh3nB1+U47Q1tLX =gc5X -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ALTER Bigserial error
Scott,
Sorry about the late response.
Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?
From
http://savage.net.au/SQL/sql-2003-2.bnf.html#identity%20column%20specification
...
Define a column of a base table.
column definition::=
column name [ data type | domain name ] [ reference scope
check ]
[ default clause | identity column specification | generation
clause ]
[ column constraint definition ... ] [ collate clause ]
column constraint definition::= [ constraint name definition ]
column constraint [ constraint characteristics ]
column constraint::=
NOT NULL
| unique specification
| references specification
| check constraint definition
reference scope check::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE
reference scope check action ]
reference scope check action::= referential action
identity column specification::=
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ left paren common sequence generator options right paren ]
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Monday, April 26, 2010 3:49 PM
To: Little, Douglas
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] ALTER Bigserial error
On Mon, Apr 26, 2010 at 12:59 PM, Little, Douglas
douglas.lit...@orbitz.com wrote:
Thanks for the response tom,
I agree it's more of an missing feature.
Regarding the concensus for direction. I'd like to see the product move in
the direction of the sql standard.
Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?
Re: [GENERAL] Hiding data in postgresql
Hi guys, thank you for your replies yesterday on this topic. I have one more question though: Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do some security through obscurity. It is for research purposes. Maybe to save populate a table with 1000 rows, but the meta-data only knows of about 500 of them? Only on an export of a dump can you find the data again. Kind regards Hector On Mon, May 24, 2010 at 9:16 PM, Hector Beyers hqbey...@gmail.com wrote: Hi guys, does ANYONE have any tips on hiding data on a database server? This means that data is stored in places that is not necessarily picked up in the schema of the database. I am doing some research on databases and need some direction. Any help or direction will be highly appreciated. Kind regards Hector
Re: [GENERAL] Hiding data in postgresql
Hector Beyers hqbey...@gmail.com wrote: thank you for your replies yesterday on this topic. I have one more question though: Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do some security through obscurity. It is for research purposes. Maybe to save populate a table with 1000 rows, but the meta-data only knows of about 500 of them? Only on an export of a dump can you find the data again. [...] Before delving deeper into this, you should get your termi- nology straight: What do you mean by meta-data? What do you mean by export of a dump? What do you mean by without the meta data noticing? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Statement Pooling
Hi, we are running java6/hibernate/c3p0/postgresql stack. Our JDBC Driver is 8.4-701.jdbc3 I have a few questions about Prepared Statements. I have read http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are- important-and-how-to-use-them-properly At the moment we have c3p0.maxStatements = 0 c3p0.maxStatementsPerConnection = 0 I don't know if I do understand statement pooling properly, so please help me. In my understanding it is like this: Our hibernate stack uses prepared statements. Postgresql is caching the execution plan. Next time the same statement is used, postgresql reuses the execution plan. This saves time planning statements inside DB. Additionally c3p0 can cache java instances of java.sql.PreparedStatement which means it is caching the java object. So when using c3p0.maxStatementsPerConnection = 100 it caches at most 100 different objects. It saves time on creating objects, but this has nothing to do with the postgresql database and its prepared statements. Right? As we use about 100 different statements I would set c3p0.maxStatementsPerConnection = 100 Is this reasonable? Is there a real benefit activating it? I remember postgresql 8.4 is replanning prepared statements when statistics change occur, but I didn't find it in the release notes. It is just saying Invalidate cached plans when referenced schemas, functions, operators, or operator classes are modified. Does PG replans prepared statements from time to time if underlying data statistics change? I am glad if you help me and give me some insights to managing my connection pool the right way. kind regards Janning PS: I think its ok to post this on general and not on pgsql-jdbc as I am not subcribed to pgsql-jdbc. If not, please let me know and I will repost there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't install postgres 8.4 on windows 2003 server
Sachin, I was using the windows user the postgres installer created to register the service. That failed. I thought I will try a admin user and I was able to register the service. Then I ran all the failed vb scripts in the installer which was listed in the install-postgresql.log file: startupcfg.vbs, createshortcuts.vbs, startserver.vbs, loadmodules.vbs. But I changed the user for the startupcfg.vbs to one of the admin users. After doing all these hacks it looks like postgres is installed fine. I was wondering why the user created by postgres was not able to register the service? Anybody else installing postgres 8.4 on windows 2003 server have this issue? I really appreciate any help. Thanks. ritas wrote: I tried that and I get the same error: pg_ctl: could not register service postgresql-8.4: error code 0 Sachin Srivastava-2 wrote: Hi, What happens if you register the service with pg_ctl directly as: Path to PGHOME\bin\pg_ctl.exe register -N ServiceName -U Username -P Password -D Path to DataDirectory -w On 5/24/10 11:58 PM, ritas wrote: Thanks Sachin for your response. I have attached the install-postgresql.log. Also, if it helps, I have the following error in my event viewer: pg_ctl: could not register service postgresql-8.4: error code 0 The postgres service is not listed under services. Thanks. http://old.nabble.com/file/p28660078/install-postgresql.log install-postgresql.log Sachin Srivastava-2 wrote: On 5/24/10 7:52 PM, ritas wrote: Hi, When I try to install postgres 8.4 on windows server 2003, I get the following error: A non-fatal error occurred whilst loading database modules. Please check the installation logs in C:/docssetttings/user/local settings/temp for details. The log file doesn't have any error, but it looks like it stopped in the middle. Also, it doesn't install the postgres service. I have attached the log file. Any help would be greatly appreciated. Thanks. http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log bitrock_installer_1004serverlog.log There must be a log file with name install-postgresql.log in the %TEMP% directory. -- Regards, Sachin Srivastava EnterpriseDBhttp://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. -- View this message in context: http://old.nabble.com/can%27t-install-postgres-8.4-on-windows-2003-server-tp28657511p28670341.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cursor_to_xml iteration of a table
Hi all - I'm currently wrestling with generating XML output from a table that has grown to a size where simply using table_to_xml or query_to_xml is no longer feasible due to the whole result set getting loaded into memory. I've been getting familiar with cursors and the cursor_to_xml command and have two issues that I can't seem to figure out: 1) When using cursor_to_xml in a plpgsql function, the FOUND variable does not seem to get set, so there is no way to exit a loop that is iterating over the cursor. Below is the function code; it loops indefinitely when it is run. create or replace function getxml() returns setof xml as $$ declare resultxml xml; curs refcursor; begin open curs for select * from groups; loop select cursor_to_xml(curs,1000, false, false, '') into resultxml; return next resultxml; exit when not found; end loop; end; $$ language plpgsql; 2) Assuming the above issue is fixed, how can I go about ensuring that the result set from the function isn't stored in memory until the function completes? Ideally, I'd like to write the output to a file with each iteration of the cursor, but I know file IO is a big no-no with plpgsql since transactions can't manage the state of files being written. Any insight is most appreciated. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On 5/24/2010 3:18 PM, Hector Beyers wrote: Yes, I mean hide. I am approaching the problem out of the perspective of a malicious user / hacker. **snip*** First hiding data is not a solution to secure or block access to information. This only slows people down it does not stop them, never underestimate users with access to the data It would be helpful to explain the type of data that needs to be hidden/secured Example of failed attempts to hide data is to look at the numerous mistakes in securing credit card data at many Companies. In almost every case that i have read the programmers just tried to hide the data or limit access instead of doing Public Key Private Key encryption methodology .I know of several big name apps that still store credit card data where the end users can reverse the encryption meaning if the key becomes unsecured any the data is visible that is encrypted. I have seen where the data is only encrypted inside the database so the information is transmitted in the clear to the client as the database decrypted the data on the fly . What is the point?? Trying to hide information is waste of time and energy look into encryption. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] export data to excel
Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help Regards
Re: [GENERAL] export data to excel
akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help configure excel to use the postgres database as an ODBC 'data source' and query it directly from your spreadsheet. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] export data to excel
Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help Regards Why not just save to the CSV file to XLS from within Excel? XLS, and to a lesser extent XLSX are baroque proprietary formats which aren't particularly well supported by most opensource tools. Openoffice.org does a fair job at it, but that's about it. Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] export data to excel
csv can easily be swallowed by Excel as a data source. I like the ODBC based query suggestion better. I've configured Excel to accept user inputs via text input widgets and pass them onto the query as params in the query predicate. I found the QueryEditor.xla add-in very valuable in this regard as it allows you to edit the sql query as text instead of their goofy and limited graphical editor which is often not up to the task. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Tuesday, May 25, 2010 2:33 PM To: akp geek Cc: pgsql-general Subject: Re: [GENERAL] export data to excel akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help configure excel to use the postgres database as an ODBC 'data source' and query it directly from your spreadsheet. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On 5/25/2010 2:58 AM, Hector Beyers wrote: No, I have not considered encrypting or decrypting data. The reason for this is that I am trying to /secure a database/ by thinking like a /malicious user / criminal/. I want to hide (for example) fraudulent data on a database where it is not easily seen by others and then build a tool to detect this hidden data. On your questions: *) What data is to remain secret? *) Who is allowed to see the secret data? *) When do they see it? *) What sacrifices are you willing to make to keep the data secret? *) Where are you going to store the key? the answers: * fraudulent data / or data that needs to be hidden. * only the malicious user - and hopefully later a detection mechanism that I aim to build. * I don't really have a preference on when they can see the data, but maybe when you export a dump. * The main purpose of hiding the data is that the normal users of the database will not easily find the hidden data. If this criteria is met, then any other sacrifices can be made. * Still need to figure that one out. Any good brainstorming ideas will help! Missed this bit prior to first responds. I think some of the assumptions here are flawed. If hacker actually got into a database why would they do this??? what is being accomplished??? why would anyone want to do this??? Again it would make allot more sense if a hacker stored data in plain site. Create tables that look like real tables following the same naming schema or use already existing tables like logs, Modify the tables adding columns to store data. Then create/update records encrypting the contents, this would protect the contents from ever being read by anyone except by the creator. Think this line through how long would a Hacker go unnoticed if they used the already existing tables adding in columns or take over stale records like old customers that are no longer active. Then use the text fields to store data. The hacker could create normal user account to access those records throwing up no red flags. How many people review table structures or update to already existing records. The current crop of hackers are not hexeditor high-school wannabe's. Hackers want to go unnoticed for as long as they can so that means doing nothing out of ordinary that throws up red flags. Just read up on the investigations on stolen credit cards. Or fake ATMS that's been installed at malls. The hackers/thieves figured out how to go unnoticed for long periods of time by appearing normal. Second assumption is the hacker actual got a admin/root level access to be able to do these kind of things. This means security upfront was lacks which point there are far bigger problems than hidden data. Far better way to secure is not trying think what they can do once they get access, but stop them getting in to start with.If anyone gets this high level of access protecting from or figuring out if they have hidden data is immaterial to the problems someone has. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On Tue, May 25, 2010 at 11:30 AM, Hector Beyers hqbey...@gmail.com wrote: Maybe to save populate a table with 1000 rows, but the meta-data only knows of about 500 of them? Only on an export of a dump can you find the data again. Well, here's your flaw: If you expect the data dumper to be able to export the rows, then the system must know that the rows/tables are there, and thus cannot be hidden for any definition of hidden. To find them, you just do the work that the dumper would do. Ie, you seem to want to be able to write files that postgres doesn't know are there, and that I don't believe you can do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] export data to excel
There is also the Java POI classes (Poor Obfuscation Implementation) for importing and exporting doc and docx, HSSF (Horrible SpreadSheet Format) for exporting and importing xls and xlsx, http://poi.apache.org/ There are also implementations for Outlook, Powerpoint and Visio. Java can be integrated with PostgreSQL using the JDBC drivers: http://jdbc.postgresql.org/ Hope this helps. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to fetch values at regular hours?
Hello all! I am logging my electricity meeter every five minute! But the problem is that I would like to get values *just before* every hour - like select * from counter_log_view where name='CNT-3' and timestamp '2010-05-23 22:00:00' order by timestamp desc limit 1; name | timestamp | count ---++--- CNT-3 | 2010-05-23 21:53:20.844422 |69 (1 rad) But I don't want to do a lot of selections and gather all the data in a file. Is there any better way to create the query so that I get the values just before every hour? I would like to do; freescada= select * from counter_log_view where name='CNT-3' and timestamp '2010-05-23 18:00:00' order by timestamp desc limit 1; name | timestamp | count ---+---+--- CNT-3 | 2010-05-23 17:53:18.58674 |43 (1 rad) freescada= select * from counter_log_view where name='CNT-3' and timestamp '2010-05-23 19:00:00' order by timestamp desc limit 1; name | timestamp | count ---++--- CNT-3 | 2010-05-23 18:53:19.151988 |50 (1 rad) freescada= select * from counter_log_view where name='CNT-3' and timestamp '2010-05-23 20:00:00' order by timestamp desc limit 1; name | timestamp | count ---++--- CNT-3 | 2010-05-23 19:53:19.683514 |51 (1 rad) In one query. Is this possible in *any* way? -- gor...@gmail.com Mob: 070-5530148
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse wrote: Hello all! I am logging my electricity meeter every five minute! But the problem is that I would like to get values *just before* every hour - like select * from counter_log_view where name='CNT-3' and timestamp '2010-05-23 22:00:00' order by timestamp desc limit 1; name | timestamp | count ---++--- CNT-3 | 2010-05-23 21:53:20.844422 |69 (1 rad) But I don't want to do a lot of selections and gather all the data in a file. Is there any better way to create the query so that I get the values just before every hour? wild guess, untested.. something like... select * from counter_log_view where name = 'CNT-3' and extract(minute from timestamp) = 55 order by timestamp; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] export data to excel
I tried this option, the error that I was getting can not use crypt. The I have used the open office as Peter mentioned and that worked for me. I will try the other options mentioned also thanks for the support Regards On Tue, May 25, 2010 at 2:33 PM, John R Pierce pie...@hogranch.com wrote: akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help configure excel to use the postgres database as an ODBC 'data source' and query it directly from your spreadsheet.
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse gor...@gmail.com wrote:
[...]
I would like to do;
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 18:00:00' order by timestamp desc limit 1;
name | timestamp | count
---+---+---
CNT-3 | 2010-05-23 17:53:18.58674 |43
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 19:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 18:53:19.151988 |50
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 20:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 19:53:19.683514 |51
(1 rad)
In one query. Is this possible in *any* way?
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
Tim
P. S.: Naming columns timestamp and count will lead to
trouble :-).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] export data to excel
akp geek wrote: On Tue, May 25, 2010 at 2:33 PM, John R Pierce pie...@hogranch.com mailto:pie...@hogranch.com wrote: akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help configure excel to use the postgres database as an ODBC 'data source' and query it directly from your spreadsheet. I tried this option, the error that I was getting can not use crypt. The I have used the open office as Peter mentioned and that worked for me. I will try the other options mentioned also not sure what crypt has to do with anything here. you would need a postgres odbc or adodb or whatever driver that windows and excel can use to access postgres directly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Yes timestamp and count - is not good names for columns...
I tried something like;
select name,date_trunc('hour',timestamp),timestamp,count from
counter_log_view where name='CNT-3' and timestamp '2010-05-23 20:00:00'
order by timestamp limit 10;
name | date_trunc | timestamp | count
---+-++---
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:43:17.411386 |23
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:53:17.45934 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:03:17.489321 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:13:17.586089 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:23:17.69116 |25
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:33:17.795955 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:43:17.89265 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:53:17.989268 |30
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:03:18.1447 |33
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:13:18.199568 |35
(10 rader)
Seems promising... But then I would like to select only the last from
date_trunc...
Hm...
GH
2010/5/25 Tim Landscheidt t...@tim-landscheidt.de
Goran Hasse gor...@gmail.com wrote:
[...]
I would like to do;
freescada= select * from counter_log_view where name='CNT-3' and
timestamp
'2010-05-23 18:00:00' order by timestamp desc limit 1;
name | timestamp | count
---+---+---
CNT-3 | 2010-05-23 17:53:18.58674 |43
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and
timestamp
'2010-05-23 19:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 18:53:19.151988 |50
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and
timestamp
'2010-05-23 20:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 19:53:19.683514 |51
(1 rad)
In one query. Is this possible in *any* way?
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
Tim
P. S.: Naming columns timestamp and count will lead to
trouble :-).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
gor...@gmail.com
Mob: 070-5530148
[GENERAL] Mysterious empty database name?
In our database list, there is one 'mysterious' database with a blank name.
We don't know how it got there.
Below is the output of pg_database. Does anyone have any idea why this is
happening? Any way to remove the 'mysterious' database safely.
datname | datdba | encoding | datistemplate |
datallowconn | datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid |
dattablespace | datconfig | datacl
---++--+---+--+--+---+--+--+---+---+
postgres | 10 |6 | f | t
| -1 | 10792 | 38260524 | 3259485997 |
1663 | |
jboss-ktj-2007-09-02 | 10 |6 | f | t
| -1 | 10792 | 38260579 | 3259486052 |
1663 | |
jboss-ktj | 10 |6 | f | t
| -1 | 10792 | 38261114 | 3259486587 |
1663 | |
jboss-ktj-test-2010-03-28 | 10 |6 | f | t
| -1 | 10792 | 38261842 | 3259487315 |
1663 | |
jboss-warestore | 10 |6 | f | t
| -1 | 10792 | 38262572 | 3259488045 |
1663 | |
template1 | 10 |6 | t | t
| -1 | 10792 | 38262629 | 3259488102 |
1663 | | {postgres=CT/postgres}
template0 | 10 |6 | t | f
| -1 | 10792 | 499 | 499 |
1663 | | {postgres=CT/postgres}
jboss-ktj-test| 10 |6 | f | t
| -1 | 10792 | 38262684 | 3259488157 |
1663 | |
| 10 |6 | f | t|
-1 | 10792 | 499 | 499 | 1663 |
| --- 'Mysterious' database
Re: [GENERAL] Mysterious empty database name?
Azlin Rahim azlin.ra...@gmail.com writes: In our database list, there is one 'mysterious' database with a blank name. We don't know how it got there. Your mail client has done you no favors as far as preserving the formatting of the SELECT output, but it looks to me like the name of the weird database is probably not blank but rather contains some control characters (perhaps a carriage return?). Depending on how old your psql is, that could result in wacky formatting, which is what it looks like you've got here. Another theory is that it's an encoding problem: non-ASCII database names are troublesome if you don't use the same encoding in each database. I'd suggest trying the SELECT under some other output format, perhaps \pset format unaligned, to see if it gets any more readable. Depending on what the name really is, you might be able to type it as a double-quoted identifier, in which case ALTER DATABASE RENAME would work to fix it. If all else fails, you could try getting the OID of the database and then UPDATE pg_database SET datname = 'something_sane' WHERE oid = nnn; as superuser should fix it. (If it's pre-8.1 PG, you might need another ALTER DATABASE RENAME to be sure subsidiary files are updated.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mysterious empty database name?
Excerpts from Azlin Rahim's message of mar may 25 18:23:13 -0400 2010: In our database list, there is one 'mysterious' database with a blank name. We don't know how it got there. Maybe somebody SQL-injected you and created it. Below is the output of pg_database. Does anyone have any idea why this is happening? Any way to remove the 'mysterious' database safely. I'd investigate a bit more what's inside, before deleting it. Maybe the name has only whitespace or control chars. You can use double quotes to refer to such names. For example, this is a name a space and a backspace: =# create database ^?; CREATE DATABASE You can see the ASCII chars that make up the name with something like this: =# select datname, ascii(substring(datname, generate_series(1, length(datname)), 1)) from pg_database; \x7F |32 \x7F | 127 Now if somebody messed with an Unicode control char, I don't know what to tell you. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse gor...@gmail.com wrote:
Yes timestamp and count - is not good names for columns...
I tried something like;
select name,date_trunc('hour',timestamp),timestamp,count from
counter_log_view where name='CNT-3' and timestamp '2010-05-23 20:00:00'
order by timestamp limit 10;
name | date_trunc | timestamp | count
---+-++---
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:43:17.411386 |23
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:53:17.45934 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:03:17.489321 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:13:17.586089 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:23:17.69116 |25
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:33:17.795955 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:43:17.89265 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:53:17.989268 |30
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:03:18.1447 |33
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:13:18.199568 |35
(10 rader)
Seems promising... But then I would like to select only the last from
date_trunc...
Hm...
Why did you not use the query I posted:
[...]
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
[...]
Is copy paste too much effort?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mysterious empty database name?
I tried to do another SELECT on pg_database with the OID and here's
what I get. Seems like there is no OID for the weird database. I'm
stumped.
Btw, our Postgresql version is 8.1.11.
# select oid,* from pg_database;
oid | datname | datdba | encoding |
datistemplate | datallowconn | datconnlimit | datlastsysoid |
datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
+---++--+---+--+--+---+--+--+---+---+
10793 | postgres | 10 |6 | f
| t| -1 | 10792 | 38260524 |
3259485997 | 1663 | |
138208 | jboss-ktj-2007-09-02 | 10 |6 | f
| t| -1 | 10792 | 38260579 |
3259486052 | 1663 | |
134606 | jboss-ktj | 10 |6 | f
| t| -1 | 10792 | 38261114 |
3259486587 | 1663 | |
208645 | jboss-ktj-test-2010-03-28 | 10 |6 | f
| t| -1 | 10792 | 38261842 |
3259487315 | 1663 | |
185623 | jboss-warestore | 10 |6 | f
| t| -1 | 10792 | 38262572 |
3259488045 | 1663 | |
1 | template1 | 10 |6 | t
| t| -1 | 10792 | 38262629 |
3259488102 | 1663 | | {postgres=CT/postgres}
10792 | template0 | 10 |6 | t
| f| -1 | 10792 | 499 |
499 | 1663 | | {postgres=CT/postgres}
245497 | jboss-ktj-test| 10 |6 | f
| t| -1 | 10792 | 38262684 |
3259488157 | 1663 | |
| 10 |6 | f | t|
-1 | 10792 | 499 | 499 | 1663 |
|
(9 rows)
On Wed, May 26, 2010 at 6:40 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Azlin Rahim azlin.ra...@gmail.com writes:
In our database list, there is one 'mysterious' database with a blank name.
We don't know how it got there.
Your mail client has done you no favors as far as preserving the
formatting of the SELECT output, but it looks to me like the name of the
weird database is probably not blank but rather contains some control
characters (perhaps a carriage return?). Depending on how old your psql
is, that could result in wacky formatting, which is what it looks like
you've got here. Another theory is that it's an encoding problem:
non-ASCII database names are troublesome if you don't use the same
encoding in each database.
I'd suggest trying the SELECT under some other output format, perhaps
\pset format unaligned, to see if it gets any more readable.
Depending on what the name really is, you might be able to type it as a
double-quoted identifier, in which case ALTER DATABASE RENAME would
work to fix it. If all else fails, you could try getting the OID
of the database and then
UPDATE pg_database SET datname = 'something_sane' WHERE oid = nnn;
as superuser should fix it. (If it's pre-8.1 PG, you might need another
ALTER DATABASE RENAME to be sure subsidiary files are updated.)
regards, tom lane
--
Azlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On Mon, May 24, 2010 at 2:16 PM, Hector Beyers hqbey...@gmail.com wrote: Hi guys, does ANYONE have any tips on hiding data on a database server? This means that data is stored in places that is not necessarily picked up in the schema of the database. I am doing some research on databases and need some direction. Any help or direction will be highly appreciated. Like everyone else who has responded I am unsure exactly what you mean but it might be that you want to implement something like a virtual private database. The basic idea is that every user connects to the same database but gets to see different subsets of data depending on what rights they have. You implement this using views. No-one gets access to the underlying tables, instead having access to a secured view. The secured view on table x looks like this: create view x as select * from real.x where i_can_see(x.key); The function i_can_see() determines whether you can see a particular row. Naturally access function, i_can_see(), needs to know who a particular user is and what rights they have. This involves some careful session management, particularly in today's web-centric applications. If you are interested in this technique, then my project, veil: http://veil.projects.postgresql.org/ , provides tools for building virtual private databases in Postgres. Be warned though, this is a difficult thing to do, may have unacceptable overhead, and may still leave channels open for data compromise. __ Marc signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Mysterious empty database name?
Azlin Rahim azlin.ra...@gmail.com writes: I tried to do another SELECT on pg_database with the OID and here's what I get. Seems like there is no OID for the weird database. I'm stumped. You didn't absorb the point about funny formatting. This makes it look even more probable that the database name contains a carriage return. See Alvaro's reply for one way to look closer. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mysterious empty database name?
Thanks Tom, Alvaro. It was indeed caused by the carriage return. I've renamed the weird db and the problem is resolved. On Wed, May 26, 2010 at 7:14 AM, Tom Lane t...@sss.pgh.pa.us wrote: Azlin Rahim azlin.ra...@gmail.com writes: I tried to do another SELECT on pg_database with the OID and here's what I get. Seems like there is no OID for the weird database. I'm stumped. You didn't absorb the point about funny formatting. This makes it look even more probable that the database name contains a carriage return. See Alvaro's reply for one way to look closer. regards, tom lane -- Azlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: PDXPUG Day at OSCON 2010
It was recommended to me to forward this to -general. Regards, Mark -- Forwarded message -- From: Mark Wong mark...@gmail.com Date: Tue, May 18, 2010 at 6:57 AM Subject: PDXPUG Day at OSCON 2010 To: pgsql-annou...@postgresql.org Thanks to the generosity of O'Reilly, we will be having a full day of free PostgreSQL sessions on Sunday, July 18 at the Oregon Convention Center. Location details and schedule information can be found on the wiki at: http://wiki.postgresql.org/wiki/PDXPUGDay2010 We will ask for a $30 donation towards PostgreSQL at the conference, but no one will be turned away. Sign up here: https://spreadsheets.google.com/viewform?hl=enformkey=dDVBRnJGWVlZRkdycFdXbXVuYTNiU2c6MQ Please submit your talk proposal here: http://spreadsheets.google.com/viewform?hl=enformkey=dHBFMGFIWmxJUzhRM3R6dXVlWWxYQ1E6MQ. Proposals will be decided upon in June 7th and updated on the wiki. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction with in function
I looking for solution for commit, rollback with in function. Below is Example function CREATE OR REPLACE FUNCTION test() RETURNS void AS $BODY$ BEGIN --- setof statements Commit; -- set of statement commit; -- if things goes wrong rollback RETURN; END; $BODY$ LANGUAGE 'plpgsql'; Thanks in advance . Regards Ravi Katkar
Re: [GENERAL] Transaction with in function
In response to Ravi Katkar : I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
