Re: RM1849: Auto-generating security keys

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

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

On Thu, Oct 20, 2016 at 11:26 AM, Fahar Abbas <fahar(dot)abbas(at)enterprisedb(dot)com>
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 <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>> Hi,
>>
>> PFA patch to fix the issue for Pyhton3.
>> RM#1849
>>
>> --
>> Regards,
>> Murtuza Zabuawala
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> On Thu, Oct 20, 2016 at 11:03 AM, Fahar Abbas <
>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>
>>> Hi Dave,
>>>
>>> I have reopened following RM:
>>> ================================
>>> https://redmine.postgresql.org/issues/1849
>>>
>>> On Wed, Oct 19, 2016 at 6:04 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>> Patch applied.
>>>>
>>>> On Wed, Oct 19, 2016 at 11:55 AM, Ashesh Vashi <
>>>> ashesh(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> 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>
>>>>>
>>>>>
>>>>> *http://www.linkedin.com/in/asheshvashi*
>>>>> <http://www.linkedin.com/in/asheshvashi>
>>>>>
>>>>> On Wed, Oct 19, 2016 at 3:47 PM, Fahar Abbas <
>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Here is the output of if we copy config_local.py and execute 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: fahar(dot)abbas(at)enterprisedb(dot)com
>>>>>> 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"
>>>>>> python setup.py
>>>>>> pgAdmin 4 - Application Initialisation
>>>>>> ======================================
>>>>>>
>>>>>> User can not do any setup for web based now.
>>>>>>
>>>>>>
>>>>>> 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: fahar(dot)abbas(at)enterprisedb(dot)com
>>>>>> 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"
>>>>>>
>>>>>> On Wed, Oct 19, 2016 at 3:03 PM, Fahar Abbas <
>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>
>>>>>>> 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: fahar(dot)abbas(at)enterprisedb(dot)com
>>>>>>> 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(at)fahar-virtual-machine:~/Projects/pgadmin4/web$
>>>>>>>
>>>>>>>
>>>>>>> Is this expected?
>>>>>>>
>>>>>>> On Wed, Oct 19, 2016 at 1:37 PM, Fahar Abbas <
>>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>>
>>>>>>>> Sure,
>>>>>>>>
>>>>>>>> Will test this thoroughly after complete investigation.
>>>>>>>>
>>>>>>>> Kind Regards,
>>>>>>>>
>>>>>>>> On Wed, Oct 19, 2016 at 1:27 PM, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> 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 <
>>>>>>>>> ashesh(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Dave,
>>>>>>>>>>
>>>>>>>>>> On Sat, Oct 15, 2016 at 8:02 AM, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Friday, October 14, 2016, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>>> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Hi
>>>>>>>>>>>>
>>>>>>>>>>>> On Thursday, October 13, 2016, Ashesh Vashi <
>>>>>>>>>>>> ashesh(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>> Hi Dave,
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Tue, Oct 11, 2016 at 9:10 PM, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> Hi Ashesh,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Can you please review the attached patch, and apply if you're
>>>>>>>>>>>>>> happy with it?
>>>>>>>>>>>>>>
>>>>>>>>>>>>> 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.
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Hmm.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> I had moved the Security object initialization after fetching
>>>>>>>>>>>>> these configurations from the database.
>>>>>>>>>>>>> I have attached a addon patch for the same.
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> OK, thanks.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> 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.
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> 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.
>>>>>>>>>>
>>>>>>>>>> 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>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> 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.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Dave Page
>>>>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>
>>>>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Dave Page
>>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>
>>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> 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
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> 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
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Dave Page
>>>> Blog: http://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>>
>>>
>>>
>>>
>>> --
>>> 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
>>>
>>
>>
>
>
> --
> 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
>