Re: [GENERAL] SSL certificates issue

[Asia]

Thank you for your reply. I agree that this configuration could be better and 
this is why I sent my post.

There is still one concern remaining. As I said I have working configuration 
with libpq and jdbc. For jdbc I created keystore, that is properly used with 
connection ssl=on parameter and clientcert=1 in og_hba.conf, everything works 
fine.

The issue is why for libpq it is enough to have only one lowest level root 
certificate matched besides the fact that certificate postgresql.crt that is 
presented to server contains actually 3 certs (2 from Intermediate authority 
with lowest level root). 
For JDBC it was not enough, I had to put whole CA chain to be able to create 
succesfull connection. It seems lipqg does not support chained CA's.

I need to confirm that this is working as designed (I will be able to adjust my 
solution depending on the answer).

Thank you.

Kind regards,
Joanna

W dniu 2011-08-22 15:37:28 użytkownik Tom Lane  napisał:
> Asia  writes:
> > Now the issue is then when using libpq it was enough to have only root 
> > certificate in server's root.crt and it worked fine.
> > But when I tried using the same with JDBC it turned out that I need to put 
> > whole chain (2 certs) of Intermediate CA 1 in server's root.crt.
> 
> This is poor configuration, because every certificate listed in root.crt
> is considered fully trusted for every purpose.  It's best to keep only
> top-level root certs in root.crt.  Instead, put the full chain of
> certificates into the client's postgresql.crt, as per the manual:
> 
> : In some cases, the client certificate might be signed by an
> : "intermediate" certificate authority, rather than one that is directly
> : trusted by the server. To use such a certificate, append the certificate
> : of the signing authority to the postgresql.crt file, then its parent
> : authority's certificate, and so on up to a "root" authority that is
> : trusted by the server. The root certificate should be included in every
> : case where postgresql.crt contains more than one certificate.
> 
> In the JDBC case you'd need to put all those certs into the client's
> keystore, which I'm afraid I don't know the details of doing.  Possibly
> somebody on pgsql-jdbc could help you with that.
> 
>   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] securing the sql server ?

[Condor]

On Mon, 22 Aug 2011 14:20:00 -0400 (EDT), Gary Chambers wrote:
so when this remote server reboots, where does the encryption key 
come

from?


Why, from a file that resides in /root on the server, of course!  :-)
That's secure, right?

-- Gary Chambers


Isn't necessary to be on the same server. The key can be stored 
somewhere
on NFS for example, if you loss the server, they cant access the key 
because

server wont have access to local NFS storage.
Another example, can be stored over flash memory and when server is
restarted the monitor guy insert flash memory. It's have so many 
variants.

I did not understand what is the point of your comment.


R.

--
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] Getting value of bind variables

[Jayadevan M]

Hi,
Thank you for the reply.
> > I am trying to debug a query that gives an error. The error in the 
> > application server log is 
> > " ERROR: operator does not exist: timestamp without time zone = 
character 
> > varying
> 
> > It looks like the error is for the condition ( CUSINDINF.MEMDOB ) = 
($4) , 
> > memdob being a date of birth (timestamp) column. When I try the query 
at 
> > psql with some values, the data is retrieved OK. Is there some logging 

> > available in PostgreSQL that will tell me what values were actually 
used?
> 
> Yes, if you're using a reasonably recent version of Postgres ---
> log_statements should provide that information.  However, it's 100%
> irrelevant to this problem what the specific value is.  The problem is
> that the application is declaring the *type* of $4 as varchar rather
> than something appropriate.
> 
I guess so. But when I tried the same query on psql by replacing ($4) with 
a value like '20110404', the query works OK. The value of $4 is being 
passed from a java application. So does this mean I have to change the 
data type in java code?

By the way ,even log_statemtnts did not give me the bind variable value. 
It gave me values for bind variables for queries which got executed, not 
for this particular query. I am logging to csv file and loading it to the 
postgres_log as mentioned in the document. For this scenario, does the 
output go somewhere else? I am looking at the column query_text (select * 
from postgres_log where error_severity='ERROR' and log_time > '20110818')

and it shows
.AND   (  CUSINDINF.MEMDOB  )=  ($4)  


Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."

Re: [GENERAL] Updating Arrays

[Tom Lane]

Merlin Moncure  writes:
> On Mon, Aug 22, 2011 at 8:49 PM, Bob Pawley  wrote:
>> I'm not sure what you mean.
>> _base -- not base -- is the column name.

> oops. anyways, the important point is on the right side of the equals sign.
> Update library.compare set _base[2] = 2 where process_id = 2;

Right.  If you assign to _base[2], you must provide a single numeric
value.  Not a one-element array.

OTOH, if you were to assign to _base[2:2] (an array slice containing
just that one element), you must provide a one-element numeric array on
the righthand side.  So if there's some reason why you need to write
'{2}' and not just '2', you can accommodate that by adjusting what
you're assigning to.

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] Updating Arrays

[David Johnston]

> -Original Message- From: Merlin Moncure
> Sent: Monday, August 22, 2011 11:03 AM
> To: Bob Pawley
> Cc: Postgresql
> Subject: Re: [GENERAL] Updating Arrays
> 
> On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley  wrote:
>> Hi
>> 
>> I exploring the use of arrays.
>> 
>> So far I have created a table and inserted a row and updated the row with an
>> array
>> Update library.compare
>>  set _base =
>>  '{2, 0.764149497122068, 4.8886}'
>>  where process_id = 2;
>> – successfully.
>> 
>> However when I attempt to update a specific element of the array
>> 
>> Update library.compare
>> set _base[2] =
>> '{2}'
>> where process_id = 2;
>> I get an error “invalid input syntax for type numeric: "{2}"”
> 
> try "set base[2]  = 2"
> 
> merlin
> 
> On Aug 22, 2011, at 21:49, "Bob Pawley"  wrote:

> I'm not sure what you mean.
> 
> _base -- not base -- is the column name.
> 
> Bob

Your column name has a leading underscore? On the table or is it an alias? 
Regardless is it that hard to replace the mis-typed column name with the 
correct one?

Compare these statements:
Original
... Set base = {1,2,3};
SELECT base[2] ... ;

Updates
Set base[2] = {2};  -- replaces the second value with an array of 1 dimension 
and a single value.
set base[2] = 2;  -- replaces the second value with a single numeric value

Now, from the above select, is the second value an array or a single value?

And please do not top-post.  Personally it isn't that big a deal to me but 
since the first reply was bottom posted you should follow suit.  Even when you 
reply first on this list it is customary to bottom-post.  When in Rome...

David J.



-- 
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] Updating Arrays

[Merlin Moncure]

> -Original Message- From: Merlin Moncure
> Sent: Monday, August 22, 2011 11:03 AM
> To: Bob Pawley
> Cc: Postgresql
> Subject: Re: [GENERAL] Updating Arrays
>
> On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley  wrote:
>>
>> Hi
>>
>> I exploring the use of arrays.
>>
>> So far I have created a table and inserted a row and updated the row with
>> an
>> array
>> Update library.compare
>>  set _base =
>>  '{2, 0.764149497122068, 4.8886}'
>>  where process_id = 2;
>> – successfully.
>>
>> However when I attempt to update a specific element of the array
>>
>> Update library.compare
>> set _base[2] =
>> '{2}'
>> where process_id = 2;
>> I get an error “invalid input syntax for type numeric: "{2}"”
>
> try "set base[2]  = 2"
>
On Mon, Aug 22, 2011 at 8:49 PM, Bob Pawley  wrote:
> I'm not sure what you mean.
>
> _base -- not base -- is the column name.

oops. anyways, the important point is on the right side of the equals sign.

Update library.compare set _base[2] = 2 where process_id = 2;

(also, please try to avoid top posting)

merlin

-- 
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] Updating Arrays

[Bob Pawley]

I'm not sure what you mean.

_base -- not base -- is the column name.

Bob

-Original Message- 
From: Merlin Moncure

Sent: Monday, August 22, 2011 11:03 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley  wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with 
an

array
Update library.compare
  set _base =
  '{2, 0.764149497122068, 4.8886}'
  where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: "{2}"”


try "set base[2]  = 2"

merlin

--
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

[GENERAL] SETOF anyelement - get_call_result_type

[Courtin Olivier]

Hi all,

I'm trying from a C function to return a SETOF anyelement,
but get_call_result_type(fcinfo, NULL, &tupdesc)
don't return TYPEFUNC_COMPOSITE  as expected.

Something i've missed ?

(I know i can do it with a SETOF RECORD instead,
but it leads to explicitely define the output type of each column
and i want to avoid this, if possible.)


Thanks in advance,

--
Olivier

--
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] question regarding full_page_write

[Greg Smith]

On 08/22/2011 05:07 PM, Martín Marqués wrote:

My question regarding your answer is, why is it important for the
first page after a checkpoint and not on other page writes?
   


The first time a page is written after a checkpoint, when 
full_page_writes is on, the entire 8K page is written out to disk at 
that point.  The idea is that if the page is corrupted in any way by a 
partial write, you can restore it to a known good state again by using 
this version.  After that copy, though, additional modifications to the 
page only need to save the delta of what changed, at the row level.  If 
there's a crash, during recovery the full page image will be written, 
then the series of deltas, ending up with the same data as was intended.


This whole mechanism resets again each time a checkpoint finishes, and 
the full page writes start all over again.  One of the main purposes of 
checkpoints are to move forward the pointer of how far back crash 
recovery needs to replay from.  Starting each new checkpoint over again, 
with a full copy of all the data modified going into the WAL, it is part 
of that logic.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--
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] question regarding full_page_write

[Martín Marqués]

2011/2/17 Greg Smith :
> AI Rumman wrote:
>>
>> I can't clearly understand what FULL_PAGE_WRITE parameter is stand for.
>> Documentation suggest that If I make it OFF, then I have the chance for DB
>> crash.
>> Can anyone please tell me how it could be happened?
>
> The database writes to disk in 8K blocks.  If you can be sure that your disk
> drives and operating system will always write in 8K blocks, you can get a
> performance improvement from turning full_page_writes off.  But if you do
> that, and it turns out that when the power is interrupted your disk setup
> will actually do partial writes of less than 8K, your database can get
> corrupted.  Your system needs to ensure that when a write happens, either
> the whole thing goes to disk, or none of it does.

Sorry for the late reply, but I was investigating this option in
postgresql.conf and saw this mail.

My question regarding your answer is, why is it important for the
first page after a checkpoint and not on other page writes?


-- 
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

-- 
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] securing the sql server ?

[Gary Chambers]

so when this remote server reboots, where does the encryption key come
from?


Why, from a file that resides in /root on the server, of course!  :-)
That's secure, right?

-- Gary Chambers

--
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] securing the sql server ?

[Condor]

On Mon, 22 Aug 2011 10:54:23 -0700, John R Pierce wrote:

On 08/22/11 10:34 AM, Condor wrote:


I did not worry about hackers attack, I worrying how to secure data
if I physical loss hard drives or server.


so when this remote server reboots, where does the encryption key 
come from?




--
john r pierceN 37, W 122
santa cruz ca mid-left coast



Mount over NFS ?

R.

--
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] securing the sql server ?

[Chris Travers]

On Mon, Aug 22, 2011 at 10:34 AM, Condor  wrote:

> I did not worry about hackers attack, I worrying how to secure data
> if I physical loss hard drives or server. Let's just imagine that you have
> a server and you store on it important data like credit cards, bank acc,
> password, clients names, addresses, social numbers, phone numbers and
> some think like that ... very important information. When front end is
> secured Im worry if I loss hard drives or server. I think if some one
> buy so expensive server is not necessary some one with gun to watching it.

The best solution typically is to see how much of the stuff like
credit card numbers you can avoid storing.   Absence of the target is
in fact the best way to keep the target secure.

If yo must store credit card data, then the PCI-DSS requires that
these be stored encrypted with proper key management controls in
place.  The key management controls are the hard part.  Throwing
together something that's PCI-DSS-compliant on the surface looks easy.
 Doing it right is surprisingly hard.  On top of this you have to
think about the fact that key management can become a significant
issue.  When you change keys, think about the level of work that
requires on the part of the database server to decrypt stuff with the
old key and encrypt it all with the new key

Best Wishes,
Chris Travers

-- 
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] securing the sql server ?

[Chris Travers]

On Mon, Aug 22, 2011 at 1:40 AM, Condor  wrote:
> Hello ppl,
> any one can tell me how I can secure linux server with database postgres for
> example ?

Here are a few steps:
1)  Identify your environment.  Focus on details.  For example, this
might be in a monitored server room where access is required to reach
the physical server.
2)  Identify the security threats of that environment.  In such an
environment you have physical security threats which are handled
through physical security, and network security threats which are
handled through network security.  The software might also have other
security considerations.
3)  Identify a level of risk that is an acceptable tradeoff between
security and usability.
4)  Create a plan along the general lines of that acceptable tradeoff.

> Im thinking to make a cryptfs file system and to deploy database over the
> cryptfs. The problem
> here may will be when front end need any data for in/out cpus of the server
> will aways
>  decrypt/encrypt data and performance will be very low.

And besides what does it buy you?  What are you protecting against?

>
> I remember a few months ago some one ask similar question about how he can
> crypt data that is
> stored on database and problem was the key. Key is stored on the same server
> if some one
> get access can decrypt data.

In general, trying to use cryptography-based security is a bad
tradeoff.  There are times when it is important but then the
cryptographic management needs to be built in at every level.   For
example, the user might have a key which is used to decrypt the actual
storage key.  The thing is that has to be handled app-side, and unless
you really have a good idea of what you are doing and why, chances are
all you will succeed in doing is killing performance.

I have looked at a very few cases where this is not a bad tradeoff.
However those are pretty rare.

Best Wishes,
Chris Travers

-- 
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] Updating Arrays

[Merlin Moncure]

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley  wrote:
> Hi
>
> I exploring the use of arrays.
>
> So far I have created a table and inserted a row and updated the row with an
> array
> Update library.compare
>   set _base =
>   '{2, 0.764149497122068, 4.8886}'
>   where process_id = 2;
> – successfully.
>
> However when I attempt to update a specific element of the array
>
> Update library.compare
> set _base[2] =
> '{2}'
> where process_id = 2;
> I get an error “invalid input syntax for type numeric: "{2}"”

try "set base[2]  = 2"

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Updating Arrays

[Bob Pawley]

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with an 
array
Update library.compare 
  set _base = 
  '{2, 0.764149497122068, 4.8886}'
  where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array 

Update library.compare 
set _base[2] = 
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: "{2}"”

Bob

Create table library.compare (
id serial UNIQUE,
process_id int4,
device_id int4, 
_base decimal[] 
);
Insert into library.compare (process_id) 
values ('2');

Re: [GENERAL] securing the sql server ?

[John R Pierce]

On 08/22/11 10:34 AM, Condor wrote:


I did not worry about hackers attack, I worrying how to secure data
if I physical loss hard drives or server. 


so when this remote server reboots, where does the encryption key come from?



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] securing the sql server ?

[Condor]

On Mon, 22 Aug 2011 09:32:39 -0500, Merlin Moncure wrote:

On Mon, Aug 22, 2011 at 3:40 AM, Condor  wrote:

Hello ppl,
any one can tell me how I can secure linux server with database 
postgres for

example ?
Im thinking to make a cryptfs file system and to deploy database 
over the

cryptfs. The problem
here may will be when front end need any data for in/out cpus of the 
server

will aways
 decrypt/encrypt data and performance will be very low.

I remember a few months ago some one ask similar question about how 
he can

crypt data that is
stored on database and problem was the key. Key is stored on the 
same server

if some one
get access can decrypt data.

Any one have some ideas how to make something like crypt bubble and 
to store

database there ?
Or something else ?


Worrying about security without defining and understanding the 
threats

you face is a pointless exercise.  If you are worried about physical
loss of the drive, a better defense is to encrypt/decrypt sensitive
data on the client so that the server is not exposed to the key.
Obviously, this has downsides like not being able to index or ad hoc
search the data in question.  So, who are you worried about -- what
are the threats?

merlin



I did not worry about hackers attack, I worrying how to secure data
if I physical loss hard drives or server. Let's just imagine that you 
have
a server and you store on it important data like credit cards, bank 
acc,

password, clients names, addresses, social numbers, phone numbers and
some think like that ... very important information. When front end is
secured Im worry if I loss hard drives or server. I think if some one
buy so expensive server is not necessary some one with gun to watching 
it.


R.

--
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] securing the sql server ?

[Michael Nolan]

On Mon, Aug 22, 2011 at 3:40 AM, Condor  wrote:

> Hello ppl,
> any one can tell me how I can secure linux server with database postgres
> for example ?
> Im thinking to make a cryptfs file system and to deploy database over the
> cryptfs. The problem
> here may will be when front end need any data for in/out cpus of the server
> will aways
>  decrypt/encrypt data and performance will be very low.
>
> I remember a few months ago some one ask similar question about how he can
> crypt data that is
> stored on database and problem was the key. Key is stored on the same
> server if some one
> get access can decrypt data.
>
> Any one have some ideas how to make something like crypt bubble and to
> store database there ?
> Or something else ?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

Recently, as a test I created a database using encfs and ran some tests
against the same database but unencrypted.

In both cases the data was being stored on a 500 GB external hard drive
connected via USB2 to an HP laptop running Linux Fedora 15.

I found that the encrypted database ran 15-20% slower on PostgreSQL 9.0.4 on
most queries.
--
Mike Nolan

Re: [GENERAL] dependency on columns basis

[Adrian Klaver]

On Monday, August 22, 2011 7:47:47 am salah jubeh wrote:
> Hello,
> 
> I have a lookup table I want to delete  which is referenced as a foreign
> key in another table.  Is there is a way to find all of the  tables
> depends on this column (foreign key) but not the whole table or view.
> 
> 
> Thanks in advance

In psql:
\d referenced_table

Example:

test(5432)aklaver=>\d fk_1
   Table "public.fk_1"
 Column  |   Type| Modifiers 
-+---+---
 id  | integer   | not null
 txt_fld | character varying | 
Indexes:
"fk_1_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "fk_2" CONSTRAINT "fk_2_id2_fkey" FOREIGN KEY (id2) REFERENCES 
fk_1(id) ON DELETE CASCADE


-- 
Adrian Klaver
adrian.kla...@gmail.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] dependency on columns basis

[salah jubeh]

Hello, 

I have a lookup table I want to delete  which is referenced as a foreign key in 
another table.  Is there is a way to find all of the  tables depends on this 
column (foreign key) but not the whole table or view. 


Thanks in advance 

Re: [GENERAL] postgresql server crash on windows 7 when using plpython

[Adrian Klaver]

On Monday, August 22, 2011 7:24:30 am c k wrote:
> No i didn't removed any thing. Only I have installed python 2.6. And then
> tried to create plpythonu. It got created, but when tries to execute
> already mentioned function server crashes.
> 
> I didn't have  any clue.

My guess is this is the clue:

"First I installed python 2.7 and then added python installation
 path to windows PATH variable. ..."

It is very possible you have a version cross reference going on. In other words 
pl/pythonu is being compiled against one version of Python, but run against 
another. If it is possible I would remove the Python 2.7 installation or at 
least the references to it and then recompile pl/pythonu.


> 
> Thanks and regards.
> 
> Chaitany Kulkarni
> 

-- 
Adrian Klaver
adrian.kla...@gmail.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] securing the sql server ?

[Merlin Moncure]

On Mon, Aug 22, 2011 at 3:40 AM, Condor  wrote:
> Hello ppl,
> any one can tell me how I can secure linux server with database postgres for
> example ?
> Im thinking to make a cryptfs file system and to deploy database over the
> cryptfs. The problem
> here may will be when front end need any data for in/out cpus of the server
> will aways
>  decrypt/encrypt data and performance will be very low.
>
> I remember a few months ago some one ask similar question about how he can
> crypt data that is
> stored on database and problem was the key. Key is stored on the same server
> if some one
> get access can decrypt data.
>
> Any one have some ideas how to make something like crypt bubble and to store
> database there ?
> Or something else ?

Worrying about security without defining and understanding the threats
you face is a pointless exercise.  If you are worried about physical
loss of the drive, a better defense is to encrypt/decrypt sensitive
data on the client so that the server is not exposed to the key.
Obviously, this has downsides like not being able to index or ad hoc
search the data in question.  So, who are you worried about -- what
are the threats?

merlin

-- 
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] postgresql server crash on windows 7 when using plpython

[c k]

No i didn't removed any thing. Only I have installed python 2.6. And then
tried to create plpythonu. It got created, but when tries to execute already
mentioned function server crashes.

I didn't have  any clue.

Thanks and regards.

Chaitany Kulkarni

On Sun, Aug 14, 2011 at 12:37 AM, Adrian Klaver wrote:

> On Saturday, August 13, 2011 8:35:08 am c k wrote:
> > I am not going to do stack trace right now, because I am using wireless
> > broadband and is much costlier to download.
> > But as other functions are working, may be it is related with other
> thing.
> > I have forgotten to told that I am using virtual machine for windows. For
> > even this the normal python interpreter does not have any problems. So it
> > seems that the problem is occurring due to crash in python package.
>
> My suspicion is it has to do with this from your original post:
>
> "First I installed python 2.7 and then added python installation
> path to windows PATH variable. ..."
>
> " Then I go to create plpythonu in a database. But it failed.
> After searching, I found that it needs python 2.6. So I again
> installed python 2.6 and then dropped plpython from database
> and created again."
>
> Unmentioned is whether you uninstalled Python 2.7 or removed the PATH
> reference
> to Python 2.7
>
> >
> > Chaitanya Kulkarni
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

Re: [GENERAL] Error with "pg_filenode.map" file

[Adrian Klaver]

On Monday, August 22, 2011 5:11:08 am alexondi wrote:
> Hi!
> Sometimes I got this error
> 
> FATAL:  could not open relation mapping file "global/pg_filenode.map": No
> such file or directory
> 
> and after restart computer all is fine. What mean this?
> 

Meant to add to my previous post:

When you get this error is the pg_filenode.map file actually missing?

It should be in the Postgres $DATA directory under the global subdirectory.

-- 
Adrian Klaver
adrian.kla...@gmail.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] Error with "pg_filenode.map" file

[Adrian Klaver]

On Monday, August 22, 2011 5:11:08 am alexondi wrote:
> Hi!
> Sometimes I got this error
> 
> FATAL:  could not open relation mapping file "global/pg_filenode.map": No
> such file or directory
> 
> and after restart computer all is fine. What mean this?
> 

Some more information would be helpful:
Postgres version
OS version
Anything else happening at same time?
Any other errors in logs?

-- 
Adrian Klaver
adrian.kla...@gmail.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_controldata return wrong version

[Adrian Klaver]

On Monday, August 22, 2011 5:17:47 am alexondi wrote:
> Hi!
> I use postgresql version 9.1beta3 and use pg_controldata and when I see
> first row
> 
> pg_controldata version number: 903

I believe you are actually seeing:
pg_control version number:903

This refers to the pg_control file and its version. 

> 
> Is this right version?
> 

Right version of what? If you mean does it refer to Postgres version 9.0.3, 
then 
no it has no relation to the overall Postgres version number. If you mean is it 
the right version of pg_control for Postgres 9.1b3, that I can not tell you.


-- 
Adrian Klaver
adrian.kla...@gmail.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] SSL certificates issue

[Tom Lane]

Asia  writes:
> Now the issue is then when using libpq it was enough to have only root 
> certificate in server's root.crt and it worked fine.
> But when I tried using the same with JDBC it turned out that I need to put 
> whole chain (2 certs) of Intermediate CA 1 in server's root.crt.

This is poor configuration, because every certificate listed in root.crt
is considered fully trusted for every purpose.  It's best to keep only
top-level root certs in root.crt.  Instead, put the full chain of
certificates into the client's postgresql.crt, as per the manual:

: In some cases, the client certificate might be signed by an
: "intermediate" certificate authority, rather than one that is directly
: trusted by the server. To use such a certificate, append the certificate
: of the signing authority to the postgresql.crt file, then its parent
: authority's certificate, and so on up to a "root" authority that is
: trusted by the server. The root certificate should be included in every
: case where postgresql.crt contains more than one certificate.

In the JDBC case you'd need to put all those certs into the client's
keystore, which I'm afraid I don't know the details of doing.  Possibly
somebody on pgsql-jdbc could help you with that.

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

[GENERAL] pg_controldata return wrong version

[alexondi]

Hi!
I use postgresql version 9.1beta3 and use pg_controldata and when I see
first row 

pg_controldata version number: 903

Is this right version? 


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-controldata-return-wrong-version-tp4723194p4723194.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] Error with "pg_filenode.map" file

[alexondi]

Hi!
Sometimes I got this error

FATAL:  could not open relation mapping file "global/pg_filenode.map": No
such file or directory

and after restart computer all is fine. What mean this?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Error-with-pg-filenode-map-file-tp4723167p4723167.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] SSL certificates issue

[Asia]

Recently I have been working on implementation of mutual SSL authentication 
between our application and PostgreSQL database.
I managed to make it work wih "ssl=true" connection option and "clientcert=1" 
flags in pg_hba.conf. Moreover I managed to make it work with C++ application 
using libpq and Java application using postgresql JDBC driver.

The only concern I have is some discrepancy between the way libpq and JDBC 
works. It seems that libpq is less restrictive than JDBC with standard
built-in SSLSocketFactory.
The following schema describes some information about my certs:

Client  Server
postgresql.crt   server.crt
postgresql.key  server.key
root.crt   root.crt

where postgresql.crt is singed by Intermediate CA 1
 server.crt is signed by Intermediate CA 2
 Intermediate CA 1 and Intermediate CA 2 are chain certs both singed by the 
same root certificate
 root.crt - root certificate that signed Intermediate CA1 and 
Intermediate CA 2 (which was enough for libpq but not enough for JDBC)
  
Now the issue is then when using libpq it was enough to have only root 
certificate in server's root.crt and it worked fine.
But when I tried using the same with JDBC it turned out that I need to put 
whole chain (2 certs) of Intermediate CA 1 in server's root.crt.

All I need is the confirmation whether this is working as designed or this is 
possibly libpq bug?

Kind Regards,
Joanna


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] securing the sql server ?

[Condor]

Hello ppl,
any one can tell me how I can secure linux server with database 
postgres for example ?
Im thinking to make a cryptfs file system and to deploy database over 
the cryptfs. The problem
here may will be when front end need any data for in/out cpus of the 
server will aways

 decrypt/encrypt data and performance will be very low.

I remember a few months ago some one ask similar question about how he 
can crypt data that is
stored on database and problem was the key. Key is stored on the same 
server if some one

get access can decrypt data.

Any one have some ideas how to make something like crypt bubble and to 
store database there ?

Or something else ?

R.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general