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

[Marsimperator]

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]

• occurred during execution of: "20_TABLE_SEQUENCE_DEFAULT.sql"

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_SEQUENCE_DEFAULT.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]

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.