AWS RDS pg_dump ERROR LOCK TABLE IN ACCESS SHARE MODE for rds_superuser
AWS RDS has all the PostgreSQL versions. But still, a lot of companies are running their database on PostgreSQL 9.6. Recently I was working on a migration project where I need to dump and restore the PostgreSQL database to a different server. You may already hear this issue and you might be solved it. But I didn’t any SO questions or any blogs. So I wanted to write one for the Non-DBAs. So they can get benefit from this.
In RDS we can’t create/grant native postgresql’s
super_user privilege instead we can grant
rds_superuser. But its not a super user. There is a good article from 2ndquadrant.
In AWS RDS PostgreSQL (v 9.6 or lesser than 9.6), the rds_superuser can’t read/write the on the tables which are owned by a different user. To perform read and write the table owner needs to grant the privilege to the rds_superuser’s separately. This is fully right?
A lot of people already aware of this, but how about pg_dump? thats what we are going to reproduce.
Reproduce the issue:
I have spun up an RDS PostgreSQL 9.6 to reproduce this issue. RDS Master user name: postgres Password: postgres
psql -h psql -h bhuvi-pg-96.chbcar19iy5o.us-east-1.rds.amazonaws.com -U postgres -d testdb testdb=> create user appuser with password 'apppassword'; testdb=> grant CONNECT on DATABASE testdb to appuser ; testdb=> grant all on SCHEMA public to appuser ; -- exit testdb=>\q
Now login to the DB server with the
appuser and create a sample table.
psql -h bhuvi-pg-96.chbcar19iy5o.us-east-1.rds.amazonaws.com -U appuser -d testdb testdb=> create table numbers (num int); testdb=> insert into numbers values (1),(2); testdb=> \d+ List of relations Schema | Name | Type | Owner | Size | --------+---------+-------+---------+------------+- public | numbers | table | appuser | 8192 bytes |
Lets take the dump with the master user(part of rds_superuser role)
pg_dump -h bhuvi-pg-96.chbcar19iy5o.us-east-1.rds.amazonaws.com -U postgres -d testdb > dump.sql pg_dump: error: query failed: ERROR: permission denied for relation numbers pg_dump: error: query was: LOCK TABLE public.numbers IN ACCESS SHARE MODE
What is the Root cause?
As I explained from the beginning, the RDS master user has the
rds_superuser role, but its not the real super user. So it cannot read or write on the tables that are owned by a different user.
To solve this issue, the table owner must be granted the select(and other access) to the RDS master user. Then we can take the dump.
psql -h bhuvi-pg-96.chbcar19iy5o.us-east-1.rds.amazonaws.com -U appuser -d testdb grant SELECT on public.numbers to postgres ; -- Now take the dump with Postgres user: pg_dump -h bhuvi-pg-96.chbcar19iy5o.us-east-1.rds.amazonaws.com -U postgres -d testdb > dump.sql
If you have a tons of tables in your database, it may difficult to do the grant one by one. So you can use the following script to generate the grant statement and execute.
Lets say I have 4 tables with different owners.
testdb=> \d+ List of relations Schema | Name | Type | Owner | Size | --------+---------------+-------+-----------+------------+ public | analytics_tbl | table | analytics | 8192 bytes | public | dba_tbl | table | dba | 8192 bytes | public | devops_tbl | table | devops | 8192 bytes | public | numbers | table | appuser | 8192 bytes |
pgpass file to skip the password prompt.
vi ~/.pgpass bhuvi-rdsendpoint.rds.amazonaws.com:5432:testdb:appuser:apppassword bhuvi-rdsendpoint.rds.amazonaws.com:5432:testdb:dba:dba bhuvi-rdsendpoint.rds.amazonaws.com:5432:testdb:analytics:analytics bhuvi-rdsendpoint.rds.amazonaws.com:5432:testdb:devops:devops chmod 600 ~/.pgpass
Create a shell script to generate the grant and import the grants.
#/bin/bash users='appuser analytics dba devops' rds_endpoint='your-rds-endpoint' db_name='testdb' dump_user='your-dump-user-name' for user in $users do psql -h $rds_endpoint -d $db_name -U $user -t -c"select 'grant select on '||schemaname||'.'||tablename||' to $dump_user' from pg_tables where tableowner=current_user;" > /tmp/$user-grants.sql psql -h $rds_endpoint -d $db_name -U $user < /tmp/$user-grants.sql done
We found this issue on RDS PostgreSQL 9.6 and lesser than that. But it is fixed on 10,11 and 12. Even though it is a very older version, but may companies are still running this. So it would be great that AWS fix it from their end.