[pgAdmin4][Patch]: Allow user to provide custom SSL certificates and provide .pgpass file

[Murtuza Zabuawala]

Hi,

PFA patch to allow user to provide custom path for SSL certificates and
also allow user to pass .pgpass file when making server connection.
RM#2649
RM#2650

*SSL certificates options reference:*
https://www.postgresql.org/docs/10/static/libpq-connect.html
https://www.postgresql.org/docs/9.1/static/ssl-tcp.html

*.pgpass file reference:*
https://www.postgresql.org/docs/10/static/libpq-pgpass.html


--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[image: https://community.postgresrocks.net/]

diff --git a/web/migrations/versions/ef590e979b0d_.py 
b/web/migrations/versions/ef590e979b0d_.py
new file mode 100644
index 000..be219c5
--- /dev/null
+++ b/web/migrations/versions/ef590e979b0d_.py
@@ -0,0 +1,46 @@
+
+"""empty message
+
+Revision ID: ef590e979b0d
+Revises: d85a62333272
+Create Date: 2017-08-23 18:37:14.836988
+
+"""
+from alembic import op
+import sqlalchemy as sa
+from pgadmin.model import db
+
+# revision identifiers, used by Alembic.
+revision = 'ef590e979b0d'
+down_revision = 'd85a62333272'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN passfile TEXT'
+)
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN sslcert TEXT'
+)
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN sslkey TEXT'
+)
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN sslrootcert TEXT'
+)
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN sslcrl TEXT'
+)
+db.engine.execute(
+'ALTER TABLE server ADD COLUMN sslcompression '
+'INTEGER default 0'
+)
+db.engine.execute(
+'UPDATE server SET sslcompression=0'
+)
+
+
+def downgrade():
+pass
diff --git a/web/pgadmin/browser/server_groups/servers/__init__.py 
b/web/pgadmin/browser/server_groups/servers/__init__.py
index a74436e..d21a8f6 100644
--- a/web/pgadmin/browser/server_groups/servers/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/__init__.py
@@ -230,7 +230,29 @@ class ServerNode(PGChildNodeView):

'((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$'
 pat4 = re.compile(EXP_IP4)
 pat6 = re.compile(EXP_IP6)
+SSL_MODES = ['require', 'verify-ca', 'verify-full']
 
+def check_ssl_fields(self, data):
+"""
+This function will allow us to check and set defaults for
+SSL fields
+
+Args:
+data: Response data
+
+Returns:
+Flag and Data
+"""
+flag = False
+if 'sslmode' in data and data['sslmode'] in self.SSL_MODES:
+flag = True
+ssl_fields = [
+'sslcert', 'sslkey', 'sslrootcert', 'sslcrl', 'sslcompression'
+]
+for field in ssl_fields:
+if field not in data:
+data[field] = None
+return flag, data
 
 def nodes(self, gid):
 res = []
@@ -381,7 +403,13 @@ class ServerNode(PGChildNodeView):
 'gid': 'servergroup_id',
 'comment': 'comment',
 'role': 'role',
-'db_res': 'db_res'
+'db_res': 'db_res',
+'passfile': 'passfile',
+'sslcert': 'sslcert',
+'sslkey': 'sslkey',
+'sslrootcert': 'sslrootcert',
+'sslcrl': 'sslcrl',
+'sslcompression': 'sslcompression'
 }
 
 disp_lbl = {
@@ -411,8 +439,6 @@ class ServerNode(PGChildNodeView):
 errormsg=gettext('Host address not valid')
 )
 
-
-
 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
 conn = manager.connection()
 connected = conn.connected()
@@ -430,7 +456,12 @@ class ServerNode(PGChildNodeView):
 
 for arg in config_param_map:
 if arg in data:
-setattr(server, config_param_map[arg], data[arg])
+value = data[arg]
+# sqlite3 do not have boolean type so we need to convert
+# it manually to integer
+if arg == 'sslcompression':
+value = 1 if value else 0
+setattr(server, config_param_map[arg], value)
 idx += 1
 
 if idx == 0:
@@ -532,6 +563,8 @@ class ServerNode(PGChildNodeView):
 conn = manager.connection()
 connected = conn.connected()
 
+is_ssl = True if server.ssl_mode in self.SSL_MODES else False
+
 return ajax_response(
 response={
 'id': server.id,
@@ -549,7 +582,14 @@ class ServerNode(PGChildNodeView):
 'version': manager.ver,
 'sslmode': server.ssl_mode,
 'server_type': manager.server_type if connected else 'pg',
-'db_res': server.db_res.split(',') if s

Next release

[Dave Page]

Anyone object to doing a release on 14th September, wrapping the code on
Monday 11th? This seems like the best option for our QA folks who will be
off for EID somewhen in the two weeks before.

Assuming not, should this be 1.7 or 2.0?

If we go with 2.0, it'll be for "safety" given the proposed changes to path
management to allow both server and desktop modes to work out of the box on
Linux.

If we do that, we also need to ensure that any changes to the config
database are backwards compatible, as a 2.0 release would be a side-by-side
installation. Surinder; was it you that had looked into that?

Comments/thoughts?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Next release

[Devrim Gündüz]

Hi,

On Thu, 2017-08-24 at 09:58 +0100, Dave Page wrote:
> Assuming not, should this be 1.7 or 2.0?
> 
> If we go with 2.0, it'll be for "safety" given the proposed changes to path
> management to allow both server and desktop modes to work out of the box on
> Linux.

+1 to 2.0.

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: Next release

[Surinder Kumar]

Hi Dave,

On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:

> Anyone object to doing a release on 14th September, wrapping the code on
> Monday 11th? This seems like the best option for our QA folks who will be
> off for EID somewhen in the two weeks before.
>
> Assuming not, should this be 1.7 or 2.0?
>
> If we go with 2.0, it'll be for "safety" given the proposed changes to
> path management to allow both server and desktop modes to work out of the
> box on Linux.
>
> If we do that, we also need to ensure that any changes to the config
> database are backwards compatible, as a 2.0 release would be a side-by-side
> installation. Surinder; was it you that had looked into that?
>
​I had looked into this and here are my findings:
1. If we are using newer version of pgAdmin and the go back to older
version of pgAdmin, then on running `python pgAdmin4.py`. the
flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
switch back to one migration only when we run  `python pgAdmin4.py`). But
we have multiple database revisions to be migrated. So migration fails here.

2. When Alebmic downgrade is performed by one step, it looks for downgrade
function in that specific database revision, but in our code we didn't
written downgrade function. But if we have written downgrade statement,
still there is an issue:
ie. If we add a new column to a table xyz using ALTER statement like:

​```
​
def upgrade():
​​
verison = get_version()

​​
db.engine.execute(
​​
'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
​ ​
)

def downgrade():
​​
pass
​```​
then on downgrade it executes `downgrade` method, so downgrade should have
code like
`ALTER TABLE server DROP COLUMN hostaddr `
but in sqlite DROP COLUMN statements don't work.
So, this is a an issue with Sqlite database. However, an alternative way is
also given. Here is link



Still, I didn't find any other solution on upgrading/downgrading database
revisions without errors.
It is an issue with Flask-Migrate(Alembic) plugin.


> Comments/thoughts?
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Next release

[Murtuza Zabuawala]

+1 to 2.0

--
Thanks,
Murtuza

On Thu, Aug 24, 2017 at 3:06 PM, Surinder Kumar <
surinder.ku...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:
>
>> Anyone object to doing a release on 14th September, wrapping the code on
>> Monday 11th? This seems like the best option for our QA folks who will be
>> off for EID somewhen in the two weeks before.
>>
>> Assuming not, should this be 1.7 or 2.0?
>>
>> If we go with 2.0, it'll be for "safety" given the proposed changes to
>> path management to allow both server and desktop modes to work out of the
>> box on Linux.
>>
>> If we do that, we also need to ensure that any changes to the config
>> database are backwards compatible, as a 2.0 release would be a side-by-side
>> installation. Surinder; was it you that had looked into that?
>>
> ​I had looked into this and here are my findings:
> 1. If we are using newer version of pgAdmin and the go back to older
> version of pgAdmin, then on running `python pgAdmin4.py`. the
> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
> switch back to one migration only when we run  `python pgAdmin4.py`). But
> we have multiple database revisions to be migrated. So migration fails here.
>
> 2. When Alebmic downgrade is performed by one step, it looks for downgrade
> function in that specific database revision, but in our code we didn't
> written downgrade function. But if we have written downgrade statement,
> still there is an issue:
> ie. If we add a new column to a table xyz using ALTER statement like:
>
> ​```
> ​
> def upgrade():
> ​​
> verison = get_version()
>
> ​​
> db.engine.execute(
> ​​
> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
> ​ ​
> )
>
> def downgrade():
> ​​
> pass
> ​```​
> then on downgrade it executes `downgrade` method, so downgrade should have
> code like
> `ALTER TABLE server DROP COLUMN hostaddr `
> but in sqlite DROP COLUMN statements don't work.
> So, this is a an issue with Sqlite database. However, an alternative way
> is also given. Here is link
> 
>
>
> Still, I didn't find any other solution on upgrading/downgrading database
> revisions without errors.
> It is an issue with Flask-Migrate(Alembic) plugin.
>
>
>> Comments/thoughts?
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>

Re: [pgAdmin4][RM2586] Cleanup feature test

[Harshal Dhumal]

Hi,

Last week Sarah, Matt and I had call regarding some issues with feature
test cases.
For example in auto commit disable test scenario from query tool test cases
by removing code line
*self.page.find_by_id("btn-auto-commit").click() *the test case scenario
was not falling.
This issue was due to previously on going database transaction. This issue
was also with test scenarios like
auto commit enable and auto rollback enabled. The attached patch fixes this
issue.

@Sarah and Matt can you please give this patch a try to check if issues we
discussed last week are fixed.

Thanks,
Harshal




-- 
*Harshal Dhumal*
*Sr. Software Engineer*

EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Aug 11, 2017 at 11:55 AM, Sarah McAlear  wrote:

> Great job on cleaning up the whole feature test suite! This is really
> going to benefit all of the developers a lot.
>
> Thanks,
> Matt & Sarah
>
> On Fri, Aug 11, 2017 at 12:24 PM, Harshal Dhumal <
> harshal.dhu...@enterprisedb.com> wrote:
>
>> Hi,
>>
>> Please find attached updated patch. In this patch I have removed unused
>> imports.
>> Regarding ajax calls on slow network/computer I have already taken care
>> of those.
>> Before sending first version of patch I tested this on Murtuza's machine
>> and also
>> tested on slow machine as well.
>>
>>
>> Thanks,
>> Harshal
>>
>> --
>> *Harshal Dhumal*
>> *Sr. Software Engineer*
>>
>> EnterpriseDB India: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> On Mon, Aug 7, 2017 at 8:55 AM, Sarah McAlear 
>> wrote:
>>
>>> Hi Harshal!
>>>
>>> There are a few files in which there are leftover imports that are not
>>> needed anymore:
>>>
>>>- PGDataypeFeatureTest
>>>- PgadminPage
>>>- CheckForXssFeatureTest
>>>- CheckDebuggerForXssFeatureTest
>>>
>>>
>>> We also noticed that there were quite a few time.sleep functions that
>>> were removed. This seems great overall, but we think that some of them were
>>> in place because of varying network and computer speeds. So for example, in
>>> QueryToolFeatureTest, there is an ajax call that was followed by a
>>> time.sleep to ensure that it had finished executing before continuing
>>> to execute. Removing this may reintroduce some flakiness. If there are no
>>> issues with flakiness after this patch, it seems like a great idea. We ran
>>> the tests a few times and didn't notice any flakiness, but we're unsure if
>>> it will be a problem on a different system.
>>>
>>> Thanks!
>>> Wenlin & Sarah
>>>
>>>
>>> On Wed, Aug 2, 2017 at 9:32 PM, Harshal Dhumal <
>>> harshal.dhu...@enterprisedb.com> wrote:
>>>
 Hi,

 Please find attached patch to improve feature test execution time.
 Now on my machine overall execution time is cut down to 280 seconds
 from 400+ seconds

 Changes:

 1. Removed fixed python time.sleeps where ever possible.
 2. Removed connect to server test cases.
 3. Query tool test cases:
  i. Merged 3 test cases On demand result on scroll, grid select all
 and column select all.
  ii. Merged 3 test cases Explain query, Explain query with verbose
 and Explain query with cost.
  iii. Merged 3 test cases Explain analyze query, Explain
 analyze with buffers and Explain analyze with timing.
 4. Improved debugger XSS test case execution time.

 --
 *Harshal Dhumal*
 *Sr. Software Engineer*

 EnterpriseDB India: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

>>>
>>>
>>
>
diff --git a/web/pgadmin/feature_tests/connect_to_server_feature_test.py b/web/pgadmin/feature_tests/connect_to_server_feature_test.py
deleted file mode 100644
index 97e96f2..000
--- a/web/pgadmin/feature_tests/connect_to_server_feature_test.py
+++ /dev/null
@@ -1,84 +0,0 @@
-##
-#
-# pgAdmin 4 - PostgreSQL Tools
-#
-# Copyright (C) 2013 - 2017, The pgAdmin Development Team
-# This software is released under the PostgreSQL Licence
-#
-##
-
-import time
-from selenium.webdriver import ActionChains
-
-import config as app_config
-from regression.feature_utils.base_feature_test import BaseFeatureTest
-from regression.python_test_utils import test_utils
-
-
-class ConnectsToServerFeatureTest(BaseFeatureTest):
-"""
-Tests that a database connection can be created from the UI
-"""
-scenarios = [
-("Test database connection", dict())
-]
-
-def before(self):
-connection = test_utils.get_db_connection(self.server['db'],
-  self.server['username'],
-  self.server['db_password'],
-  self.server['host'],
-  self.server['port'],
-   

Dried Salted Fish August 2017

[Bonesca - Jona]

    [ View in browser ]( http://r.newsletter.bonescamail.nl/pnktx1lqoatrf.html 
)   

 
This email was sent to pgadmin-hack...@postgresql.org
You received this email because you are registered with Bonesca Import en 
Export BV
 
[ Unsubscribe here ]( http://r.newsletter.bonescamail.nl/pnktx1lqoatrg.html )  

Sent by
[  ]( http://r.newsletter.bonescamail.nl/click/2umbbo6elaoatrd.html )     
© 2017 Bonesca Import en Export BV  

Re: [pgadmin4][Patch] Greenplum specific DDL and Dashboard display

[Dave Cramer]

Can we get some movement on this patch? This seems like a more sane way to
go to support different "products"

Dave Cramer

On 22 August 2017 at 16:56, Dave Cramer  wrote:

>
> On 22 August 2017 at 16:41, Dave Cramer  wrote:
>
>> Ok,
>>
>> Surely this :
>>
>> self.table_template_path = 'table/sql/' + (
>> +'#{0}#{1}#'.format(server_type, ver)
>> +if server_type == 'gpdb' else
>> +'#{0}#'.format(ver)
>> +)
>>
>> could be written in a more readable manner ??
>>
>>
>>
> Apologies, after reading a bit, this is apparently idiomatic python.
>
> Please ignore
>
>
>> Dave Cramer
>>
>> On 22 August 2017 at 14:25, Dave Cramer  wrote:
>>
>>> Hi,
>>>
>>> I've been able to get back to this and test it. So far so good. It
>>> applies more or less cleanly against 1.6 and everything I've tried so far
>>> works
>>>
>>> I'll update more as I test it.
>>>
>>> Thanks
>>>
>>> Dave Cramer
>>>
>>> On 21 August 2017 at 05:29, Teng Zhang  wrote:
>>>
 Hi,

 Thanks for the review, here is a fixed patch working for GBDP which
 shows the appropriate graphs.
 In this fix, we toke out the changes to diver/psycopg2 and
 implemented the greenplum version checking process in the ppas way
 mentioned by Dave Cramer.

 Regards,
 Teng Zhang & Hao Wang

 On Mon, Aug 21, 2017 at 3:55 PM, Ashesh Vashi <
 ashesh.va...@enterprisedb.com> wrote:

> On Mon, Aug 21, 2017 at 1:23 PM, Dave Page  wrote:
>
>> Ashesh, do you have a recommended way to do this?
>>
>> I haven't looked at the patch, but I assume it adds a database driver
>> module for GPDB?
>>
> I have not looked at the patch yet.
> I will take a look at it.
>
> --
>
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise PostgreSQL Company
> 
>
>
> *http://www.linkedin.com/in/asheshvashi*
> 
>
>>
>> On Mon, Aug 21, 2017 at 8:50 AM, Jing Li  wrote:
>>
>>> Hi Dave,
>>>
>>> Since we're hoping to get this change working for GPDB we've
>>> currently using this method to detect if it's gpdb and show the 
>>> appropriate
>>> graphs. Right now it displays errors on the dashboard if it's connected 
>>> to
>>> a gpdb server.
>>> For this patch specifically, the goal is to improve the experience
>>> for greenplum users so they can get the same information as someone
>>> connected to a postgres server.
>>>
>>> I do agree that this is a bigger discussion about how we handle
>>> behavior change overall if it's regular postgres or something else. 
>>> Let's
>>> talk about how we can restructure this behavior in a wider context. Are 
>>> you
>>> open to meeting about it?
>>>
>>> Thanks,
>>> ~Jing
>>>
>>>
>>>
>>> On Fri, Aug 18, 2017 5:37 AM, Dave Cramer davecra...@gmail.com
>>> wrote:
>>>
 Hi Violet.

 I don't really like the way this has been implemented. It adds a
 variable which is only used for gpdb.

 There are other places in the code where the behaviour is changed
 if the server is ppas or regular postgres.

 Candidly I think all of this needs restructuring.

 Dave Cramer

 On 15 August 2017 at 23:29, Violet Cheng  wrote:

 Hi,

 Any comment on this patch? If no, will it be committed soon?

 Thanks,
 Violet

 On Wed, Aug 9, 2017 at 12:05 PM, Sarah McAlear >>> > wrote:

 Hi Hackers!

 This patch enables Greenplum users to see the same charts on the
 dashboard as postgres users. It also adds some additional information 
 to
 the DDL that is Greenplum specific and necessary to create a new table.

 Thanks!
 Sarah





>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>

>>>
>>
>

Re: Next release

[Dave Page]

On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar <
surinder.ku...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:
>
>> Anyone object to doing a release on 14th September, wrapping the code on
>> Monday 11th? This seems like the best option for our QA folks who will be
>> off for EID somewhen in the two weeks before.
>>
>> Assuming not, should this be 1.7 or 2.0?
>>
>> If we go with 2.0, it'll be for "safety" given the proposed changes to
>> path management to allow both server and desktop modes to work out of the
>> box on Linux.
>>
>> If we do that, we also need to ensure that any changes to the config
>> database are backwards compatible, as a 2.0 release would be a side-by-side
>> installation. Surinder; was it you that had looked into that?
>>
> ​I had looked into this and here are my findings:
> 1. If we are using newer version of pgAdmin and the go back to older
> version of pgAdmin, then on running `python pgAdmin4.py`. the
> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
> switch back to one migration only when we run  `python pgAdmin4.py`). But
> we have multiple database revisions to be migrated. So migration fails here.
>
> 2. When Alebmic downgrade is performed by one step, it looks for downgrade
> function in that specific database revision, but in our code we didn't
> written downgrade function. But if we have written downgrade statement,
> still there is an issue:
> ie. If we add a new column to a table xyz using ALTER statement like:
>
> ​```
> ​
> def upgrade():
> ​​
> verison = get_version()
>
> ​​
> db.engine.execute(
> ​​
> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
> ​ ​
> )
>
> def downgrade():
> ​​
> pass
> ​```​
> then on downgrade it executes `downgrade` method, so downgrade should have
> code like
> `ALTER TABLE server DROP COLUMN hostaddr `
> but in sqlite DROP COLUMN statements don't work.
> So, this is a an issue with Sqlite database. However, an alternative way
> is also given. Here is link
> 
>
>
> Still, I didn't find any other solution on upgrading/downgrading database
> revisions without errors.
> It is an issue with Flask-Migrate(Alembic) plugin.
>


Urgh. So I guess the other option is that we version the DB filename as
well. The downside of that is that users will want to migrate their
settings - which may be awkward as we'll have no real way of knowing where
they are.

Thoughts?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Next release

[Harshal Dhumal]

-- 
*Harshal Dhumal*
*Sr. Software Engineer*

EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Aug 24, 2017 at 9:44 PM, Dave Page  wrote:

>
>
> On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar <
> surinder.ku...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:
>>
>>> Anyone object to doing a release on 14th September, wrapping the code on
>>> Monday 11th? This seems like the best option for our QA folks who will be
>>> off for EID somewhen in the two weeks before.
>>>
>>> Assuming not, should this be 1.7 or 2.0?
>>>
>>> If we go with 2.0, it'll be for "safety" given the proposed changes to
>>> path management to allow both server and desktop modes to work out of the
>>> box on Linux.
>>>
>>> If we do that, we also need to ensure that any changes to the config
>>> database are backwards compatible, as a 2.0 release would be a side-by-side
>>> installation. Surinder; was it you that had looked into that?
>>>
>> ​I had looked into this and here are my findings:
>> 1. If we are using newer version of pgAdmin and the go back to older
>> version of pgAdmin, then on running `python pgAdmin4.py`. the
>> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
>> switch back to one migration only when we run  `python pgAdmin4.py`). But
>> we have multiple database revisions to be migrated. So migration fails here.
>>
>> 2. When Alebmic downgrade is performed by one step, it looks for
>> downgrade function in that specific database revision, but in our code we
>> didn't written downgrade function. But if we have written downgrade
>> statement, still there is an issue:
>> ie. If we add a new column to a table xyz using ALTER statement like:
>>
>> ​```
>> ​
>> def upgrade():
>> ​​
>> verison = get_version()
>>
>> ​​
>> db.engine.execute(
>> ​​
>> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
>> ​ ​
>> )
>>
>> def downgrade():
>> ​​
>> pass
>> ​```​
>> then on downgrade it executes `downgrade` method, so downgrade should
>> have code like
>> `ALTER TABLE server DROP COLUMN hostaddr `
>> but in sqlite DROP COLUMN statements don't work.
>> So, this is a an issue with Sqlite database. However, an alternative way
>> is also given. Here is link
>> 
>>
>>
>> Still, I didn't find any other solution on upgrading/downgrading database
>> revisions without errors.
>> It is an issue with Flask-Migrate(Alembic) plugin.
>>
>
>
> Urgh. So I guess the other option is that we version the DB filename as
> well. The downside of that is that users will want to migrate their
> settings - which may be awkward as we'll have no real way of knowing where
> they are.
>
> Thoughts?
>
> Or should we write our own custom backword migrations? For eg. dropping
 column can be achieved by creating another table excluding the columns
which we want to drop then copy data to new table and then drop old table
and rename new table to old name. And also sqlite database schema which we
have in pgAdmin4 is small so writing and maintaining custom migration won
be that hard.

-- 
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Next release

[Dave Page]

On Thu, Aug 24, 2017 at 8:28 PM, Harshal Dhumal <
harshal.dhu...@enterprisedb.com> wrote:

>
>
> --
> *Harshal Dhumal*
> *Sr. Software Engineer*
>
> EnterpriseDB India: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> On Thu, Aug 24, 2017 at 9:44 PM, Dave Page  wrote:
>
>>
>>
>> On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar <
>> surinder.ku...@enterprisedb.com> wrote:
>>
>>> Hi Dave,
>>>
>>> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:
>>>
 Anyone object to doing a release on 14th September, wrapping the code
 on Monday 11th? This seems like the best option for our QA folks who will
 be off for EID somewhen in the two weeks before.

 Assuming not, should this be 1.7 or 2.0?

 If we go with 2.0, it'll be for "safety" given the proposed changes to
 path management to allow both server and desktop modes to work out of the
 box on Linux.

 If we do that, we also need to ensure that any changes to the config
 database are backwards compatible, as a 2.0 release would be a side-by-side
 installation. Surinder; was it you that had looked into that?

>>> ​I had looked into this and here are my findings:
>>> 1. If we are using newer version of pgAdmin and the go back to older
>>> version of pgAdmin, then on running `python pgAdmin4.py`. the
>>> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
>>> switch back to one migration only when we run  `python pgAdmin4.py`). But
>>> we have multiple database revisions to be migrated. So migration fails here.
>>>
>>> 2. When Alebmic downgrade is performed by one step, it looks for
>>> downgrade function in that specific database revision, but in our code we
>>> didn't written downgrade function. But if we have written downgrade
>>> statement, still there is an issue:
>>> ie. If we add a new column to a table xyz using ALTER statement like:
>>>
>>> ​```
>>> ​
>>> def upgrade():
>>> ​​
>>> verison = get_version()
>>>
>>> ​​
>>> db.engine.execute(
>>> ​​
>>> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
>>> ​ ​
>>> )
>>>
>>> def downgrade():
>>> ​​
>>> pass
>>> ​```​
>>> then on downgrade it executes `downgrade` method, so downgrade should
>>> have code like
>>> `ALTER TABLE server DROP COLUMN hostaddr `
>>> but in sqlite DROP COLUMN statements don't work.
>>> So, this is a an issue with Sqlite database. However, an alternative way
>>> is also given. Here is link
>>> 
>>>
>>>
>>> Still, I didn't find any other solution on upgrading/downgrading
>>> database revisions without errors.
>>> It is an issue with Flask-Migrate(Alembic) plugin.
>>>
>>
>>
>> Urgh. So I guess the other option is that we version the DB filename as
>> well. The downside of that is that users will want to migrate their
>> settings - which may be awkward as we'll have no real way of knowing where
>> they are.
>>
>> Thoughts?
>>
>> Or should we write our own custom backword migrations? For eg. dropping
>  column can be achieved by creating another table excluding the columns
> which we want to drop then copy data to new table and then drop old table
> and rename new table to old name. And also sqlite database schema which we
> have in pgAdmin4 is small so writing and maintaining custom migration won
> be that hard.
>

The problem is that we don't want to migrate backwards; we want both
versions to be able to run with the same database (for example, because you
might have multiple versions installed with the EDB PG installer as I do on
my laptop).

Previously, we always made sure our changes were backwards compatible (e.g.
by only adding new columns, never removing or renaming them), and our
home-grown migration code only cared about upgrading the database to the
current version; it wouldn't complain if the database was of a newer
version.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Fwd: [pgadmin4][Patch] Greenplum specific DDL and Dashboard display

[Teng Zhang]

Sure, you can get as much as you like.
Thanks

-- Forwarded message --
From: Dave Cramer 
Date: Thu, Aug 24, 2017 at 8:34 PM
Subject: Re: [pgadmin4][Patch] Greenplum specific DDL and Dashboard display
To: Teng Zhang 
Cc: Ashesh Vashi , pgadmin-hackers <
pgadmin-hack...@postgresql.org>, Dave Page , Jing Li <
jin...@pivotal.io>


Can we get some movement on this patch? This seems like a more sane way to
go to support different "products"

Dave Cramer

On 22 August 2017 at 16:56, Dave Cramer  wrote:

>
> On 22 August 2017 at 16:41, Dave Cramer  wrote:
>
>> Ok,
>>
>> Surely this :
>>
>> self.table_template_path = 'table/sql/' + (
>> +'#{0}#{1}#'.format(server_type, ver)
>> +if server_type == 'gpdb' else
>> +'#{0}#'.format(ver)
>> +)
>>
>> could be written in a more readable manner ??
>>
>>
>>
> Apologies, after reading a bit, this is apparently idiomatic python.
>
> Please ignore
>
>
>> Dave Cramer
>>
>> On 22 August 2017 at 14:25, Dave Cramer  wrote:
>>
>>> Hi,
>>>
>>> I've been able to get back to this and test it. So far so good. It
>>> applies more or less cleanly against 1.6 and everything I've tried so far
>>> works
>>>
>>> I'll update more as I test it.
>>>
>>> Thanks
>>>
>>> Dave Cramer
>>>
>>> On 21 August 2017 at 05:29, Teng Zhang  wrote:
>>>
 Hi,

 Thanks for the review, here is a fixed patch working for GBDP which
 shows the appropriate graphs.
 In this fix, we toke out the changes to diver/psycopg2 and
 implemented the greenplum version checking process in the ppas way
 mentioned by Dave Cramer.

 Regards,
 Teng Zhang & Hao Wang

 On Mon, Aug 21, 2017 at 3:55 PM, Ashesh Vashi <
 ashesh.va...@enterprisedb.com> wrote:

> On Mon, Aug 21, 2017 at 1:23 PM, Dave Page  wrote:
>
>> Ashesh, do you have a recommended way to do this?
>>
>> I haven't looked at the patch, but I assume it adds a database driver
>> module for GPDB?
>>
> I have not looked at the patch yet.
> I will take a look at it.
>
> --
>
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise PostgreSQL Company
> 
>
>
> *http://www.linkedin.com/in/asheshvashi*
> 
>
>>
>> On Mon, Aug 21, 2017 at 8:50 AM, Jing Li  wrote:
>>
>>> Hi Dave,
>>>
>>> Since we're hoping to get this change working for GPDB we've
>>> currently using this method to detect if it's gpdb and show the 
>>> appropriate
>>> graphs. Right now it displays errors on the dashboard if it's connected 
>>> to
>>> a gpdb server.
>>> For this patch specifically, the goal is to improve the experience
>>> for greenplum users so they can get the same information as someone
>>> connected to a postgres server.
>>>
>>> I do agree that this is a bigger discussion about how we handle
>>> behavior change overall if it's regular postgres or something else. 
>>> Let's
>>> talk about how we can restructure this behavior in a wider context. Are 
>>> you
>>> open to meeting about it?
>>>
>>> Thanks,
>>> ~Jing
>>>
>>>
>>>
>>> On Fri, Aug 18, 2017 5:37 AM, Dave Cramer davecra...@gmail.com
>>> wrote:
>>>
 Hi Violet.

 I don't really like the way this has been implemented. It adds a
 variable which is only used for gpdb.

 There are other places in the code where the behaviour is changed
 if the server is ppas or regular postgres.

 Candidly I think all of this needs restructuring.

 Dave Cramer

 On 15 August 2017 at 23:29, Violet Cheng  wrote:

 Hi,

 Any comment on this patch? If no, will it be committed soon?

 Thanks,
 Violet

 On Wed, Aug 9, 2017 at 12:05 PM, Sarah McAlear >>> > wrote:

 Hi Hackers!

 This patch enables Greenplum users to see the same charts on the
 dashboard as postgres users. It also adds some additional information 
 to
 the DDL that is Greenplum specific and necessary to create a new table.

 Thanks!
 Sarah





>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>

>>>
>>
>

Re: pgAdmin4: Random failure of FTS test cases due to improper random string creation

[Navnath Gadakh]

Hi Dave,

  Please find the attached patch. The code added to tear down the FTS
related objects. As this issue was random, I have tested this patch on all
12 servers (pg/ppas) with multiple time and got no errors.

Thanks.


On Fri, Aug 18, 2017 at 1:35 PM, Dave Page 
wrote:

>
>
> On Fri, Aug 18, 2017 at 4:54 AM, Ashesh Vashi <
> ashesh.va...@enterprisedb.com> wrote:
>
>> On Fri, Aug 11, 2017 at 1:24 PM, Navnath Gadakh <
>> navnath.gad...@enterprisedb.com> wrote:
>>
>>> Hi Dave,
>>>
>>> Please find the attached patch for UUID creation issues with
>>> test objects for FTS configurations, FTS dictionaries and FTS parsers.
>>> Previously(refer email with subject "Build failed in Jenkins:
>>> pgadmin4-master-python27 #279" and "Build failed in Jenkins:
>>> pgadmin4-master-python33 #207"), test cases were randomly failing due to
>>> repetitions of the test object names.
>>>
>>> In the old code we used some part of the string for creating a UUID
>>> string, but it seems that at some point that created string gets repeated
>>> and due to which test cases were failing as this was a random behavior, now
>>> it is fixed.
>>>
>> Navnath,
>>
>> We're still not removing the temporary objects, created by test-cases, in
>> the tear-down function.
>> We should have removed them in the tear-down function to fix the issue in
>> proper way.
>>
>> Dave - thoughts?
>>
>
> Right - it only seems to be FTS and FDW related objects that suffer from
> this problem from what I can see, so I assume we're getting it right for
> everything else by removing objects in the tear-down.
>
> --
> Dave Page
> VP, Chief Architect, Tools & Installers
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>



-- 
Regards,
Navnath Gadakh

EnterpriseDB Corporation
The Enterprise PostgreSQL Company


fts_teardown_issue_v2.patch
Description: Binary data

Re: Next release

[Surinder Kumar]

Hi
On Fri, Aug 25, 2017 at 1:03 AM, Dave Page  wrote:

>
>
> On Thu, Aug 24, 2017 at 8:28 PM, Harshal Dhumal <
> harshal.dhu...@enterprisedb.com> wrote:
>
>>
>>
>> --
>> *Harshal Dhumal*
>> *Sr. Software Engineer*
>>
>> EnterpriseDB India: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> On Thu, Aug 24, 2017 at 9:44 PM, Dave Page  wrote:
>>
>>>
>>>
>>> On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar <
>>> surinder.ku...@enterprisedb.com> wrote:
>>>
 Hi Dave,

 On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:

> Anyone object to doing a release on 14th September, wrapping the code
> on Monday 11th? This seems like the best option for our QA folks who will
> be off for EID somewhen in the two weeks before.
>
> Assuming not, should this be 1.7 or 2.0?
>
> If we go with 2.0, it'll be for "safety" given the proposed changes to
> path management to allow both server and desktop modes to work out of the
> box on Linux.
>
> If we do that, we also need to ensure that any changes to the config
> database are backwards compatible, as a 2.0 release would be a 
> side-by-side
> installation. Surinder; was it you that had looked into that?
>
 ​I had looked into this and here are my findings:
 1. If we are using newer version of pgAdmin and the go back to older
 version of pgAdmin, then on running `python pgAdmin4.py`. the
 flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can
 switch back to one migration only when we run  `python pgAdmin4.py`). But
 we have multiple database revisions to be migrated. So migration fails 
 here.

 2. When Alebmic downgrade is performed by one step, it looks for
 downgrade function in that specific database revision, but in our code we
 didn't written downgrade function. But if we have written downgrade
 statement, still there is an issue:
 ie. If we add a new column to a table xyz using ALTER statement like:

 ​```
 ​
 def upgrade():
 ​​
 verison = get_version()

 ​​
 db.engine.execute(
 ​​
 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
 ​ ​
 )

 def downgrade():
 ​​
 pass
 ​```​
 then on downgrade it executes `downgrade` method, so downgrade should
 have code like
 `ALTER TABLE server DROP COLUMN hostaddr `
 but in sqlite DROP COLUMN statements don't work.
 So, this is a an issue with Sqlite database. However, an alternative
 way is also given. Here is link
 


 Still, I didn't find any other solution on upgrading/downgrading
 database revisions without errors.
 It is an issue with Flask-Migrate(Alembic) plugin.

>>>
>>>
>>> Urgh. So I guess the other option is that we version the DB filename as
>>> well. The downside of that is that users will want to migrate their
>>> settings - which may be awkward as we'll have no real way of knowing where
>>> they are.
>>>
>>> Thoughts?
>>>
>>> Or should we write our own custom backword migrations? For eg. dropping
>>  column can be achieved by creating another table excluding the columns
>> which we want to drop then copy data to new table and then drop old table
>> and rename new table to old name. And also sqlite database schema which we
>> have in pgAdmin4 is small so writing and maintaining custom migration won
>> be that hard.
>>
>
> The problem is that we don't want to migrate backwards; we want both
> versions to be able to run with the same database (for example, because you
> might have multiple versions installed with the EDB PG installer as I do on
> my laptop).
>
> Previously, we always made sure our changes were backwards compatible
> (e.g. by only adding new columns, never removing or renaming them), and our
> home-grown migration code only cared about upgrading the database to the
> current version; it wouldn't complain if the database was of a newer
> version.
>
​The code which is responsible to run database migration is
`db_upgrade(app)` in `pgadmin/__init__.py​` it executes when python server
runs `python pgAdmin4.py`, It fails with older version of pgAdmin4(say 1.5)
because it cannot find db revision file (revision id stored in table
'alembic_version') in `web/migrations` folder of latest pgAdmin4-1.5

But If we catch this exception like:
```
import alembic
try:
db_upgrade(app)
except alembic.util.exc.CommandError as e: # Handle migration error, I
expect this exception will be raised in older version of code.
app.logger.info('Failed to run migrations: %s' % str(e))
```

It will fail to run migrations but exception will be handled and python app
server will be started successfully and pgAdmin4 will run with newer
database.
Or, we should check whether the migration which is about to run against the
revisi

Re: Next release

[Ashesh Vashi]

On Fri, Aug 25, 2017 at 12:16 PM, Surinder Kumar <
surinder.ku...@enterprisedb.com> wrote:

> Hi
> On Fri, Aug 25, 2017 at 1:03 AM, Dave Page  wrote:
>
>>
>>
>> On Thu, Aug 24, 2017 at 8:28 PM, Harshal Dhumal <
>> harshal.dhu...@enterprisedb.com> wrote:
>>
>>>
>>>
>>> --
>>> *Harshal Dhumal*
>>> *Sr. Software Engineer*
>>>
>>> EnterpriseDB India: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>> On Thu, Aug 24, 2017 at 9:44 PM, Dave Page  wrote:
>>>


 On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar <
 surinder.ku...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page  wrote:
>
>> Anyone object to doing a release on 14th September, wrapping the code
>> on Monday 11th? This seems like the best option for our QA folks who will
>> be off for EID somewhen in the two weeks before.
>>
>> Assuming not, should this be 1.7 or 2.0?
>>
>> If we go with 2.0, it'll be for "safety" given the proposed changes
>> to path management to allow both server and desktop modes to work out of
>> the box on Linux.
>>
>> If we do that, we also need to ensure that any changes to the config
>> database are backwards compatible, as a 2.0 release would be a 
>> side-by-side
>> installation. Surinder; was it you that had looked into that?
>>
> ​I had looked into this and here are my findings:
> 1. If we are using newer version of pgAdmin and the go back to older
> version of pgAdmin, then on running `python pgAdmin4.py`. the
> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it 
> can
> switch back to one migration only when we run  `python pgAdmin4.py`). But
> we have multiple database revisions to be migrated. So migration fails 
> here.
>
> 2. When Alebmic downgrade is performed by one step, it looks for
> downgrade function in that specific database revision, but in our code we
> didn't written downgrade function. But if we have written downgrade
> statement, still there is an issue:
> ie. If we add a new column to a table xyz using ALTER statement like:
>
> ​```
> ​
> def upgrade():
> ​​
> verison = get_version()
>
> ​​
> db.engine.execute(
> ​​
> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)'
> ​ ​
> )
>
> def downgrade():
> ​​
> pass
> ​```​
> then on downgrade it executes `downgrade` method, so downgrade should
> have code like
> `ALTER TABLE server DROP COLUMN hostaddr `
> but in sqlite DROP COLUMN statements don't work.
> So, this is a an issue with Sqlite database. However, an alternative
> way is also given. Here is link
> 
>
>
> Still, I didn't find any other solution on upgrading/downgrading
> database revisions without errors.
> It is an issue with Flask-Migrate(Alembic) plugin.
>


 Urgh. So I guess the other option is that we version the DB filename as
 well. The downside of that is that users will want to migrate their
 settings - which may be awkward as we'll have no real way of knowing where
 they are.

 Thoughts?

 Or should we write our own custom backword migrations? For eg. dropping
>>>  column can be achieved by creating another table excluding the columns
>>> which we want to drop then copy data to new table and then drop old table
>>> and rename new table to old name. And also sqlite database schema which we
>>> have in pgAdmin4 is small so writing and maintaining custom migration won
>>> be that hard.
>>>
>>
>> The problem is that we don't want to migrate backwards; we want both
>> versions to be able to run with the same database (for example, because you
>> might have multiple versions installed with the EDB PG installer as I do on
>> my laptop).
>>
>> Previously, we always made sure our changes were backwards compatible
>> (e.g. by only adding new columns, never removing or renaming them), and our
>> home-grown migration code only cared about upgrading the database to the
>> current version; it wouldn't complain if the database was of a newer
>> version.
>>
> ​The code which is responsible to run database migration is
> `db_upgrade(app)` in `pgadmin/__init__.py​` it executes when python server
> runs `python pgAdmin4.py`, It fails with older version of pgAdmin4(say 1.5)
> because it cannot find db revision file (revision id stored in table
> 'alembic_version') in `web/migrations` folder of latest pgAdmin4-1.5
>
> But If we catch this exception like:
> ```
> import alembic
> try:
> db_upgrade(app)
> except alembic.util.exc.CommandError as e: # Handle migration error, I
> expect this exception will be raised in older version of code.
> app.logger.info('Failed to run migrations: %s' % str(e))