PostgreSQL – Troubleshooting Role Permission Problems

postgresql

I have a role called readwrite, created by a user called postgres. This role has following grants applied:

GRANT CONNECT ON DATABASE confere TO readwrite;
GRANT USAGE, CREATE ON SCHEMA airflow_staging TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT USAGE ON SEQUENCES TO readwrite;

After that I created a user called confere and assigned it to readwrite role:

create user confere with password '...'
grant readwrite to confere

When I run a create table statement with postgres user, confere select it without any problem:

--as postgres
create table airflow_staging.test (a text)

--as confere
select * from airflow_staging.test
a|
-|

But when I create a table with confere user, postgres cannot see its content because it gets a owner error:

--as confere
create table airflow_staging.test_2 (a text)

--as postgres
select * from airflow_staging.test_2
SQL Error [42501]: ERROR: permission denied for table teste_2

Why? Is there a way for postgres user to see tables created by confere?

Obs.: This is not a PostgreSQL superuser, I'm running Google Cloud SQL and Postgres 11

Best Answer

Your ALTER DEFAULT PRIVILEGES doesn't grant to "postgres" (unless "postgres" is also a member of "readwrite") and only grants for tables created by "postgres" (assuming you were logged on as "postgres" at the time you ran it).

Execute the ALTER DEFAULT PRIVILEGES again, this time while logged in as "confere". And either grant "readwrite" to "postgres", or change the TO part of the ALTER DEFAULT PRIVILEGES.

Related Question