Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
YChapran opened this issue Jul 14, 2021 · 3 comments
Open
Labels

Comments

@YChapran
Copy link

YChapran commented Jul 14, 2021

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 jmafc added the dbtoyaml label Jul 14, 2021
@jmafc
Copy link
Member

jmafc commented Jul 14, 2021

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
Copy link
Author

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
Copy link
Member

jmafc commented Jul 14, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants