CloudSQL Postgres confused around table grants and owners

Question

I'm using CloudSQL Postgres 11 on GCP, and I've got a few questions around permissions and grants. I'm just not getting it at the moment, being very new to postgres.

I have a user, pgadmin which is a superuser. With this user I can connect to the instance and create a database called 'sandbox' for example.

I then have an app role which is defined as follows:

CREATE ROLE app;
GRANT CONNECT ON DATABASE <database name> TO app;
GRANT USAGE, CREATE ON SCHEMA public TO app;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app;

I create a user called app_sandbox which I grant this role.

The app user then makes a db migration using flyway which creates 2 tables. That side of things is fine.

But with my superuser, pgadmin, I can't see these tables or query them even though this user owns the database and is a superuser. I've tried all sorts of grants. Feels like I'm missing something important because even if I true to create a readonly role with the pgadmin user I am unable to grant access to the underlying tables in the public schema of the database.

What am I missing?

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2019-10-11T12:44:09

Just because one user (=pgadmin) owns the database, does not mean that user also owns the tables created in that database. And because pgadmin doesn't own those tables, you can't access them when logged in as pgadmin

If the app user created the tables, they belong to that user, and only the app user can grant privileges on those tables to other users.

CloudSQL Postgres confused around table grants and owners

1 Answers