-
Bug
-
Resolution: Cannot Reproduce
-
Medium (View bug fix roadmap)
-
None
-
8.5.0, 8.13.0
-
8.05
-
1
-
Severity 2 - Major
-
7
-
Issue Summary
This ticket is used to track a bug on RDS - after Postgres 10 upgrade, Jira may fail to start.
When ugprading to Postgres 10 on AWS RDS, Jira can't start.
It's because after upgrade, public schema lost access privileges.
Steps to Reproduce
- Connect to postgres sever (psql)
- connect to jira database by \c <jiradb>
- list out schemas \dn+
Expected Results
- rdsadmin user has access privileges
List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 row)
Actual Results
- access privileges are lost.
List of schemas Name | Owner | Access privileges | Description --------+----------+-------------------+------------------------ public | rdsadmin | | standard public schema (1 row)
Workaround
Grant schema access to confluence user.
confluence=> ALTER SCHEMA public OWNER to <jira_db_user>; ALTER SCHEMA confluence=> GRANT ALL ON SCHEMA public TO <jira_db_user>; GRANT
- is cloned from
-
-
- Long Term Backlog
-
[JRASERVER-72246] Postgres upgrade on AWS RDS Jira can't find tables
| Resolution | New: Cannot Reproduce [ 5 ] | |
| Status | Original: Gathering Impact [ 12072 ] | New: Closed [ 6 ] |
I tried to reproduce this issue:
- run RDS on 9.6.20, use psql console to check the permissions on schema:
psql (13.3 (Debian 13.3-1.pgdg100+1), server 9.6.20) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. atldb=> \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 row)
- upgrade RDS to 10.17, use the console to check the permissions:
psql (13.3 (Debian 13.3-1.pgdg100+1), server 10.17) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. atldb=> \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 row)
Which is an expected result and proves that the issue on AWS side was fixed. I'm closing the ticket.
| Description |
Original:
h3. Issue Summary
This ticket is used to track a bug on RDS - after Postgres 10 upgrade, Jira may fail to start. [https://dba.stackexchange.com/questions/237359/aws-rds-postgresql-master-user-lost-all-privileges-after-upgrading-from-v9-6] When ugprading to Postgres 10 on AWS RDS, Jira can't start. It's because after upgrade, public schema lost access privileges. h3. Steps to Reproduce # Connect to postgres sever (psql) # connect to jira database by \c <jiradb> # list out schemas \dn+ h3. Expected Results # rdsadmin user has access privileges {code:java} List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 row) {code} h3. Actual Results # access privileges are lost. {code:java} List of schemas Name | Owner | Access privileges | Description --------+----------+-------------------+------------------------ public | rdsadmin | | standard public schema (1 row) {code} h3. Workaround Grant schema access to confluence user. {code:java} confluence=> ALTER SCHEMA public OWNER to <jira_db_user>; ALTER SCHEMA confluence=> GRANT ALL ON SCHEMA public TO <jira_db_user>; GRANT {code} |
New:
h3. Issue Summary
This ticket is used to track a bug on RDS - after Postgres 10 upgrade, Jira may fail to start. [https://dba.stackexchange.com/questions/237359/aws-rds-postgresql-master-user-lost-all-privileges-after-upgrading-from-v9-6] When ugprading to Postgres 10 on AWS RDS, Jira can't start. It's because after upgrade, public schema lost access privileges. h3. Steps to Reproduce # Connect to postgres sever (psql) # connect to jira database by \c <jiradb> # list out schemas \dn+ h3. Expected Results # rdsadmin user has access privileges {code:java} List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 row) {code} h3. Actual Results # access privileges are lost. {code:java} List of schemas Name | Owner | Access privileges | Description --------+----------+-------------------+------------------------ public | rdsadmin | | standard public schema (1 row) {code} h3. Workaround Grant schema access to confluence user. {code:java} confluence=> ALTER SCHEMA public OWNER to <jira_db_user>; ALTER SCHEMA confluence=> GRANT ALL ON SCHEMA public TO <jira_db_user>; GRANT {code} |
| UIS | Original: 8 | New: 7 |
| Support reference count | Original: 2 | New: 1 |
| Remote Link | Original: This issue links to "Page (Confluence)" [ 543352 ] |
| UIS | Original: 1 | New: 8 |
| UIS | New: 1 |
| Support reference count | Original: 1 | New: 2 |
This issue still exists in PostgreSQL 12.2 , AWS RDS. With public schema has lost privileges and workaround fixed the issue.