in Databases

DDL permissions in MySQL vs. Postgres

I ran into a problem the other day where I was running an ETL job that plays a shell game with tables. I had tested this ETL job initially with an admin user that had the super privilege and everything went well. The job loads up staging tables and then in a transaction will rename the primary table to a primary_old name, and the primary_staging table to the primary table. Then the primary_old table is renamed to primary_staging and truncated in preparation for the next run. I decided this job should not have the super privilege, so I created an ETL user and gave it all of the permissions it should need on the database, schema, and tables. I ran the job and it started out wonderfully. Loaded up the staging table just fine but choked when it tried to rename the tables. In MySQL you can grant the “alter” privilege on the database or table and you are good to go. Postgres however requires that you have the appropriate permission and that you are the OWNER of the object/table. You can see the owners of the tables or objects by typing “\d” in psql. If you create everything as a local admin or Postgres user, the objects will be owned by that user therefore preventing an ETL user from altering or renaming the table. This leaves a few options that I have discovered from searching or can think of.

  • Change the owner of the object to the ETL user
  • Create a role and assign the table or object to that role and put the ETL user and the admin or Postgres user in that role
  • Grant the super privilege to the ETL user directly or put it in the Postgres or admin role that you want to own the object.

Personally I am not all that thrilled with any of those options as they all require either privilege escalation, or mucking around with owners of individual tables or objects which adds administrative overhead.  I wanted to highlight these options as potential ways people can get around this problem.  I think ETL jobs are the only use case I will have that present this problem as it is rare that I would ever have an application user with DDL needs.  Normally DDL changes are handled through a deployment process of some kind, which is done by the admin/deploy or super user and these problems do not present themselves.

Write a Comment