search

3liz / qgis-pgmetadata-plugin

QGIS Plugin to manage some metadata from PostgreSQL layer
GNU General Public License v2.0
12 stars 10 forks source link

PgMetadata schema installation failing on "ALTER SEQUENCE" - sequence and table have different owners #167

Open Marsimperator opened 1 month ago

Marsimperator commented 1 month ago

Beforehand: This problem may be specific to my postgres db

Hi, i encountered a problem during the execution of the tool 'pg_metadata:create_database_structure', i.e. the first tool you execute to install PgMetadata. I am a new PgMetadata user and wanted to try the plugin and see if it would be useful for our work.

The Error

I got an SQL Error: [ERROR: sequence must have same owner as table it is linked to]

So what happened?

Beforehand: I did not setup/create our database, I am the GIS/Python-guy that was granted user privileges to work on the DB.

After the installation failed, I went in manually and executed the statements from _20_TABLE_SEQUENCEDEFAULT.sql piece by piece to see what went wrong. I created the table "contact" and the following sequence: "pgmetadata.contact_id_seq". The error occured on:

  ALTER SEQUENCE pgmetadata.contact_id_seq OWNED BY pgmetadata.contact.id;

The error implied that they might have different owners, so I checked the owners. The result? I was the owner of the sequence, but I was not the owner of the table! That was a huge surprise to me as I had just created the table myself.

I am not proficient enough with PostgreSQL to know exactly why this happens, but: _The role (let's call it:) postgis_user was automatically the owner of every newly created table, instead of the actual user who issued the command!_ So that error may be caused by a specific setup/properties of the PostgreSQL database that is used. If you know more about that, feel free to let me know.

Solution To circumvent this, I modified the "20_TABLE_SEQUENCE_DEFAULT.sql" file and included the following command after each table creation:

  ALTER TABLE pgmetadata.<table_name> owner TO "<my_user_name>";

Then I was the owner of each table and each sequence. and the installation worked flawlessly.

Conclusion

This may be a niche problem, but maybe it could be beneficial for the user that is setting up PgMetadata, to be able to specify the table owner in the tool-window as an optional parameter?

Greetings

effjot commented 1 month ago

It might in fact be a local configuration issue. Try to find the person administrating the DB or have a look at the DB with pgAdmin, e.g. in the “Event Triggers” section. In our setup, there are a few event triggers to adjust owners and privileges when a schema or table is created. Postgresql’s access control management is quite fine-grained and your admin might have missed something.