Saleor: Unable to install, permission denied to create extension "hstore"

[onelsonic]

trying to install saleor, but getting the following error :

django.db.utils.ProgrammingError: permission denied to create extension "hstore" HINT: Must be superuser to create this extension.

I don't and will not have the superuser right for postgreSQL for security reasons.
(shared hosting solution)

Can we manually do what this extension does, with simple SQL command.
Or can the database be manually prepared so we don;t have to use this extension.

[NyanKiyoshi]

I think you would have to run CREATE EXTENSION IF NOT EXISTS hstore; and drop the operation from this migration file:

https://github.com/mirumee/saleor/blob/ceed73c8214f34df66e106f98efb2bbd2c84c24c/saleor/product/migrations/0013_auto_20161130_0608.py#L9-L13

[onelsonic]

when doing :
CREATE EXTENSION IF NOT EXISTS hstore;

I get the following :

ERROR:  permission denied to create extension "hstore"
HINT:  Must be superuser to create this extension.

Is there anyway to use Saleor without the "hstore" extension ?

What about JSONB , is this not more recent, up to date?

[NyanKiyoshi]

Yes, look for Hstore in migrations, and remove them, should work. Maybe we could have a flag to disable it as we don't need it apart for backward compatibility.

[onelsonic]

Yes a flag or parameter for the install would be perfect.
I found all these files below with a reference to hstore, how should they be edited to include this flag, so we can install Saleor without hstore ?
Otherwise Saleor cannot be installed on any shared hosting providers.

• [ ] 1. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0013_auto_20161130_0608.py
• [ ] 2. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0015_productvariant_attributes.py
• [ ] 3. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0016_auto_20161204_0311.py
• [ ] 4. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0021_add_hstore_extension.py
• [ ] 5. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0022_auto_20161212_0301.py
• [ ] 6. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0023_auto_20161211_1912.py
• [ ] 7. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0044_auto_20180108_0814.py
• [ ] 8. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0056_auto_20180330_0321.py
• [ ] 9. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0070_auto_20180912_0329.py
• [ ] 10. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0074_auto_20181010_0730.py
• [ ] 11. https://github.com/mirumee/saleor/blob/master/saleor/product/migrations/0104_fix_invalid_attributes_map.py

[NyanKiyoshi]

A flag wouldn't be needed in your case, just drop the hstore references. But for the core itself, it would be nice to have such flag.

Otherwise Saleor cannot be installed on any shared hosting providers.

Most of them should allow admin operations over databases, thus including having a super users for installing extensions and scripts. That is very odd to not allow such rights to the customers.

[NyanKiyoshi]

@onelsonic check out the above-linked pull request for a solution to your need 👍

[onelsonic]

Nice one.
I checked with many hosting providers. It is common practice to not provide superuser rights to their database profiles on shared hosting. Superuser rights need a dedicated server/machine, not the entry level offers.
Wordpress and Prestashop can all be installed on shared hosting for example.

I tried commenting the following in some of the files :

0013_auto_20161130_0608.py :
# from django.contrib.postgres.operations import HStoreExtension

# operations = [HStoreExtension()]

0056_auto_20180330_0321.py
# import django.contrib.postgres.fields.hstore

but then, not too sure what to do with these for example :

        migrations.AlterField(
            model_name="product",
            name="attributes",
            field=django.contrib.postgres.fields.hstore.HStoreField(
                blank=True, default={}
            ),

should this rewritten in JSONB or converted ? That where I stopped so far.

Looking forward for the flag to work.
This will provide the ability to fully install Saleor on more plateforms.

[NyanKiyoshi]

Use the PR instead of trying to rewrite exact same PR. You can append .patch at the end of the URL to get a patch file.

[onelsonic]

I applied both commits :
https://github.com/mirumee/saleor/pull/5576/commits/aba3321ec8a1f8560912fafc64a21d9628be84fc
then
https://github.com/mirumee/saleor/pull/5576/commits/2ae4aa28a1c8e42bd23eadff2c171c15ebc4ebb6

I tested it twice and still get an error with the following file :
/saleor/saleor/product/migrations/product.0022_auto_20161212_0301.py

# python manage.py migrate
/saleor/saleor/urls.py:31: UserWarning: The debug toolbar was not installed. Ignore the error.             settings.py should already have warned the user about it.
  settings.py should already have warned the user about it."
Operations to perform:
  Apply all migrations: account, app, auth, checkout, contenttypes, core, discount, django_prices_openexchangerates, django_prices_vatlayer, giftcard, menu, order, page, payment, plugins, product, shipping, site, sites, warehouse, webhook, wishlist
Running migrations:
  Applying product.0022_auto_20161212_0301...Traceback (most recent call last):
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedObject: type "hstore" does not exist
LINE 1: ...R TABLE "product_product" ADD COLUMN "attributes" hstore DEF...
                                                             ^


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

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 233, in handle
    fake_initial=fake_initial,
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/migrations/operations/fields.py", line 112, in database_forwards
    field,
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 480, in add_field
    self.execute(sql, params)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: type "hstore" does not exist
LINE 1: ...R TABLE "product_product" ADD COLUMN "attributes" hstore DEF...
                                                             ^

[zodiacfireworks]

To create an extension by running migrate, make your database user superuser

ALTER USER myuser WITH SUPERUSER;

After run migrate, remove the superuser role from your user.

ALTER USER username WITH NOSUPERUSER;

You only need the superuser role to create the extensions.

You can also create the extension manually and then run migrations, if the extensions already exist the database user will not try to create it.

CREATE EXTENSION IF NOT EXISTS hstore;

[NyanKiyoshi]

@onelsonic did you set BACKWARD_HSTORE_COMPATIBILITY to False?

export BACKWARD_HSTORE_COMPATIBILITY=False

[onelsonic]

ok with the flag set to false, it works. no more hstore errors.
The migrations starts and works now fine except that it stops at this file now :
0037_auto_20171124_0847.py

When digging into it. The problem is the way the database migration is handled. Having to rely on third party PostgreSQL extensions is creating and will create this problem on any Shared Hosting providers, reducing the scope of Saleor.

So it seem this process needs to be extended to the extensions used for the database migrations.
Digging into the topic, Oracle communicated as well on this issue when one migrates their database to PostgreSQL.

We need to bypass or process manually this extension "pg_trgm" as well.

Running migrations:
  Applying product.0022_auto_20161212_0301... OK
  Applying product.0023_auto_20161211_1912... OK
  Applying product.0024_migrate_json_data... OK
  Applying product.0025_auto_20161219_0517... OK
  Applying product.0026_auto_20161230_0347... OK
  Applying product.0027_auto_20170113_0435... OK
  Applying product.0013_auto_20161130_0608... OK
  Applying product.0014_remove_productvariant_attributes... OK
  Applying product.0015_productvariant_attributes... OK
  Applying product.0016_auto_20161204_0311... OK
  Applying product.0017_attributechoicevalue_slug... OK
  Applying product.0018_auto_20161212_0725... OK
  Applying product.0026_merge_20161221_0845... OK
  Applying product.0028_merge_20170116_1016... OK
  Applying product.0029_product_is_featured... OK
  Applying product.0030_auto_20170206_0407... OK
  Applying product.0031_auto_20170206_0601... OK
  Applying product.0032_auto_20170216_0438... OK
  Applying product.0033_auto_20170227_0757... OK
  Applying product.0034_product_is_published... OK
  Applying product.0035_auto_20170919_0846... OK
  Applying product.0036_auto_20171115_0608... OK
  Applying product.0037_auto_20171124_0847...Traceback (most recent call last):
  File "/virtualenv/web/3.7/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InsufficientPrivilege: permission denied to create extension "pg_trgm"
HINT:  Must be superuser to create this extension. 

[NyanKiyoshi]

For that you would need to change the logic of the product search:
https://github.com/mirumee/saleor/blob/d0f029e3c30d3ff64b01c60f3c0033febc69235c/saleor/search/backends/postgresql_storefront.py#L7-L24

Nothing I can do there, that's a feature

[onelsonic]

Thanks @NyanKiyoshi
Using the flag set to false, the outdated extension "hstore" is not more required, this works greats
the "pg_trgm" is another thing to deal with so I created another issue for it :
https://github.com/mirumee/saleor/issues/5579

This one can be closed, and the flag is working fine.

[iDevPy]

Login to PostgreSQL and run:
CREATE EXTENSION IF NOT EXISTS hstore;

For other extensions you can do:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;