0

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

1

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.

2
  • Thanks! So if I want that all tables became selectable by readonly members, I have to run ALTER DEFAULT PRIVILEGES for each new user created? Dec 15 '19 at 22:19
  • @BrennoFlávio yes, I believe that that is the case. An alternative is to do it only for the 'readwrite' role, and then make each user do a "set role readwrite" before they create objects.
    – jjanes
    Dec 16 '19 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.