Subject: RM1849: Auto-generating security keys

From: Dave Page <[email-masked]>
Date: Tue, 11 Oct 2016 16:40:30 +0100

Hi Ashesh,

Can you please review the attached patch, and apply if you're happy with it?

The purpose is to auto-generate the various security keys that are
currently in the configuration file, and store them in the SQLite database.
This allows us to remove the checks for config_local.py and the hard-coded
default keys which are causing some problems with packaging:

- Hard coded defaults are fine for Desktop mode, and packages generally aim
to make that work primarily.
- Hard coded defaults are a security risk for Server mode, hence we
currently require the user to manually setup keys, which is currently being
overridden by packagers for Desktop mode.

This change ensures that we have unique security keys for every
installation, whether running in desktop or server mode (generated from
os.urandom).

Thanks!

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/web/config.py b/web/config.py
index 20714f9..8411d79 100644
--- a/web/config.py
+++ b/web/config.py
@@ -140,21 +140,13 @@ DEFAULT_SERVER_PORT = 5050
 # Enable CSRF protection?
 CSRF_ENABLED = True
 
-# Secret key for signing CSRF data. Override this in config_local.py if
-# running on a web server
-CSRF_SESSION_KEY = 'SuperSecret1'
-
-# Secret key for signing cookies. Override this in config_local.py if
-# running on a web server
-SECRET_KEY = 'SuperSecret2'
-
-# Salt used when hashing passwords. Override this in config_local.py if
-# running on a web server
-SECURITY_PASSWORD_SALT = 'SuperSecret3'
-
 # Hashing algorithm used for password storage
 SECURITY_PASSWORD_HASH = 'pbkdf2_sha512'
 
+# NOTE: CSRF_SESSION_KEY, SECRET_KEY and SECURITY_PASSWORD_SALT are no
+#       longer part of the main configuration, but are stored in the
+#       configuration databases 'keys' table and are auto-generated.
+
 # Should HTML be minified on the fly when not in debug mode?
 # Note: This is disabled by default as it will error when processing the
 #       docs. If the serving of docs is handled by an Apache HTTPD
diff --git a/web/pgAdmin4.py b/web/pgAdmin4.py
index 1fb34f9..f894f8b 100644
--- a/web/pgAdmin4.py
+++ b/web/pgAdmin4.py
@@ -32,18 +32,6 @@ config.SETTINGS_SCHEMA_VERSION = SCHEMA_VERSION
 # Sanity checks
 ##########################################################################
 
-# Check for local settings if running in server mode
-if config.SERVER_MODE is True:
-    local_config = os.path.join(os.path.dirname(os.path.realpath(__file__)),
-                                'config_local.py')
-    if not os.path.isfile(local_config):
-        print("The configuration file %s does not exist.\n" % local_config)
-        print("Before running this application, ensure that config_local.py has been created")
-        print("and sets values for SECRET_KEY, SECURITY_PASSWORD_SALT and CSRF_SESSION_KEY")
-        print("at bare minimum. See config.py for more information and a complete list of")
-        print("settings. Exiting...")
-        sys.exit(1)
-
 # Check if the database exists. If it does not, create it.
 if not os.path.isfile(config.SQLITE_PATH):
     setupfile = os.path.join(os.path.dirname(os.path.realpath(__file__)),
diff --git a/web/pgadmin/__init__.py b/web/pgadmin/__init__.py
index d988172..21fe636 100644
--- a/web/pgadmin/__init__.py
+++ b/web/pgadmin/__init__.py
@@ -26,7 +26,7 @@ from pgadmin.utils.session import create_session_interface
 from werkzeug.local import LocalProxy
 from werkzeug.utils import find_modules
 
-from pgadmin.model import db, Role, Server, ServerGroup, User, Version
+from pgadmin.model import db, Role, Server, ServerGroup, User, Version, Keys
 # Configuration settings
 import config
 
@@ -127,11 +127,6 @@ def create_app(app_name=config.APP_NAME):
     app.config.update(dict(PROPAGATE_EXCEPTIONS=True))
 
     ##########################################################################
-    # Setup session management
-    ##########################################################################
-    app.session_interface = create_session_interface(app)
-
-    ##########################################################################
     # Setup logging and log the application startup
     ##########################################################################
 
@@ -222,7 +217,19 @@ def create_app(app_name=config.APP_NAME):
             from setup import do_upgrade
             do_upgrade(app, user_datastore, security, version)
 
+    ##########################################################################
+    # Setup security
+    ##########################################################################
+    with app.app_context():
+        config.CSRF_SESSION_KEY = Keys.query.filter_by(name = 'CSRF_SESSION_KEY').first().value
+        config.SECRET_KEY = Keys.query.filter_by(name = 'SECRET_KEY').first().value
+        config.SECURITY_PASSWORD_SALT = Keys.query.filter_by(name = 'SECURITY_PASSWORD_SALT').first().value
+
+    app.session_interface = create_session_interface(app)
+
+    ##########################################################################
     # Load all available server drivers
+    ##########################################################################
     driver.init_app(app)
 
     ##########################################################################
diff --git a/web/pgadmin/model/__init__.py b/web/pgadmin/model/__init__.py
index 019e9b1..9727d2b 100644
--- a/web/pgadmin/model/__init__.py
+++ b/web/pgadmin/model/__init__.py
@@ -29,7 +29,7 @@ from flask_sqlalchemy import SQLAlchemy
 #
 ##########################################################################
 
-SCHEMA_VERSION = 13
+SCHEMA_VERSION = 14
 
 ##########################################################################
 #
@@ -207,3 +207,10 @@ class Process(db.Model):
     end_time = db.Column(db.String(), nullable=True)
     exit_code = db.Column(db.Integer(), nullable=True)
     acknowledge = db.Column(db.String(), nullable=True)
+
+
+class Keys(db.Model):
+    """Define the keys table."""
+    __tablename__ = 'keys'
+    name = db.Column(db.String(), nullable=False, primary_key=True)
+    value = db.Column(db.String(), nullable=False)
\ No newline at end of file
diff --git a/web/setup.py b/web/setup.py
index 64273fb..90697fa 100755
--- a/web/setup.py
+++ b/web/setup.py
@@ -10,6 +10,7 @@
 """Perform the initial setup of the application, by creating the auth
 and settings database."""
 
+import base64
 import getpass
 import os
 import random
@@ -22,7 +23,7 @@ from flask_security import Security, SQLAlchemyUserDatastore
 from flask_security.utils import encrypt_password
 
 from pgadmin.model import db, Role, User, Server, \
-    ServerGroup, Version
+    ServerGroup, Version, Keys
 # Configuration settings
 import config
 
@@ -40,6 +41,16 @@ if hasattr(__builtins__, 'raw_input'):
 
 def do_setup(app):
     """Create a new settings database from scratch"""
+
+    # Get some defaults for the various keys
+    with app.app_context():
+        config.CSRF_SESSION_KEY = base64.urlsafe_b64encode(os.urandom(32))
+        app.jinja_env.globals['config']['CSRF_SESSION_KEY'] = config.CSRF_SESSION_KEY
+        config.SECRET_KEY = base64.urlsafe_b64encode(os.urandom(32))
+        app.jinja_env.globals['config']['SECRET_KEY'] = config.SECRET_KEY
+        config.SECURITY_PASSWORD_SALT = base64.urlsafe_b64encode(os.urandom(32))
+        app.jinja_env.globals['config']['SECURITY_PASSWORD_SALT'] = config.SECURITY_PASSWORD_SALT
+
     if config.SERVER_MODE is False:
         print("NOTE: Configuring authentication for DESKTOP mode.")
         email = config.DESKTOP_USER
@@ -116,6 +127,17 @@ def do_setup(app):
             name='ConfigDB', value=config.SETTINGS_SCHEMA_VERSION
         )
         db.session.merge(version)
+        db.session.commit()
+
+        # Create the keys
+        key = Keys(name='CSRF_SESSION_KEY', value=config.CSRF_SESSION_KEY)
+        db.session.merge(key)
+
+        key = Keys(name='SECRET_KEY', value=config.SECRET_KEY)
+        db.session.merge(key)
+
+        key = Keys(name='SECURITY_PASSWORD_SALT', value=config.SECURITY_PASSWORD_SALT)
+        db.session.merge(key)
 
         db.session.commit()
 
@@ -329,6 +351,23 @@ ALTER TABLE SERVER
     ADD COLUMN discovery_id TEXT
     """)
 
+        if int(version.value) < 14:
+            db.engine.execute("""
+CREATE TABLE keys (
+    name TEST NOT NULL,
+    value TEXT NOT NULL,
+    PRIMARY KEY (name))
+                """)
+
+            sql = "INSERT INTO keys (name, value) VALUES ('CSRF_SESSION_KEY', '%s')" % base64.urlsafe_b64encode(os.urandom(32))
+            db.engine.execute(sql)
+
+            sql = "INSERT INTO keys (name, value) VALUES ('SECRET_KEY', '%s')" % base64.urlsafe_b64encode(os.urandom(32))
+            db.engine.execute(sql)
+
+            sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', '%s')" % base64.urlsafe_b64encode(os.urandom(32))
+            db.engine.execute(sql)
+
     # Finally, update the schema version
     version.value = config.SETTINGS_SCHEMA_VERSION
     db.session.merge(version)
@@ -364,15 +403,6 @@ if __name__ == '__main__':
         'config_local.py'
     )
 
-    if not os.path.isfile(local_config):
-        print("""
- The configuration file - {0} does not exist.
- Before running this application, ensure that config_local.py has been created
- and sets values for SECRET_KEY, SECURITY_PASSWORD_SALT and CSRF_SESSION_KEY
- at bare minimum. See config.py for more information and a complete list of
- settings. Exiting...""".format(local_config))
-        sys.exit(1)
-
     # Check if the database exists. If it does, tell the user and exit.
     if os.path.isfile(config.SQLITE_PATH):
         print("""

-- 
Sent via pgadmin-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

---

From: Ashesh Vashi <[email-masked]>
Date: Thu, 13 Oct 2016 19:46:56 +0530

Hi Dave,

> Can you please review the attached patch, and apply if you're happy with
Overall the patch looked good to me.
But - I encounter an issue in 'web' mode, which wont happen with 'runtime'.

Steps for reproduction on existing pgAdmin 4 environment with 'web' mode.
- Apply the patch
- Start the pgAdmin4 application (stand alone application).
- Open pgAdmin home page.
- Log out (if already login).

And, you will see an exception.

I have figure out the issue with the patch.
We were setting the SECURITY_PASSWORD_SALT, after initializing the Security
object.
Hence - it could not set the SECURITY_KEY, and SECURITY_PASSWORD_SALT
properly.

I had moved the Security object initialization after fetching these
configurations from the database.
I have attached a addon patch for the same.

Now - I run into another issue.
Because - the existing password was hashed using the old
SECURITY_PASSWORD_SALT, I am no more able to login to pgAdmin 4.

I think - we need to think about different strategy for upgrading the
configuration file in the 'web' mode.
I was thinking - we can store the existing security configurations in the
database during upgrade process in 'web' mode.

I was not able to spend much time on it due to some other priorities.

--
Thanks & Regards,
Ashesh Vashi

> - Hard coded defaults are fine for Desktop mode, and packages generally
> aim to make that work primarily.

---

From: Dave Page <[email-masked]>
Date: Fri, 14 Oct 2016 18:27:19 +0100

Hi

On Thursday, October 13, 2016, Ashesh Vashi <[email-masked]>
wrote:

> Hi Dave,
>
> On Tue, Oct 11, 2016 at 9:10 PM, Dave Page <[email-masked]
> <javascript:_e(%7B%7D,'cvml',[email-masked]');>> wrote:
>
>> Hi Ashesh,
>>
> We were setting the SECURITY_PASSWORD_SALT, after initializing the
> Security object.

Hmm.

> I have attached a addon patch for the same.

OK, thanks.

> database during upgrade process in 'web' mode.

My concern with that is that we'll likely be storing the default config
values in many cases, thus for those users, perpetuating the problem.

I guess what we need to do is re-encrypt the password during the upgrade -
however, that makes me think; we then have both the key and the encrypted
passwords in the same database which is clearly not a good idea. Sigh...
Needs more thought.

---

From: Dave Page <[email-masked]>
Date: Sat, 15 Oct 2016 03:32:22 +0100

Hi

On Friday, October 14, 2016, Dave Page <[email-masked]> wrote:

> Hi
>
> On Thursday, October 13, 2016, Ashesh Vashi <[email-masked]
> <javascript:_e(%7B%7D,'cvml',[email-masked]');>> wrote:
>
>> Hi Dave,
>>

OK, so I've been thinking about this and experimenting for a couple of
hours, as well as annoying the crap out of Magnus by thinking out loud in
his general direction, and it looks like this isn't a major problem as from
what I can see,  SECURITY_PASSWORD_SALT is (aside from really being a key
not a salt) not the only salting that's done.

It looks like it's used system-wide as the key to generate an HMAC of the
users password, which is then passed to passlib which salts and hashes it.
I did some testing, and found that two users with the same password end up
with different hashes in the database, so clearly there is also per-user
salting happening. I also created two users, then dropped the database and
created the same user accounts with the same passwords again, and found
that the resulting hashes were different in both databases - thus there is
something else ensuring the hashes are unique across different
installations/databases.

So, I believe we can do as you suggest and migrate existing values for
SECURITY_PASSWORD_SALT, given that there's clearly some other per user and
per installation/database salting going on anyway. New installations can
have the random value for SECURITY_PASSWORD_SALT.

I don't believe SECURITY_KEY and CSRF_SESSION_KEY are issues either, as
they're used for purposes that are essentially ephemeral, and thus can be
changed during an upgrade.

Adding Magnus as I'd appreciate any thoughts he may have.

Patch attached - please review (Ashesh, but others too would be
appreciated)!

Thanks.

 

index d988172..79fa1c6 100644

 
 
@@ -206,7 +201,7 @@ def create_app(app_name=config.APP_NAME):
 
     # Setup Flask-Security
     user_datastore = SQLAlchemyUserDatastore(db, User, Role)
-    security = Security(app, user_datastore)
+    security = Security(None, user_datastore)
 
     # Upgrade the schema (if required)
     with app.app_context():
@@ -220,9 +215,29 @@ def create_app(app_name=config.APP_NAME):
                 )
             )
             from setup import do_upgrade
-            do_upgrade(app, user_datastore, security, version)
+            do_upgrade(app, user_datastore, version)
+
+    ##########################################################################
+    # Setup security
+    ##########################################################################
+    with app.app_context():
+        config.CSRF_SESSION_KEY = Keys.query.filter_by(name = 'CSRF_SESSION_KEY').first().value
+        config.SECRET_KEY = Keys.query.filter_by(name = 'SECRET_KEY').first().value
+        config.SECURITY_PASSWORD_SALT = Keys.query.filter_by(name = 'SECURITY_PASSWORD_SALT').first().value
+
+    # Update the app.config with proper security keyes for signing CSRF data,
+    # signing cookies, and the SALT for hashing the passwords.
+    app.config.update(dict(CSRF_SESSION_KEY=config.CSRF_SESSION_KEY))
+    app.config.update(dict(SECRET_KEY=config.SECRET_KEY))
+    app.config.update(dict(SECURITY_PASSWORD_SALT=config.SECURITY_PASSWORD_SALT))
 
+    security.init_app(app)
+

-SCHEMA_VERSION = 13
+SCHEMA_VERSION = 14
 
index 64273fb..441a0f3 100755
 
+import base64
 import getpass
 import os
 import random

@@ -40,6 +41,7 @@ if hasattr(__builtins__, 'raw_input'):
 
 def do_setup(app):
@@ -116,6 +118,17 @@ def do_setup(app):
 
         db.session.commit()
 
@@ -128,7 +141,7 @@ def do_setup(app):
     )

-def do_upgrade(app, datastore, security, version):
+def do_upgrade(app, datastore, version):
     """Upgrade an existing settings database"""
     #######################################################################
     # Run whatever is required to update the database schema to the current
@@ -329,6 +342,29 @@ ALTER TABLE SERVER
     ADD COLUMN discovery_id TEXT
     """)
 
+        if int(version.value) < 14:
+            db.engine.execute("""
+CREATE TABLE keys (
+    name TEST NOT NULL,
+    value TEXT NOT NULL,
+    PRIMARY KEY (name))
+                """)
+
+            sql = "INSERT INTO keys (name, value) VALUES ('CSRF_SESSION_KEY', '%s')" % base64.urlsafe_b64encode(os.urandom(32))
+            db.engine.execute(sql)
+
+            sql = "INSERT INTO keys (name, value) VALUES ('SECRET_KEY', '%s')" % base64.urlsafe_b64encode(os.urandom(32))
+            db.engine.execute(sql)
+
+            # If SECURITY_PASSWORD_SALT is not in the config, but we're upgrading, then it must (unless the
+            # user edited the main config - which they shouldn't have done) have been at it's default
+            # value, so we'll use that. Otherwise, use whatever we can find in the config.
+            if hasattr(config, 'SECURITY_PASSWORD_SALT'):
+                sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', '%s')" % config.SECURITY_PASSWORD_SALT
+            else:
+                sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', 'SuperSecret3')"
+            db.engine.execute(sql)
+
     # Finally, update the schema version
     version.value = config.SETTINGS_SCHEMA_VERSION
     db.session.merge(version)
@@ -347,6 +383,12 @@ ALTER TABLE SERVER
 ###############################################################################
 if __name__ == '__main__':
     app = Flask(__name__)
+
+    # Get some defaults for the various keys
+    config.CSRF_SESSION_KEY = base64.urlsafe_b64encode(os.urandom(32))
+    config.SECRET_KEY = base64.urlsafe_b64encode(os.urandom(32))
+    config.SECURITY_PASSWORD_SALT = base64.urlsafe_b64encode(os.urandom(32))
+
     app.config.from_object(config)
 
     if config.TESTING_MODE:
@@ -364,15 +406,6 @@ if __name__ == '__main__':
         'config_local.py'
     )
 
@@ -381,7 +414,6 @@ Entering upgrade mode...""" % config.SQLITE_PATH)
 
         # Setup Flask-Security
         user_datastore = SQLAlchemyUserDatastore(db, User, Role)
-        security = Security(app, user_datastore)
 
         # Always use "< REQUIRED_VERSION" as the test for readability
         with app.app_context():
@@ -403,7 +435,7 @@ Exiting...""" % (version.value))
             print("NOTE: Upgrading database schema from version %d to %d." % (
                 version.value, config.SETTINGS_SCHEMA_VERSION
             ))
-            do_upgrade(app, user_datastore, security, version)
+            do_upgrade(app, user_datastore, version)
     else:
         directory = os.path.dirname(config.SQLITE_PATH)
         if not os.path.exists(directory):

---

From: Ashesh Vashi <[email-masked]>
Date: Wed, 19 Oct 2016 11:16:26 +0530

Hi Dave,

On Sat, Oct 15, 2016 at 8:02 AM, Dave Page <[email-masked]> wrote:

>> On Thursday, October 13, 2016, Ashesh Vashi <
>> [email-masked]> wrote:
>>
>>> Hi Dave,
>>>
>>> I was thinking - we can store the existing security configurations in
>>> the database during upgrade process in 'web' mode.
>>>
>>
>> I guess what we need to do is re-encrypt the password during the upgrade
>> - however, that makes me think; we then have both the key and the encrypted
We do not need to generate the random SECURITY_PASSWORD_SALT during upgrade
mode, which was wrong added in my addon patch.

Please find the updated patch.

Otherwise - looks good to me.
Please commit the new patch (if you're ok with the change).

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com/>

*http://www.linkedin.com/in/asheshvashi*
<http://www.linkedin.com/in/asheshvashi>

---

From: Dave Page <[email-masked]>
Date: Wed, 19 Oct 2016 09:27:15 +0100

Patch applied.

Fahar, can you please test this thoroughly in desktop and server modes,
with both fresh and upgraded installations?

https://redmine.postgresql.org/issues/1849

Packagers: This change means that packages are no longer forced to create a
config_local.py file, and there is no longer any need to explicitly set
SECURITY_PASSWORD_SALT, SECURITY_KEY and CSRF_SESSION_KEY in the config (in
fact, they should be removed for new installations, if you have included
them in 1.0)

Thanks.

On Wed, Oct 19, 2016 at 6:46 AM, Ashesh Vashi <[email-masked]

> We do not need to generate the random SECURITY_PASSWORD_SALT during
> upgrade mode, which was wrong added in my addon patch.
>

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 13:37:49 +0500

Sure,

Will test this thoroughly after complete investigation.

Kind Regards,

On Wed, Oct 19, 2016 at 1:27 PM, Dave Page <[email-masked]> wrote:

> Packagers: This change means that packages are no longer forced to create
> a config_local.py file, and there is no longer any need to explicitly set
> SECURITY_PASSWORD_SALT, SECURITY_KEY and CSRF_SESSION_KEY in the config
> (in fact, they should be removed for new installations, if you have
> included them in 1.0)
>
> Thanks.
>
>
> On Wed, Oct 19, 2016 at 6:46 AM, Ashesh Vashi <
>>>> I guess what we need to do is re-encrypt the password during the
>>>> upgrade - however, that makes me think; we then have both the key and the
>>>> encrypted passwords in the same database which is clearly not a good idea.
>>>> Sigh... Needs more thought.
>>>>
>>>
>>> It looks like it's used system-wide as the key to generate an HMAC of
>>> the users password, which is then passed to passlib which salts and hashes
>>> it. I did some testing, and found that two users with the same password end
>>> up with different hashes in the database, so clearly there is also per-user

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation
Phone Office: +92-51-835-8874
Phone Direct: +92-51-8466803
Mobile: +92-333-5409707
Skype ID: syed.fahar.abbas
Website: www.enterprisedb.com

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 15:03:20 +0500

Dave,

Testing Environment

Ubuntu 16.04 Linux 64:
--------------------------------

pg-AdminIV Development Environment Setup for Ubuntu  :

1) Install GIT

sudo apt-get install git

2) Install pip3

sudo apt-get install python3-pip

3) Install virtualenv

sudo pip3 install virtualenv

4) install below dependency as it is required for psycopg2 & pycrypto module

sudo apt-get install libpq-dev

sudo apt-get install python3-dev

5) Create virtual environment

virtualenv -p python3 venv

6) Create mkdir Projects

7) Clone git repo in Projects

git clone http://git.postgresql.org/git/pgadmin4.git

8) activate virtual environment

source venv/bin/activate

9) Install modules

pip3 install -r requirements_py3.txt

*10) Edit the config.py file to config_local.py  resides in
Projects\pgAdmin4\web *

11)Now run setup.py file  (\Projects\pgAdmin4\web)
    python setup.py

If user does not create config_local.py and do Python setup.py for new
Development then SECURITY_PASSWORD_SALT message is also displayed:

Here is the output:
-------------------------

python setup.py
pgAdmin 4 - Application Initialisation
======================================

The configuration database - '/home/fahar/.pgadmin/pgadmin4.db' does not
exist.
Entering initial setup mode...
NOTE: Configuring authentication for SERVER mode.

    Enter the email address and password to use for the initial pgAdmin
user     account:

Email address: [email-masked]
Password:
Retype password:
Traceback (most recent call last):
  File "setup.py", line 449, in <module>
    do_setup(app)
  File "setup.py", line 96, in do_setup
    password = encrypt_password(p1)
  File
"/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py",
line 150, in encrypt_password
    signed = get_hmac(password).decode('ascii')
  File
"/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py",
line 108, in get_hmac
    'set to "%s"' % _security.password_hash)
RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must not be
None when the value of `SECURITY_PASSWORD_HASH` is set to "pbkdf2_sha512"
(venv) fahar@fahar-virtual-machine:~/Projects/pgadmin4/web$

Is this expected?

On Wed, Oct 19, 2016 at 1:37 PM, Fahar Abbas <[email-masked]>
wrote:

>>>>> My concern with that is that we'll likely be storing the default
>>>>> config values in many cases, thus for those users, perpetuating the problem.
>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 15:17:47 +0500

Here is the output of if we copy config_local.py and execute python setup.py
pgAdmin 4 - Application Initialisation
======================================

User can not do any setup for web based now.

On Wed, Oct 19, 2016 at 3:03 PM, Fahar Abbas <[email-masked]>
wrote:

>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py",
> line 108, in get_hmac
> On Wed, Oct 19, 2016 at 1:37 PM, Fahar Abbas <[email-masked]
> > wrote:
>
>> Sure,
>>
>>> Packagers: This change means that packages are no longer forced to
>>> create a config_local.py file, and there is no longer any need to
>>> explicitly set SECURITY_PASSWORD_SALT, SECURITY_KEY
>>> and CSRF_SESSION_KEY in the config (in fact, they should be removed for new
>>> installations, if you have included them in 1.0)
>>>
>>> Thanks.
>>>
>>>
>>>>>>> Hence - it could not set the SECURITY_KEY, and
>>>>>>> SECURITY_PASSWORD_SALT properly.
>>>>>>>
>>>>>>
>>>>>> Hmm.
>>>>>>
>>>>>>
>>>>>>>
>>>>>>> I think - we need to think about different strategy for upgrading
>>>>>>> the configuration file in the 'web' mode.
>>>>>>> I was thinking - we can store the existing security configurations
>>>>>>> in the database during upgrade process in 'web' mode.
>>>>>>>
>>>>>>
>>>>> I don't believe SECURITY_KEY and CSRF_SESSION_KEY are issues either,
>>>>> as they're used for purposes that are essentially ephemeral, and thus can
>>>>> be changed during an upgrade.
>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Ashesh Vashi <[email-masked]>
Date: Wed, 19 Oct 2016 16:25:02 +0530

Hi Fahar,

Please log the case on redmine.
Please find the attached patch, please apply it locally, and test it.

And, please update the case, and this mail chain accordingly.

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com>

On Wed, Oct 19, 2016 at 3:47 PM, Fahar Abbas <[email-masked]>
wrote:

> On Wed, Oct 19, 2016 at 3:03 PM, Fahar Abbas <[email-masked]
> > wrote:
>
>> Dave,
>>
>> Testing Environment
>>
>> Ubuntu 16.04 Linux 64:
>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must not
>> be None when the value of `SECURITY_PASSWORD_HASH` is set to "pbkdf2_sha512"
>> On Wed, Oct 19, 2016 at 1:37 PM, Fahar Abbas <
>> [email-masked]> wrote:
>>
>>> Sure,
>>>
>>>>>> OK, so I've been thinking about this and experimenting for a couple
>>>>>> of hours, as well as annoying the crap out of Magnus by thinking out loud
>>>>>> in his general direction, and it looks like this isn't a major problem as
>>>>>> from what I can see,  SECURITY_PASSWORD_SALT is (aside from really being a
>>>>>> key not a salt) not the only salting that's done.
>>>>>>
>>>>>> So, I believe we can do as you suggest and migrate existing values
>>>>>> for SECURITY_PASSWORD_SALT, given that there's clearly some other per user
>>>>>> and per installation/database salting going on anyway. New installations
>>>>>> can have the random value for SECURITY_PASSWORD_SALT.
>>>>>>

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 16:03:09 +0500

On Wed, Oct 19, 2016 at 3:55 PM, Ashesh Vashi <[email-masked]

> Please log the case on redmine.
https://redmine.postgresql.org/issues/1871

> Sure Will test the patch and update the status accordingly.

> On Wed, Oct 19, 2016 at 3:47 PM, Fahar Abbas <[email-masked]
> > wrote:
>
>> On Wed, Oct 19, 2016 at 3:03 PM, Fahar Abbas <
>>> The configuration database - '/home/fahar/.pgadmin/pgadmin4.db' does
>>> not exist.
>>>>> Fahar, can you please test this thoroughly in desktop and server
>>>>> modes, with both fresh and upgraded installations?
>>>>>
>>>>>>>>> I had moved the Security object initialization after fetching
>>>>>>>>> these configurations from the database.
>>>>>>> It looks like it's used system-wide as the key to generate an HMAC
>>>>>>> of the users password, which is then passed to passlib which salts and
>>>>>>> hashes it. I did some testing, and found that two users with the same
>>>>>>> password end up with different hashes in the database, so clearly there is
>>>>>>> also per-user salting happening. I also created two users, then dropped the
>>>>>>> database and created the same user accounts with the same passwords again,
>>>>>>> and found that the resulting hashes were different in both databases - thus
>>>>>>> there is something else ensuring the hashes are unique across different
>>>>>>> installations/databases.
>>>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Sandeep Thakkar <[email-masked]>
Date: Wed, 19 Oct 2016 16:35:46 +0530

Hi Dave,

On Wed, Oct 19, 2016 at 1:57 PM, Dave Page <[email-masked]> wrote:

> OK. Will remove config_local.py from the packaging. We do not set the
mentioned directives in the config.

-- 
Sandeep Thakkar

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 16:42:16 +0500

On Wed, Oct 19, 2016 at 4:03 PM, Fahar Abbas <[email-masked]>
wrote:

>
>
> On Wed, Oct 19, 2016 at 3:55 PM, Ashesh Vashi <
> This is resolved now and no error message displayed when we apply the
patch that is already shared.

>> On Wed, Oct 19, 2016 at 3:47 PM, Fahar Abbas <
>>>>>>>>>> We were setting the SECURITY_PASSWORD_SALT, after initializing
>>>>>>>>>> the Security object.
>>>>>>>>>> I was thinking - we can store the existing security
>>>>>>>>>> configurations in the database during upgrade process in 'web' mode.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>> I don't believe SECURITY_KEY and CSRF_SESSION_KEY are issues
>>>>>>>> either, as they're used for purposes that are essentially ephemeral, and
>>>>>>>> thus can be changed during an upgrade.
>>>>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Dave Page <[email-masked]>
Date: Wed, 19 Oct 2016 13:05:45 +0100

Great, thanks!

On Wed, Oct 19, 2016 at 12:42 PM, Fahar Abbas <[email-masked]>
wrote:

>
>
> On Wed, Oct 19, 2016 at 4:03 PM, Fahar Abbas <[email-masked]
> > wrote:
>
>>
>>
>>>>>     Enter the email address and password to use for the initial
>>>>> pgAdmin user     account:
>>>>>
>>>>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must
>>>>> not be None when the value of `SECURITY_PASSWORD_HASH` is set to
>>>>> "pbkdf2_sha512"
>>>>>>>>>>> I think - we need to think about different strategy for
>>>>>>>>>>> upgrading the configuration file in the 'web' mode.
>>>>>>>>> OK, so I've been thinking about this and experimenting for a
>>>>>>>>> couple of hours, as well as annoying the crap out of Magnus by thinking out
>>>>>>>>> loud in his general direction, and it looks like this isn't a major problem
>>>>>>>>> as from what I can see,  SECURITY_PASSWORD_SALT is (aside from really being
>>>>>>>>> a key not a salt) not the only salting that's done.
>>>>>>>>>

---

From: Neel Patel <[email-masked]>
Date: Wed, 19 Oct 2016 17:41:15 +0530

Hi,

Just to update for Python 3.
It gives below error while running "pgAdmin4.py".

#####

Traceback (most recent call last):
  File "/usr/lib/python3.4/threading.py", line 920, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.4/threading.py", line 868, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/lib/python3.4/socketserver.py", line 620, in
process_request_thread
    self.handle_error(request, client_address)
  File "/usr/lib/python3.4/socketserver.py", line 617, in
process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.4/socketserver.py", line 344, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.4/socketserver.py", line 673, in __init__
    self.handle()
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
line 200, in handle
    rv = BaseHTTPRequestHandler.handle(self)
  File "/usr/lib/python3.4/http/server.py", line 398, in handle
    self.handle_one_request()
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
line 235, in handle_one_request
    return self.run_wsgi()
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
line 177, in run_wsgi
    execute(self.server.app)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
line 165, in execute
    application_iter = app(environ, start_response)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/_compat.py",
line 33, in reraise
    raise value
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 1643, in full_dispatch_request
    response = self.process_response(response)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 1864, in process_response
    self.save_session(ctx.session, response)
  File
"/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
line 926, in save_session
    return self.session_interface.save_session(self, session, response)
  File
"/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
line 267, in save_session
    self.manager.put(session)
  File
"/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
line 144, in put
    self.parent.put(session)
  File
"/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
line 214, in put
    session.sign(self.secret)
  File
"/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
line 71, in sign
    self.hmac_digest = _calc_hmac('%s:%s' % (self.sid, self.randval),
secret)
  File
"/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
line 44, in _calc_hmac
    secret.encode(), body.encode(), hashlib.sha1
AttributeError: 'bytes' object has no attribute 'encode'
 #######

Thanks,
Neel Patel

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 17:19:38 +0500

Yes Neel is Right.

This issue is also reproducible with Python 3.5 when user Launch python
with pgAdmin4.py

python pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://localhost:5050 in your
browser.
Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/lib/python3.5/threading.py", line 914, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.5/threading.py", line 862, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/lib/python3.5/socketserver.py", line 628, in
process_request_thread
    self.handle_error(request, client_address)
  File "/usr/lib/python3.5/socketserver.py", line 625, in
process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.5/socketserver.py", line 354, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.5/socketserver.py", line 681, in __init__
    self.handle()
  File "/home/fahar/venv/lib/python3.5/site-packages/werkzeug/serving.py",
line 200, in handle
    rv = BaseHTTPRequestHandler.handle(self)
  File "/usr/lib/python3.5/http/server.py", line 422, in handle
    self.handle_one_request()
  File "/home/fahar/venv/lib/python3.5/site-packages/werkzeug/serving.py",
line 235, in handle_one_request
    return self.run_wsgi()
  File "/home/fahar/venv/lib/python3.5/site-packages/werkzeug/serving.py",
line 177, in run_wsgi
    execute(self.server.app)
  File "/home/fahar/venv/lib/python3.5/site-packages/werkzeug/serving.py",
line 165, in execute
    application_iter = app(environ, start_response)
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/_compat.py",
line 33, in reraise
    raise value
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
1643, in full_dispatch_request
    response = self.process_response(response)
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
1864, in process_response
    self.save_session(ctx.session, response)
  File "/home/fahar/venv/lib/python3.5/site-packages/flask/app.py", line
926, in save_session
    return self.session_interface.save_session(self, session, response)
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/utils/session.py", line
267, in save_session
    self.manager.put(session)
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/utils/session.py", line
144, in put
    self.parent.put(session)
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/utils/session.py", line
214, in put
    session.sign(self.secret)
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/utils/session.py", line
71, in sign
    self.hmac_digest = _calc_hmac('%s:%s' % (self.sid, self.randval),
secret)
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/utils/session.py", line
44, in _calc_hmac
    secret.encode(), body.encode(), hashlib.sha1
AttributeError: 'bytes' object has no attribute 'encode'

On Wed, Oct 19, 2016 at 5:11 PM, Neel Patel <[email-masked]>
wrote:

>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/
> site-packages/werkzeug/serving.py", line 200, in handle
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/
> site-packages/werkzeug/serving.py", line 235, in handle_one_request
>     return self.run_wsgi()
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/
> site-packages/werkzeug/serving.py", line 177, in run_wsgi
>     execute(self.server.app)
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/
> site-packages/werkzeug/serving.py", line 165, in execute
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
> line 2000, in __call__
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
> line 1991, in wsgi_app
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/
> site-packages/flask/_compat.py", line 33, in reraise
>     raise value
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
> line 1988, in wsgi_app
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/app.py",
>   File "/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
>   File "/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
> line 144, in put
>     self.parent.put(session)
>   File "/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
> line 214, in put
>   File "/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
> line 71, in sign
>   File "/home/neel/Projects/pgAdmin4/pgadmin4_patch/pgadmin4/web/pgadmin/utils/session.py",
> line 44, in _calc_hmac
> On Wed, Oct 19, 2016 at 5:12 PM, Fahar Abbas <[email-masked]
> > wrote:
>
>>
>>
>> On Wed, Oct 19, 2016 at 4:03 PM, Fahar Abbas <
>>>>>> If user does not create config_local.py and do Python setup.py for
>>>>>> new Development then SECURITY_PASSWORD_SALT message is also displayed:
>>>>>>
>>>>>> Here is the output:
>>>>>>>>>> It looks like it's used system-wide as the key to generate an
>>>>>>>>>> HMAC of the users password, which is then passed to passlib which salts and
>>>>>>>>>> So, I believe we can do as you suggest and migrate existing
>>>>>>>>>> values for SECURITY_PASSWORD_SALT, given that there's clearly some other
>>>>>>>>>> per user and per installation/database salting going on anyway. New
>>>>>>>>>> installations can have the random value for SECURITY_PASSWORD_SALT.
>>>>>>>>>>
>>>>>>>>> We do not need to generate the random SECURITY_PASSWORD_SALT
>>>>>>>>> during upgrade mode, which was wrong added in my addon patch.
>>>>>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Sandeep Thakkar <[email-masked]>
Date: Wed, 19 Oct 2016 18:09:31 +0530

Here is the patch where we remove the config_local.py being created during
packaging. The mac build script missed creating config_distro.py earlier
and it has been take care of now. Please review the attached patch.

I'll also make the changes in the EDB packaging scripts where we bundle
pgAdmin in PG server and EPAS Meta.

-- 
Sandeep Thakkar

---

From: Fahar Abbas <[email-masked]>
Date: Wed, 19 Oct 2016 17:51:07 +0500

After applying this patch on Python 2.7.11 on Windows 2012 64bit, User can
launch pgAdmin4 with python pgAdmin4.py without any error. Will also retest
once the 1871 will be resolved

On Wed, Oct 19, 2016 at 5:19 PM, Fahar Abbas <[email-masked]>
wrote:

>>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
>> line 200, in handle
>>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
>>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
>> line 177, in run_wsgi
>>     execute(self.server.app)
>>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/werkzeug/serving.py",
>> line 165, in execute
>>   File "/home/neel/workspace/pgAdmin4_3_4/lib/python3.4/site-packages/flask/_compat.py",
>> line 33, in reraise
>>     raise value
>> On Wed, Oct 19, 2016 at 5:12 PM, Fahar Abbas <
>>>>>>> The configuration database - '/home/fahar/.pgadmin/pgadmin4.db'
>>>>>>> does not exist.
>>>>>>>>>>>> My concern with that is that we'll likely be storing the
>>>>>>>>>>>> default config values in many cases, thus for those users, perpetuating the
>>>>>>>>>>>> problem.
>>>>>>>>>>>>
>>>>>>>>>>>> I guess what we need to do is re-encrypt the password during
>>>>>>>>>>>> the upgrade - however, that makes me think; we then have both the key and
>>>>>>>>>>>> the encrypted passwords in the same database which is clearly not a good
>>>>>>>>>>>> idea. Sigh... Needs more thought.
>>>>>>>>>>>>
>>>>>>>>>>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Dave Page <[email-masked]>
Date: Wed, 19 Oct 2016 13:56:36 +0100

I assume that's an existing issue with Python 3.5? That file wasn't changed
by this patch.

---

From: Dave Page <[email-masked]>
Date: Wed, 19 Oct 2016 14:04:41 +0100

Patch applied.

---

From: Dave Page <[email-masked]>
Date: Wed, 19 Oct 2016 14:10:38 +0100

Thanks, applied.

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 10:33:11 +0500

Hi Dave,

I have reopened following RM:
================================
https://redmine.postgresql.org/issues/1849

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 20 Oct 2016 11:15:35 +0530

Hi,

PFA patch to fix the issue for Pyhton3.
RM#1849

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

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 10:56:42 +0500

Murtaza,

I have applied this patch and there is no success on new pgAdmin4 setup as
well as existing pgAdmin4 setup.

On Thu, Oct 20, 2016 at 10:45 AM, Murtuza Zabuawala <
[email-masked]> wrote:

> On Thu, Oct 20, 2016 at 11:03 AM, Fahar Abbas <

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 20 Oct 2016 11:30:54 +0530

Could you delete 'keys' table from pgadmin4.db file & try again?

On Thu, Oct 20, 2016 at 11:26 AM, Fahar Abbas <[email-masked]>
wrote:

> On Thu, Oct 20, 2016 at 10:45 AM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>> Hi,
>>

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 11:38:41 +0500

I tried different variations and when launch pgAdmin4 with web browser
still exception displayed when user deleted .pgadmin folder

Here is the output:
------------------------
python pgAdmin4.py
  File "/usr/lib/python3.5/http/server.py", line 415, in handle

On Thu, Oct 20, 2016 at 11:00 AM, Murtuza Zabuawala <
[email-masked]> wrote:

> On Thu, Oct 20, 2016 at 11:26 AM, Fahar Abbas <
>> I have applied this patch and there is no success on new pgAdmin4 setup
>> as well as existing pgAdmin4 setup.
>>
>>>>>>>>>> Packagers: This change means that packages are no longer forced
>>>>>>>>>> to create a config_local.py file, and there is no longer any need to
>>>>>>>>>>>>>> We were setting the SECURITY_PASSWORD_SALT, after
>>>>>>>>>>>>>> initializing the Security object.

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Dave Page <[email-masked]>
Date: Thu, 20 Oct 2016 07:45:02 +0100

I think you'll also need to set the version back to 13 in the version table.

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

> On 20 Oct 2016, at 07:00, Murtuza Zabuawala <[email-masked]> wrote:
> 
>> On Thu, Oct 20, 2016 at 11:26 AM, Fahar Abbas <[email-masked]> wrote:
>> Murtaza,
>> 
>> I have applied this patch and there is no success on new pgAdmin4 setup as well as existing pgAdmin4 setup.
>> 
>>> On Thu, Oct 20, 2016 at 10:45 AM, Murtuza Zabuawala <[email-masked]> wrote:
>>> Hi,
>>> 
>>>> On Thu, Oct 20, 2016 at 11:03 AM, Fahar Abbas <[email-masked]> wrote:
>>>> Hi Dave,
>>>> 
>>>>>> On Wed, Oct 19, 2016 at 11:55 AM, Ashesh Vashi <[email-masked]> wrote:
>>>>>> Hi Fahar,
>>>>>> 
>>>>>> http://www.linkedin.com/in/asheshvashi
>>>>>> 
>>>>>>> On Wed, Oct 19, 2016 at 3:47 PM, Fahar Abbas <[email-masked]> wrote:
>>>>>>> The configuration database - '/home/fahar/.pgadmin/pgadmin4.db' does not exist.
>>>>>>>     Enter the email address and password to use for the initial pgAdmin user     account:
>>>>>>> 
>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 150, in encrypt_password
>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 108, in get_hmac
>>>>>>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must not be None when the value of `SECURITY_PASSWORD_HASH` is set to "pbkdf2_sha512"
>>>>>>> python setup.py
>>>>>>> The configuration database - '/home/fahar/.pgadmin/pgadmin4.db' does not exist.
>>>>>>>     Enter the email address and password to use for the initial pgAdmin user     account:
>>>>>>> 
>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 150, in encrypt_password
>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 108, in get_hmac
>>>>>>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must not be None when the value of `SECURITY_PASSWORD_HASH` is set to "pbkdf2_sha512"
>>>>>>> 
>>>>>>>> On Wed, Oct 19, 2016 at 3:03 PM, Fahar Abbas <[email-masked]> wrote:
>>>>>>>> Dave,
>>>>>>>> 
>>>>>>>> Testing Environment
>>>>>>>>  
>>>>>>>> Ubuntu 16.04 Linux 64:
>>>>>>>> 10) Edit the config.py file to config_local.py  resides in Projects\pgAdmin4\web  
>>>>>>>> If user does not create config_local.py and do Python setup.py for new Development then SECURITY_PASSWORD_SALT message is also displayed:
>>>>>>>> 
>>>>>>>> Here is the output:
>>>>>>>> The configuration database - '/home/fahar/.pgadmin/pgadmin4.db' does not exist.
>>>>>>>>     Enter the email address and password to use for the initial pgAdmin user     account:
>>>>>>>> 
>>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 150, in encrypt_password
>>>>>>>>   File "/home/fahar/venv/lib/python3.5/site-packages/flask_security/utils.py", line 108, in get_hmac
>>>>>>>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT` must not be None when the value of `SECURITY_PASSWORD_HASH` is set to "pbkdf2_sha512"
>>>>>>>>> On Wed, Oct 19, 2016 at 1:37 PM, Fahar Abbas <[email-masked]> wrote:
>>>>>>>>> Sure,
>>>>>>>>> 
>>>>>>>>>> Fahar, can you please test this thoroughly in desktop and server modes, with both fresh and upgraded installations?
>>>>>>>>>> 
>>>>>>>>>> Packagers: This change means that packages are no longer forced to create a config_local.py file, and there is no longer any need to explicitly set SECURITY_PASSWORD_SALT, SECURITY_KEY and CSRF_SESSION_KEY in the config (in fact, they should be removed for new installations, if you have included them in 1.0)
>>>>>>>>>> 
>>>>>>>>>> Thanks.
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>>> On Wed, Oct 19, 2016 at 6:46 AM, Ashesh Vashi <[email-masked]> wrote:
>>>>>>>>>>> Hi Dave,
>>>>>>>>>>> 
>>>>>>>>>>>>>> On Thursday, October 13, 2016, Ashesh Vashi <[email-masked]> wrote:
>>>>>>>>>>>>>> Hi Dave,
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> We were setting the SECURITY_PASSWORD_SALT, after initializing the Security object.
>>>>>>>>>>>>>> Hence - it could not set the SECURITY_KEY, and SECURITY_PASSWORD_SALT properly.
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Hmm.
>>>>>>>>>>>>>  
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> I had moved the Security object initialization after fetching these configurations from the database.
>>>>>>>>>>>>>> Because - the existing password was hashed using the old SECURITY_PASSWORD_SALT, I am no more able to login to pgAdmin 4.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> I think - we need to think about different strategy for upgrading the configuration file in the 'web' mode.
>>>>>>>>>>>>>> I was thinking - we can store the existing security configurations in the database during upgrade process in 'web' mode.
>>>>>>>>>>>>> 
>>>>>>>>>>>>> My concern with that is that we'll likely be storing the default config values in many cases, thus for those users, perpetuating the problem.
>>>>>>>>>>>>> 
>>>>>>>>>>>>> I guess what we need to do is re-encrypt the password during the upgrade - however, that makes me think; we then have both the key and the encrypted passwords in the same database which is clearly not a good idea. Sigh... Needs more thought. 
>>>>>>>>>>>> 
>>>>>>>>>>>> OK, so I've been thinking about this and experimenting for a couple of hours, as well as annoying the crap out of Magnus by thinking out loud in his general direction, and it looks like this isn't a major problem as from what I can see,  SECURITY_PASSWORD_SALT is (aside from really being a key not a salt) not the only salting that's done. 
>>>>>>>>>>>> 
>>>>>>>>>>>> It looks like it's used system-wide as the key to generate an HMAC of the users password, which is then passed to passlib which salts and hashes it. I did some testing, and found that two users with the same password end up with different hashes in the database, so clearly there is also per-user salting happening. I also created two users, then dropped the database and created the same user accounts with the same passwords again, and found that the resulting hashes were different in both databases - thus there is something else ensuring the hashes are unique across different installations/databases.
>>>>>>>>>>>> 
>>>>>>>>>>>> So, I believe we can do as you suggest and migrate existing values for SECURITY_PASSWORD_SALT, given that there's clearly some other per user and per installation/database salting going on anyway. New installations can have the random value for SECURITY_PASSWORD_SALT.
>>>>>>>>>>> 
>>>>>>>>>>> We do not need to generate the random SECURITY_PASSWORD_SALT during upgrade mode, which was wrong added in my addon patch.
>>>>>>>>>>> 
>>>>>>>>>>> http://www.linkedin.com/in/asheshvashi  
>>>>>>>>>>>> 
>>>>>>>>>>>> I don't believe SECURITY_KEY and CSRF_SESSION_KEY are issues either, as they're used for purposes that are essentially ephemeral, and thus can be changed during an upgrade.
>>>>>>>>>>>> 
>>>>>>>>>>>> Patch attached - please review (Ashesh, but others too would be appreciated)!
>>>>>>>>>>>> 
>>>>>>>>>>>> Thanks.
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> -- 
>>>>>>>>>>>> Dave Page

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 12:30:49 +0500

Murtaza,

Once i delete key table from pgAdmin4.db file then i can launch pgAdmin4
with terminal as well as web browser for existing pgAdmin4 setup.

In case of fresh setup once we delete key table and apply latest patch
following message displayed:

python pgAdmin4.py
Traceback (most recent call last):
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1139, in _execute_context
    context)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: keys

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "pgAdmin4.py", line 46, in <module>
    app = create_app()
  File "/home/fahar/Projects/pgadmin4/web/pgadmin/__init__.py", line 224,
in create_app
    config.CSRF_SESSION_KEY = Keys.query.filter_by(name =
'CSRF_SESSION_KEY').first().value
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2659, in first
    ret = list(self[0:1])
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2457, in __getitem__
    return list(res)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2761, in __iter__
    return self._execute_and_instances(context)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2776, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 914, in execute
    return meth(self, multiparams, params)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1146, in _execute_context
    context)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1341, in _handle_dbapi_exception
    exc_info
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 185, in reraise
    raise value.with_traceback(tb)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1139, in _execute_context
    context)
  File
"/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table:
keys [SQL: 'SELECT keys.name AS keys_name, keys.value AS keys_value \nFROM
keys \nWHERE keys.name = ?\n LIMIT ? OFFSET ?'] [parameters:
('CSRF_SESSION_KEY', 1, 0)]

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Ashesh Vashi <[email-masked]>
Date: Thu, 20 Oct 2016 13:06:29 +0530

On Thu, Oct 20, 2016 at 1:00 PM, Fahar Abbas <[email-masked]>
wrote:

>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
> line 450, in do_execute
That's because - you've not yet update the ConfigDB to 13 in the version
table in the sqlite configuration table.

And, the next exception, you're describing, is result of that.

--

Thanks & Regards,

Ashesh Vashi

>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
> line 2659, in first
>     ret = list(self[0:1])
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
> line 2761, in __iter__
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
> line 914, in execute
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
> line 185, in reraise
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>   File "/home/fahar/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
> line 450, in do_execute
> sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such
> table: keys [SQL: 'SELECT keys.name AS keys_name, keys.value AS
> keys_value \nFROM keys \nWHERE keys.name = ?\n LIMIT ? OFFSET ?']
> [parameters: ('CSRF_SESSION_KEY', 1, 0)]
>
>
> On Thu, Oct 20, 2016 at 11:00 AM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>>>>>>>>>   File "/home/fahar/venv/lib/python3.
>>>>>>>>> 5/site-packages/flask_security/utils.py", line 150, in
>>>>>>>>> encrypt_password
>>>>>>>>>   File "/home/fahar/venv/lib/python3.
>>>>>>>>> 5/site-packages/flask_security/utils.py", line 108, in get_hmac
>>>>>>>>> RuntimeError: The configuration value `SECURITY_PASSWORD_SALT`
>>>>>>>>> must not be None when the value of `SECURITY_PASSWORD_HASH` is set to
>>>>>>>>> "pbkdf2_sha512"
>>>>>>>>>>>>>>> I had moved the Security object initialization after
>>>>>>>>>>>>>>> fetching these configurations from the database.

---

From: Ashesh Vashi <[email-masked]>
Date: Thu, 20 Oct 2016 13:23:30 +0530

You were missing the other places, before the function - do_setup(..) call,
where we are generating the other keys automatically.
I've checked-in the code.

Fahar,

If you want to test the issue properly, please follow the below steps:
- git checkout c4f1b8eb112e99d228c40a412020fa616dbe44f6
  This was the commit-id, where we have the configuration schema version
'13'.
- Delete the existing pgadmin4.db
- Set CSRF_SESSION_KEY, SECRET_KEY, SECURITY_PASSWORD_SALT in
config_local.py.
- Execute the command - 'python setup.py'.
- Now - you've configuration file with old schema.
- 'git checkout master' to go to the latest code.
- Make sure - you've latest code. 'git pull'.
- Now - rerun 'python setup.py/pgAdmin4.py'. It should work.

--

Thanks & Regards,

Ashesh Vashi

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 13:03:11 +0500

Thanks Ashesh for your steps and will also test with new setup( on fresh
machine) and our customers can face this problem with new setup and will
update the status accordingly.

On Thu, Oct 20, 2016 at 12:53 PM, Ashesh Vashi <
[email-masked]> wrote:

> You were missing the other places, before the function - do_setup(..)
> call, where we are generating the other keys automatically.
> On Thu, Oct 20, 2016 at 11:15 AM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>> Hi,
>>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 13:35:53 +0500

Thanks pgAdmin4 Development team!

User can launch pgAdmin4 with web browser with no issues with fresh setup,
i tried on Ubuntu 16.04 Linux 64(Python 3.5). Will also try on OS X with
Python 3 and will let you know.

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation

---

From: Fahar Abbas <[email-masked]>
Date: Thu, 20 Oct 2016 15:54:30 +0500

All,

I have not seen any issue in pgAdmin4 on MAC OS X(Python 3.5) Platform as
well hence 1849 is resolved.

Kind Regards,

On Thu, Oct 20, 2016 at 1:35 PM, Fahar Abbas <[email-masked]>
wrote:

> On Thu, Oct 20, 2016 at 1:03 PM, Fahar Abbas <[email-masked]
> > wrote:
>

-- 
Syed Fahar Abbas
Quality Management Group

EnterpriseDB Corporation