search

perseas / Pyrseas

Provides utilities for Postgres database schema versioning.
https://perseas.github.io/
BSD 3-Clause "New" or "Revised" License
395 stars 67 forks source link

Sequences processing doesn't skip schemas defined with '-N' option. #234

Open YChapran opened 3 years ago

YChapran commented 3 years ago

Sequences processing doesn't skip schemas defined with '-N' option, as a result in the case the DB has restricted rights and I'm not interested in the restricted schemas the dbtoyaml fails on retrieval of information for Sequences from the restricted scheme.

Steps:

  1. Have several schemas in DB. One scheme should have restricted access and a table with a Sequence.
  2. Run dbtoyaml -W -x -n myShemaName -N restrictedSchemaName dbName

Result:

  1. Command fails with error: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/bin/dbtoyaml", line 8, in sys.exit(main()) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/dbtoyaml.py", line 49, in main dbmap = db.to_map() File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/database.py", line 443, in to_map self.from_catalog(True) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/database.py", line 349, in from_catalog self.db = self.Dicts(self.dbconn, single_db) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/database.py", line 97, in init self.tables = ClassDict(dbconn) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/dbobject/init.py", line 633, in init self._from_catalog() File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/dbobject/table.py", line 840, in _from_catalog obj.get_dependent_table(self.dbconn) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pyrseas/dbobject/table.py", line 169, in get_dependent_table data = dbconn.fetchone( File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pgdbconn/dbconn.py", line 93, in fetchone curs = self.execute(query, args) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pgdbconn/dbconn.py", line 81, in execute raise exc File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pgdbconn/dbconn.py", line 77, in execute curs.execute(query, args) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/psycopg2/extras.py", line 146, in execute return super().execute(query, vars) psycopg2.errors.InsufficientPrivilege: permission denied for schema restrictedSchemaName

Expected: As restrictedSchemaName is set to be skipped no data related to this schema should be read from the DB. The yaml file printed to stdout.

jmafc commented 3 years ago

I believe there are possibly two issues here. The first is that, although we would like to dbtoyaml to be run by any PG user, it is sometimes not possible. In general, Pyrseas only queries the system catalogs which AFAIK are always visible to any user. However, in some cases we have to fetch some data from objects (e.g., sequences before PG 10, see objects/table.py line 134) or using PG system functions/conversions that enforce privileges and restrictions. I think this is the case here (see objects/table.py line 169). That query could, perhaps, be deconstructed to sidestep the privileges, but it would be (a) less efficient and (b) less maintainable. Nevertheless, dbtoyaml should probably be modified to catch the InsufficientPrivilege exception and report the problem with a more user friendly message. The second potential issue is that, even if dbtoyaml is run from an admin account that doesn't run into the privilege problem, the output may not be according to expectations. This is a conjecture on my part.

YChapran commented 3 years ago

Thank you for the quick feedback.

What for do you need the information from the schemas defined to skip? Assume, farther you drop information related to the schemas you are not interested in. From this view reading extra that is just finally dropped is already a high performance penalty. As for me, this is a bug with the '-N' parameter as it states that specified schema is going to be avoided - Does not extract schema matching schema..

jmafc commented 3 years ago

Extract was perhaps not the best word to use. Since the general use case for Pyrseas is to deal with a whole database, we always retrieve "all" catalog data ("all" is in quotes because most system objects are excluded but that does cause other problems). Schemas may be useful constructs for DBAs to segregate tables and other objects into seemingly neat "rooms", but PG allows connections between these "rooms" in arbitrary ways. There's nothing preventing a DBA from defining a table in schema A, a related sequence in schema B and a trigger function on the table in schema C. So Pyrseas fetches everything first, and then on output determines what ought to be "extracted". For example, if you run dbtoyaml -t sometable, it will (or should) output not only the sometable definition but a someseq sequence (assuming it's related to sometable) even if it's defined in a different schema (BTW, this is the way that pg_dump behaves also), because otherwise you could not use the YAML output to recreate the table and its sequence in another database.