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.

Sign up for GitHub

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

Jump to bottom

Correct user should own postgresql tables as well as databases #3286

Open
acozine opened this issue Aug 26, 2022 · 4 comments · May be fixed by #3334
Open

Correct user should own postgresql tables as well as databases #3286

acozine opened this issue Aug 26, 2022 · 4 comments · May be fixed by #3334
Labels
cross-team Affects cross-team projects

Comments

@acozine
Copy link
Contributor

acozine commented Aug 26, 2022

Related to #3280.

After migrating the staging database, we saw this error when attempting to deploy. Short form of error: ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied for table schema_migrations.

I looked at the new postgres host and found that , turned out that the pulmap_staging database was owned by the pulmap_staging user, but the tables inside that database were owned by the postgres user:

pulmap_staging=# \dt
                     List of relations
 Schema |              Name                            | Type     |  Owner   
--------+--------------------------------+-------+----------
 public | active_storage_attachments       | table | postgres
 public | active_storage_blobs                  | table | postgres
 public | active_storage_variant_records | table | postgres
 public | ar_internal_metadata                  | table | postgres
 public | bookmarks                                   | table | postgres
 public | schema_migrations                     | table | postgres
 public | searches                                      | table | postgres
 public | sidecar_image_transitions          | table | postgres
 public | solr_document_sidecars             | table | postgres
 public | users                                            | table | postgres
(10 rows)

I manually fixed the tables with:

$ for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" pulmap_staging` ; do  psql -c "alter table \"$tbl\" owner to pulmap_staging" pulmap_staging ; done

so pulmap-staging is back up. We should find the root cause of this problem. It might be in the postgresql role, or in the database migration playbook, or both.
@acozine
Copy link
Contributor Author

acozine commented Aug 29, 2022 •
edited
Loading

If the tables are the only ones owned by the old user name, AND THE OLD USER NAME IS NOT THE postgres USER, you can also do REASSIGN OWNED BY olduser TO newuser;.

Don't ever do this with the postgres user, though, as that could change the ownership of vital administrative resources.

@hackartisan
Copy link
Member

related to #3231?

@jrgriffiniii
Copy link
Contributor

This was also the case with the oawaiver-prod1, as described within the following issue: pulibrary/oawaiver#83

@jrgriffiniii jrgriffiniii mentioned this issue Sep 9, 2022
Closed
2 tasks
@acozine
Copy link
Contributor Author

acozine commented Sep 9, 2022

@kayiwa @leefaisonr and I looked at this today. We think it's related to the task in the postgresql role that changes the db owner. Currently that task uses the postgresql_db module, but we may need to use the postgresql_owner module instead.

@acozine acozine linked a pull request Sep 19, 2022 that will close this issue
Draft
@acozine acozine added the cross-team Affects cross-team projects label Nov 8, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
cross-team Affects cross-team projects
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

testing a new approach to psql table ownership pulibrary/princeton_ansible
3 participants
@hackartisan @acozine @jrgriffiniii