Re: [GENERAL] PG and database encryption

[Paul Jungwirth]

On 08/22/2017 01:08 PM, John McKown wrote:

On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464
 wrote:

We have a requirement to encrypt the entire database.


Personally, what I'd do (and actually do at work) is to us LUKS.


I second that, although I'll add that if you're on AWS you can also use 
encrypted EBS volumes. You get a very similar effect, except all you 
need to do is tick a checkbox (or set a CloudFormation attribute, etc.). 
Also you can get unattended reboots without storing the key somewhere 
vulnerable. There may be perf advantages too; I'm not sure.


Good luck!
Paul


--
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] Retrieving query results

[Michael Paquier]

On Wed, Aug 23, 2017 at 3:19 AM, Igor Korot  wrote:
> [quote]
> PQntuples
>
> Returns the number of rows (tuples) in the query result. Because it
> returns an integer result, large result sets might overflow the return
> value on 32-bit operating systems.
>
> int PQntuples(const PGresult *res);
>  [/quote]
>
> Is there another way to not to overflow the result?

Not really with the existing API. Note that getting at 2 billion rows
is really a lot, and would cause performance issues on the application
side because a bunch of data would need to be processed, and getting
out this much data is not network-wise anyway.
-- 
Michael


-- 
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_rewind - restore new slave failed to startup during recovery

[Michael Paquier]

On Tue, Aug 22, 2017 at 11:39 PM, Magnus Hagander  wrote:
> On Tue, Aug 22, 2017 at 3:06 AM, Michael Paquier 
> wrote:
>> That flow looks correct to me. No I think that you should trigger
>> manually a checkpoint after step 2 on the promoted standby so as its
>> control file gets forcibly updated correctly with its new timeline
>> number. This is a small but critical point people usually miss. The
>> documentation of pg_rewind does not mention this point when using a
>> live source server, and many people have fallen into this trap up to
>> now... We should really mention that in the docs. What do others
>> think?
>
> If the documentation is missing such a clearly critical step, then I would
> say that's a definite documentation bug and it needs to be fixed. We can't
> really fault people for missing a small detail if we didn't document the
> small detail...

What do you think about the attached? I would recommend a back-patch
down to 9.5 to get the documentation right everywhere but I think as
well that this may not be enough. We could document as well an example
of a full-fledged failover flow in the Notes, in short:
1) Promote a standby.
2) Stop the old master cleanly. If it has been killed atrociously,
make it finish recovery once and then stop it so as its WAL data is
ahead of the point WAL has fork after the promotion (shutdown
checkpoint record is at least here).
3) Prepare source server for the rewind.
3-1) Using file copy, stop the source server (promoted standby) cleanly first.
3-2) Using SQL, issue a checkpoint on the source server to update its
control file and making sure that the timeline number is up-do-date on
disk.
4) Perform the actual rewind. This will need WAL segments on the
target from the point WAL has forked to the shutdown checkpoint record
created at step 2).
5) Create recovery.conf on the target and point it to the source for
streaming, or archives. Then let it perform recovery.
-- 
Michael


rewind-checkpoint-doc.patch
Description: Binary data

-- 
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] Retrieving query results

[Igor Korot]

Hi, Michael,

On Tue, Aug 22, 2017 at 8:32 PM, Michael Paquier
 wrote:
> On Wed, Aug 23, 2017 at 3:19 AM, Igor Korot  wrote:
>> [quote]
>> PQntuples
>>
>> Returns the number of rows (tuples) in the query result. Because it
>> returns an integer result, large result sets might overflow the return
>> value on 32-bit operating systems.
>>
>> int PQntuples(const PGresult *res);
>>  [/quote]
>>
>> Is there another way to not to overflow the result?
>
> Not really with the existing API.


What do you mean "not really"

> Note that getting at 2 billion rows
> is really a lot, and would cause performance issues on the application
> side because a bunch of data would need to be processed, and getting
> out this much data is not network-wise anyway.

That's OK, As long as my program works with arbitrary number of rows.

Thank you.

> --
> Michael


-- 
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] Porting libpq to QNX 4.25

[Tom Lane]

marcelo  writes:
> Is there a libpq porting to QNX 4.25? I just tried to compile one of the 
> modules, but was rejected because the QNX's standard library have not an 
> Int64 type.

We removed QNX support in 8.2, so you could try using some pre-8.2
release.  It's possible it was broken for awhile before that, though,
since the reason for killing it was that no one had shown any interest
in testing it in a long time.

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] What is the proper query

[Igor Korot]

Or this is the bug in 9.1?
Since it looks like there are 2 columns with the same info in 1 table/view

Thank you.


On Tue, Aug 22, 2017 at 12:08 AM, Igor Korot  wrote:
> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage WHERE
> table_schema = 'public' AND table_name = 'leaguescorehitter';
>  constraint_catalog | constraint_schema |constraint_name
>   | table_catalog | table_schema |table_name | column_name |
> ordinal_position | position_in_unique_constraint
> +---++---+--+---+-+--+---
>  draft  | public| leaguescorehitter_id_fkey
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | playerid|
>  2 | 2
>  draft  | public|
> leaguescorehitter_scoreid_fkey | draft | public   |
> leaguescorehitter | scoreid |1 |
>   1
> (4 rows)
>
> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>
> Thank you.


-- 
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] What is the proper query

[Igor Korot]

Hi, David,

On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
 wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:
>>
>> Or this is the bug in 9.1?
>> Since it looks like there are 2 columns with the same info in 1
>> table/view
>
>
> This old email thread sounds similar to what you are describing here.
>
> https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com

Consider following table creation command:

CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
integer, value double, foreign key(id) references leagues(id), foreign
key(id, playerid) references playersinleague(id, playerid), foreign
key(scoreid) references scorehits(scoreid));

There are 3 foreign keys in this table for which there are 4 rows
displayed in my query as it should be:

1 for leagues(id)
1 for scorehits(scoreid)
2 for playersinleague(id,playerid) - 1 row per field

However what I would expect to see is:

[code]
ordinal_position  |position_in_unique_constraint
  0  1
   - this is for leagues(id)
  1  1
  1  2
   - those 2 are for
playersinleague(id,playerid)
  2  1
   - this is for scorehits(scoreid)
[/code]

Instead I got ordinal_positionv = position_in_unique_constraints and can't tell
which constraint is which, or more precisely, when the one ends and
second starts.

Hopefully this above will not be mangled and the spacing will be kept.

Thank you.

>
> 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] What is the proper query

[David G. Johnston]

On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:

> Or this is the bug in 9.1?
> Since it looks like there are 2 columns with the same info in 1
> table/view
>

​This old email thread sounds similar to what you are describing here.

​https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com

David J.

Re: [GENERAL] What is the proper query

[David G. Johnston]

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot  wrote:

> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage
>
​[...]​


> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>

Not using the key_column_usage view.  What that view is doing is basically
saying (my understanding from reading the docs, not testing it out):

CREATE TABLE tbl_pk
UNIQUE (col1, col2)​

CREATE TABLE tbl_fk
FOREIGN (col2, col1) REFERENCES tbl_pk (col1, col2)

Now your ordinal/position rows would be:

(1, 2)
(2, 1)

instead of:

(1, 1)
(2, 2)

if you had defined the FK and PK with the same column names in the same
order, like is done almost always and like you did in your example.

If you want to enumerate constraints you need to use a different
information_schema view or, as Melvin showed, use pg_catalog.  I'm not
fluent enough to provide examples.  If you provide the question/problem you
are trying to resolve others will likely offer suggestions.

David J.

Re: [GENERAL] pg_rewind - restore new slave failed to startup during recovery

[Magnus Hagander]

On Tue, Aug 22, 2017 at 3:06 AM, Michael Paquier 
wrote:

> On Tue, Aug 22, 2017 at 9:52 AM, Dylan Luong 
> wrote:
> > 1.   Disable the master ltm member (all connections redired to slave
> > member)
> > 2.   Promote slave (touch promote.me)
> > 3.   Stop the master db (old master)
> > 4.   Do pg_rewind on the new slave (old master)
> > 5.   Start the new slave.
>
> That flow looks correct to me. No I think that you should trigger
> manually a checkpoint after step 2 on the promoted standby so as its
> control file gets forcibly updated correctly with its new timeline
> number. This is a small but critical point people usually miss. The
> documentation of pg_rewind does not mention this point when using a
> live source server, and many people have fallen into this trap up to
> now... We should really mention that in the docs. What do others
> think?
>

If the documentation is missing such a clearly critical step, then I would
say that's a definite documentation bug and it needs to be fixed. We can't
really fault people for missing a small detail if we didn't document the
small detail...

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [GENERAL] Deadlocks

[Melvin Davidson]

On Tue, Aug 22, 2017 at 9:42 AM, Martin Moore 
wrote:

> Hi, I’m having issues with deadlocks.
>
> v9.6 on Debian Jessie.
>
> I have a number of ‘select’ functions which for logging purposes also call
> another fn that inserts a record into a ‘logging’ table.
>
> However, even with only 1 or 2 users making very infrequent calls to the
> parent fn, deadlocks are occurring. I’m hoping there’s something I’m
> unaware of as this shouldn’t happen!
>
> What could this be? I don’t have any logs at present as I removed the
> insert statement some time ago, but need to get this issue resolved.
>
> Thanks,
>
> Martin.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*I don't think you will get much help unless you show us the query and the
structure*
*of the tables involved.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Porting libpq to QNX 4.25

[marcelo]

Thank you, Tom.
We'll replace those QNX machines with WIndows XP ones (via dual boot), 
so we can use our Devart's ORM the same as the most "user oriented" 
applications.



On 22/08/17 12:39, Tom Lane wrote:

marcelo  writes:

Is there a libpq porting to QNX 4.25? I just tried to compile one of the
modules, but was rejected because the QNX's standard library have not an
Int64 type.

We removed QNX support in 8.2, so you could try using some pre-8.2
release.  It's possible it was broken for awhile before that, though,
since the reason for killing it was that no one had shown any interest
in testing it in a long time.

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] What is the proper query

[Melvin Davidson]

*While the information_schema is useful, there is no substitute for
learning how to use the pg_catalog and system information functions.*


*See if this query gives you what you are looking for:*






*SELECT rel.relname,   con.conname,   con.contype,
con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
JOIN pg_constraint con ON (con.conrelid = rel.oid)*

*ORDER by relname, contype, conname;*


On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot  wrote:

> Hi, David,
>
> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>  wrote:
> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:
> >>
> >> Or this is the bug in 9.1?
> >> Since it looks like there are 2 columns with the same info in 1
> >> table/view
> >
> >
> > This old email thread sounds similar to what you are describing here.
> >
> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>
> Consider following table creation command:
>
> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
> integer, value double, foreign key(id) references leagues(id), foreign
> key(id, playerid) references playersinleague(id, playerid), foreign
> key(scoreid) references scorehits(scoreid));
>
> There are 3 foreign keys in this table for which there are 4 rows
> displayed in my query as it should be:
>
> 1 for leagues(id)
> 1 for scorehits(scoreid)
> 2 for playersinleague(id,playerid) - 1 row per field
>
> However what I would expect to see is:
>
> [code]
> ordinal_position  |position_in_unique_constraint
>   0  1
>- this is for leagues(id)
>   1  1
>   1  2
>- those 2 are for
> playersinleague(id,playerid)
>   2  1
>- this is for scorehits(scoreid)
> [/code]
>
> Instead I got ordinal_positionv = position_in_unique_constraints and can't
> tell
> which constraint is which, or more precisely, when the one ends and
> second starts.
>
> Hopefully this above will not be mangled and the spacing will be kept.
>
> Thank you.
>
> >
> > 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
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Retrieving query results

[Igor Korot]

 Hi, ALL,
[quote]
PQntuples

Returns the number of rows (tuples) in the query result. Because it
returns an integer result, large result sets might overflow the return
value on 32-bit operating systems.

int PQntuples(const PGresult *res);
 [/quote]

Is there another way to not to overflow the result?

Thank you.


-- 
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 and database encryption

[Joshua D. Drake]

On 08/22/2017 12:48 PM, rakeshkumar464 wrote:

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.


If you are encrypting the entire database, use the filesystem.

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/chap-Security_Guide-Encryption.html

Thanks!

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *


--
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 and database encryption

[John McKown]

On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464
 wrote:
> We have a requirement to encrypt the entire database.  What is the best tool
> to accomplish this. Our primary goal is that it should be transparent to the
> application, with no change in the application, as compared to un-encrypted
> database. Reading about pgcrypto module, it seems it is good for few columns
> only and using it to encrypt entire database is not a good use-case.
>
> Is this which can be done best by file level encryption?  What are the good
> tools on Linux (RHES), preferably open-source.
>
> Thanks

In addition to the link that Joshua gave you, there is this:
https://www.enterprisedb.com/blog/postgres-and-transparent-data-encryption-tde

Personally, what I'd do (and actually do at work) is to us LUKS. This
is a "full disk encryption". When the filesystem is mounted, the
system asks for the password. Unfortunately, this method allows all
users who have the proper authority (UNIX & SELinux) to read (maybe
write) the underlying files. Of course, a properly secured environment
would not allow this, but systems can be hacked. And it does not
address any off-filesystem backups, which would need to be separately
encrypted. LUKS is a good method, IMO, to protect the data if the
media is stolen, but not for protecting the individual files from
improper access. SELinux is pretty good at that.


-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown


-- 
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 and database encryption

[rakeshkumar464]

Thanks John and JD.

John: Are you telling that the backup of a database has no protection?  



--
View this message in context: 
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618p5979624.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] PG and database encryption

[rakeshkumar464]

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.

Thanks



--
View this message in context: 
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618.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] make postgresql 9.5 default on centos 7

[Devrim Gündüz]

Hi,

On Fri, 2017-08-18 at 13:50 -0400, Steve Clark wrote:
> I loaded 9.5 on CentOS 7 but by default every thing wants to use the default
> 9.2 version that comes with CentOS 7.
> 
> Is there a simple way to fix this so the 9.5 version of tools and libraries
> are used.

yum remove postgresql-* 
yum reinstall postgresql95*

will do the trick. Please note that the second one will restart the database,
so make sure that you run these in a maintenance period.

The first command will remove all OS PostgreSQL packages. This is a required
step to remove actual binaries from OS. The second command will reinstall the
packages, and they will create alternatives entries for the binaries, etc.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi, Melvin,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson 
wrote:

>
>
>
> *While the information_schema is useful, there is no substitute for
> learning how to use the pg_catalog and system information functions.*
>
>
> *See if this query gives you what you are looking for:*
>
>
>
>
>
>
> *SELECT rel.relname,   con.conname,   con.contype,
> con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
> JOIN pg_constraint con ON (con.conrelid = rel.oid)*
>
> *ORDER by relname, contype, conname;*
>

Here is what I'm after:

select x.ordinal_position AS pos, x.position_in_unique_constraint AS
field_pos, c.constraint_name AS name, x.table_schema as schema,
x.table_name AS table, x.column_name AS column, y.table_schema as
ref_schema, y.table_name as ref_table, y.column_name as ref_column,
c.update_rule, c.delete_rule from
information_schema.referential_constraints c,
information_schema.key_column_usage x, information_schema.key_column_usage
y where x.constraint_name = c.constraint_name and y.ordinal_position =
x.position_in_unique_constraint and y.constraint_name =
c.unique_constraint_name AND x.table_schema = $1 AND x.table_name = $2
order by c.constraint_name, x.ordinal_position;

Then in my C++ code:

std::map;

foreign_keys[pos].push_back( new FKField( field_pos, name, column,
ref_schema, ref_table, ref_column, update_rule, delete_rule ) );

This is my target.

Thank you.


>
>
> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot  wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>  wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:
>> >>
>> >> Or this is the bug in 9.1?
>> >> Since it looks like there are 2 columns with the same info in 1
>> >> table/view
>> >
>> >
>> > This old email thread sounds similar to what you are describing here.
>> >
>> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>>
>> Consider following table creation command:
>>
>> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
>> integer, value double, foreign key(id) references leagues(id), foreign
>> key(id, playerid) references playersinleague(id, playerid), foreign
>> key(scoreid) references scorehits(scoreid));
>>
>> There are 3 foreign keys in this table for which there are 4 rows
>> displayed in my query as it should be:
>>
>> 1 for leagues(id)
>> 1 for scorehits(scoreid)
>> 2 for playersinleague(id,playerid) - 1 row per field
>>
>> However what I would expect to see is:
>>
>> [code]
>> ordinal_position  |position_in_unique_constraint
>>   0  1
>>- this is for leagues(id)
>>   1  1
>>   1  2
>>- those 2 are for
>> playersinleague(id,playerid)
>>   2  1
>>- this is for scorehits(scoreid)
>> [/code]
>>
>> Instead I got ordinal_positionv = position_in_unique_constraints and
>> can't tell
>> which constraint is which, or more precisely, when the one ends and
>> second starts.
>>
>> Hopefully this above will not be mangled and the spacing will be kept.
>>
>> Thank you.
>>
>> >
>> > 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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] PG and database encryption

[Ron Johnson]

On 08/22/2017 02:55 PM, Joshua D. Drake wrote:

On 08/22/2017 12:48 PM, rakeshkumar464 wrote:

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.


If you are encrypting the entire database, use the filesystem.

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/chap-Security_Guide-Encryption.html 



But that's protection against stolen laptops.

--
World Peace Through Nuclear Pacification



--
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 and database encryption

[Condor]

On 22-08-2017 22:48, rakeshkumar464 wrote:
We have a requirement to encrypt the entire database.  What is the best 
tool
to accomplish this. Our primary goal is that it should be transparent 
to the
application, with no change in the application, as compared to 
un-encrypted
database. Reading about pgcrypto module, it seems it is good for few 
columns

only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the 
good

tools on Linux (RHES), preferably open-source.

Thanks



--
View this message in context:
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



Hello,
I also was interesting about this topic. My research take me to two 
problems that can be happened
if you encrypt your hard drive partition where is stored postgresql 
data. Postgresql does not support

encryption like oracle or mssql.

The problems that I mentored is two and they are connected with 
filesystem:


1. If some is happened on your filesystem and encrypted drive need to be 
force checked. It's can damage your files.
2. If LURKS is used, if problem is happened (bad sector, cluster 
problem) and that problem / bad sector is
there where is stored your LURKS header encryption data, you cannot 
mount your encrypted partition and

if you does not have experiences what to do, your data is lost forever.

My data is too important and because I don't have much time to make more 
researches, I get decision not to use encryption.
I think there is hardware named TDS or was IDS but may be is deprecated 
but Im not sure.


If you realize encryption somehow, drop us or me email with information.



Regards,
Hristo S.


--
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 and database encryption

[PT]

On Tue, 22 Aug 2017 12:48:13 -0700 (MST)
rakeshkumar464  wrote:

> We have a requirement to encrypt the entire database.  What is the best tool
> to accomplish this. Our primary goal is that it should be transparent to the
> application, with no change in the application, as compared to un-encrypted
> database. Reading about pgcrypto module, it seems it is good for few columns
> only and using it to encrypt entire database is not a good use-case.
> 
> Is this which can be done best by file level encryption?  What are the good
> tools on Linux (RHES), preferably open-source.

"encrypt the database" is bullshit wank terminology for "we're a government
agency and don't know what we're talking about"

On multiple occasions, I demonstrated that an unecrypted database was the
least likely disclosure vector for sensative data, and that we shouldn't
waste any time on it until we had ensured that all other breach vectors had
been fixed.  Over the course of 4 years at that job, we never managed to get
all the other (more likely) breach vectors secured.

While it's possible that you've already fixed all other breach
vectors, I'd be willing to bet actual money that you have not.
The very fact that you ask for something that "is transparent to the
application" tells me that you're not going to actually implement it
effectively anyway.

As a result, my opinion would be that you use filesystem encryption. It's
very efficient, low management overhead, and proven technology that doesn't
interfere with anything else you're doing. You can then check that box on
whatever form you have to fill out and the beaurocrats will leave you alone.
On top of that, it effectivley protects againts possible breach vectors that
don't require changing the application.

Real security will require changing the application. But take my word for it,
nobody wants to hear the list of breach vectors that can only be fixed by
modifying the application. Because people aren't interested in real security,
they're just interested in checking boxes on a form.

-- 
PT 


-- 
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] make postgresql 9.5 default on centos 7

[Scott Marlowe]

On Tue, Aug 22, 2017 at 12:44 PM, Devrim Gündüz  wrote:
>
> Hi,
>
> On Fri, 2017-08-18 at 13:50 -0400, Steve Clark wrote:
>> I loaded 9.5 on CentOS 7 but by default every thing wants to use the default
>> 9.2 version that comes with CentOS 7.
>>
>> Is there a simple way to fix this so the 9.5 version of tools and libraries
>> are used.
>
> yum remove postgresql-*
> yum reinstall postgresql95*
>
> will do the trick. Please note that the second one will restart the database,
> so make sure that you run these in a maintenance period.
>
> The first command will remove all OS PostgreSQL packages. This is a required
> step to remove actual binaries from OS. The second command will reinstall the
> packages, and they will create alternatives entries for the binaries, etc.

Centos and Yum have supported multiple versions of PostgreSQL for
quite some time now. Just change the ports in their respective
postgresql.conf files and start them up.


-- 
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 and database encryption

[Scott Marlowe]

On Tue, Aug 22, 2017 at 3:13 PM, PT  wrote:
> On Tue, 22 Aug 2017 12:48:13 -0700 (MST)
> rakeshkumar464  wrote:
>
>> We have a requirement to encrypt the entire database.  What is the best tool
>> to accomplish this. Our primary goal is that it should be transparent to the
>> application, with no change in the application, as compared to un-encrypted
>> database. Reading about pgcrypto module, it seems it is good for few columns
>> only and using it to encrypt entire database is not a good use-case.
>>
>> Is this which can be done best by file level encryption?  What are the good
>> tools on Linux (RHES), preferably open-source.
>
> "encrypt the database" is bullshit wank terminology for "we're a government
> agency and don't know what we're talking about"
>
> On multiple occasions, I demonstrated that an unecrypted database was the
> least likely disclosure vector for sensative data, and that we shouldn't
> waste any time on it until we had ensured that all other breach vectors had
> been fixed.  Over the course of 4 years at that job, we never managed to get
> all the other (more likely) breach vectors secured.
>
> While it's possible that you've already fixed all other breach
> vectors, I'd be willing to bet actual money that you have not.
> The very fact that you ask for something that "is transparent to the
> application" tells me that you're not going to actually implement it
> effectively anyway.
>
> As a result, my opinion would be that you use filesystem encryption. It's
> very efficient, low management overhead, and proven technology that doesn't
> interfere with anything else you're doing. You can then check that box on
> whatever form you have to fill out and the beaurocrats will leave you alone.
> On top of that, it effectivley protects againts possible breach vectors that
> don't require changing the application.
>
> Real security will require changing the application. But take my word for it,
> nobody wants to hear the list of breach vectors that can only be fixed by
> modifying the application. Because people aren't interested in real security,
> they're just interested in checking boxes on a form.

This. Without a much stricter definition of the attack vectors you're
trying to defeat "encrypt the whole database" is a very hand-wavy
proposition. Are you protecting against people getting into the data
center and stealing your hard drives? Rogue applications getting
access to the db? Someone sniffing the passwords or unencrypting them
on the servers etc etc.

OP: It's just generic a requirement to take seriously. Sit down, come
up with possible attack vectors and possible ways to thwart them.
Security isn't something you do one time and you're done, it's a
constant process of design, review, updates, and education.


-- 
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] What is the proper query

[Igor Korot]

Melvin et al,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson 
wrote:

>
>
>
> *While the information_schema is useful, there is no substitute for
> learning how to use the pg_catalog and system information functions.*
>
>
> *See if this query gives you what you are looking for:*
>
>
>
>
>
>
> *SELECT rel.relname,   con.conname,   con.contype,
> con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
> JOIN pg_constraint con ON (con.conrelid = rel.oid)*
>
> *ORDER by relname, contype, conname;*
>

I tried your query, but its not really what I'm looking for.

This is what I'm looking for (taken from SQLite shell):

sqlite> PRAGMA foreign_key_list(leaguescorehitter);
id|seq|table|from|to|on_update|on_delete|match
0|0|scorehits|scoreid|scoreid|NO ACTION|NO ACTION|NONE
1|0|playersinleague|id|id|NO ACTION|NO ACTION|NONE
1|1|playersinleague|playerid|playerid|NO ACTION|NO ACTION|NONE
2|0|leagues|id|id|NO ACTION|NO ACTION|NONE

Can I get something from PostgreSQL?

Thank you.


>
> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot  wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>  wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:
>> >>
>> >> Or this is the bug in 9.1?
>> >> Since it looks like there are 2 columns with the same info in 1
>> >> table/view
>> >
>> >
>> > This old email thread sounds similar to what you are describing here.
>> >
>> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>>
>> Consider following table creation command:
>>
>> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
>> integer, value double, foreign key(id) references leagues(id), foreign
>> key(id, playerid) references playersinleague(id, playerid), foreign
>> key(scoreid) references scorehits(scoreid));
>>
>> There are 3 foreign keys in this table for which there are 4 rows
>> displayed in my query as it should be:
>>
>> 1 for leagues(id)
>> 1 for scorehits(scoreid)
>> 2 for playersinleague(id,playerid) - 1 row per field
>>
>> However what I would expect to see is:
>>
>> [code]
>> ordinal_position  |position_in_unique_constraint
>>   0  1
>>- this is for leagues(id)
>>   1  1
>>   1  2
>>- those 2 are for
>> playersinleague(id,playerid)
>>   2  1
>>- this is for scorehits(scoreid)
>> [/code]
>>
>> Instead I got ordinal_positionv = position_in_unique_constraints and
>> can't tell
>> which constraint is which, or more precisely, when the one ends and
>> second starts.
>>
>> Hopefully this above will not be mangled and the spacing will be kept.
>>
>> Thank you.
>>
>> >
>> > 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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

[GENERAL] install the oracle data wrapper extension

[Peter Koukoulis]

Hi

Is there a reliable way to create a database link from PosgreSQL 9.6 to
Oracle 11g?
I am running 9.6 on Linux 64 bit.
I can connect to the Oracle database with sqlplus from the PostgreSQL
server.

Also, I have  in attempted to install the oracle_fdw wrapper as an
alternatve, but I keep getting the following error:

ft_node=# create extension oracle_fdw;
ERROR:  could not open extension control file
"/usr/share/postgresql/9.6/extension/oracle_fdw.control": No such file or
directory
ft_node=# \conninfo
You are connected to database "ft_node" as user "postgres" on host
"localhost" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)

Any suggestions?

P

Re: [GENERAL] install the oracle data wrapper extension

[Jeff Janes]

On Tue, Aug 22, 2017 at 2:47 PM, Peter Koukoulis 
wrote:

>
> Hi
>
> Is there a reliable way to create a database link from PosgreSQL 9.6 to
> Oracle 11g?
> I am running 9.6 on Linux 64 bit.
> I can connect to the Oracle database with sqlplus from the PostgreSQL
> server.
>
> Also, I have  in attempted to install the oracle_fdw wrapper as an
> alternatve, but I keep getting the following error:
>
> ft_node=# create extension oracle_fdw;
> ERROR:  could not open extension control file 
> "/usr/share/postgresql/9.6/extension/oracle_fdw.control":
> No such file or directory
> ft_node=# \conninfo
> You are connected to database "ft_node" as user "postgres" on host
> "localhost" at port "5432".
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
>
> Any suggestions?
>

What steps did you take to install oracle_fdw?

Cheers,

Jeff

Re: [GENERAL] PG and database encryption

[John McKown]

On Tue, Aug 22, 2017 at 3:27 PM, rakeshkumar464
 wrote:
> Thanks John and JD.
>
> John: Are you telling that the backup of a database has no protection?

If you use LUKS to encrypt a filesystem and then copy any file data on
that filesystem to another file on an unencrypted filesystem, then the
copy is not encrypted. You'd need to use something like gpg2 to
encrypt it before storing. The same if you dumped the database using a
utility such as pg_dump. I don't know of a way to encrypt a database
such that it is unencrypted transparently for the PostgreSQL server,
but not for some other application which can access the physical
files. And even if this were possible, the pg_dump would output
unencrypted data. This is basically due to your requirement that the
PostgreSQL client (application) not supply a password to PostgreSQL to
do the decryption. Of course, you could embed the
encryption/decryption into the application itself so that the data is
encrypted before it is passed to PostgreSQL to store. But I think that
violates your original requirements.


-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown


-- 
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] Porting libpq to QNX 4.25

[marcelo]

I'm pretty sure that Watcom 9.6 libraries lacks long long or any such 
variants.

And, of course, I don' t have another tool chain.
Thank you

On 21/08/17 22:20, George Neuner wrote:

On Mon, 21 Aug 2017 13:27:56 -0300, marcelo
 wrote:


Is there a libpq porting to QNX 4.25? I just tried to compile one of the
modules, but was rejected because the QNX's standard library have not an
Int64 type.
TIA

QNX 4.25 is very old (mid 90's) - its toolchain compiler would be C90
unless you've replaced it with something newer.  I'm pretty sure
int64_t was not yet a standard type until C99.

However, many (most?) compilers already supported 64-bit ints as an
extension years before the standard emerged.

You might try "__int64", or "long long" (with or without space).  Or
search the headers for a *_MAX constant equal to 9223372036854775807.
[i.e. (2^63)-1]

George







--
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] [BUGS] Fwd: PostgreSQL 9.4.13 is facing issue in shutting down

[Abhijit Gharami]

The client is a Hot Standby server.

Regards,
Abhijit

On Tue, Aug 22, 2017 at 11:05 AM, Michael Paquier  wrote:

> On Tue, Aug 22, 2017 at 1:58 PM, Abhijit Gharami
>  wrote:
> > Recently we have updated our PostgreSQL version from 9.4.12 to 9.4.13.
> With
> > version 9.4.13 while trying to stop the PostgreSQL server, database is
> > facing issues in shutting down. The problem probably occurs because of
> the
> > WAL sender process not getting terminated on shutdown request.
> > ps output:
> > postgre+  7915  0.0  1.9 589772 39528 ?S<   09:11   0:00
> > //bin/postgres -D /.../postgresql -h 0.0.0.0 -p 5432
> > postgre+  8617  0.0  0.4 590760  8344 ?S postgres:
> > wal sender process replicator xx.xx.xx.xxx(45948) streaming 0/490
>
> One change introduced between 9.4.12 and 9.4.13 is the way WAL senders
> are stopped at shutdown. The checkpointer will first send a signal to
> all the WAL senders after all the normal backends have exited before
> beginning to issue the checkpoint shutdown. This will switch the WAL
> senders to a stopping state where all new queries are blocked, and all
> WAL senders exit after they have sent any remaining WAL to the
> clients, including the checkpoint shutdown record. So, what is the
> client behind this WAL sender? It seems that this WAL sender is
> waiting for some confirmation activity.
> --
> Michael
>

[GENERAL] Deadlocks

[Martin Moore]

Hi, I’m having issues with deadlocks.

v9.6 on Debian Jessie.

I have a number of ‘select’ functions which for logging purposes also call 
another fn that inserts a record into a ‘logging’ table.

However, even with only 1 or 2 users making very infrequent calls to the parent 
fn, deadlocks are occurring. I’m hoping there’s something I’m unaware of as 
this shouldn’t happen!

What could this be? I don’t have any logs at present as I removed the insert 
statement some time ago, but need to get this issue resolved.

Thanks,

Martin.   




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