Creating a read only role for IAM users in AlloyDB

[IDWalsh]

Hi,

I'm struggling to create a read only role that works with an IAM user on AlloyDB. For context there are 2 service account IAM users from different services. One with Read/Write and one with Read Only.

I'll share the read only because it's the one not working. 

CREATE ROLE read_only WITH
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE;

GRANT CONNECT ON DATABASE service TO read_only;

Switch over to the database service.

GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only;

None of which error at any point.

Except it just gets permission denied trying to do any kind of select.

I ran the GRANTs as our `alloydbsuperuser` the tables were created by the read/write service though as it has the SQL migration files. So it's the owner of the tables, but that shouldn't matter as the above grants ought to work.
Then in alloydb studio as the superuser this will also fail

GRANT read_only TO admin;

SET role read_only;
SELECT * FROM my_table;

With the last 2 lines running in a transaction. With the fairly bland error:

permission denied for table my_table (SQLSTATE 42501)

Any help would be greatly appreciated.

[nmagcalengjr · Accepted solution]

Hi @IDWalsh,

Welcome to Google Cloud Community!

The issue is likely caused by ownership and default privileges not being applied retroactively or to the appropriate role. Since the tables were created by the read-write service account, You have to explicitly GRANT SELECT on the existing tables, and possibly do it as the owner (which is your Read/Write role or user), not as alloydbsuperuser.

Even though alloydbsuperuser is a superuser, it does not automatically own tables created by other roles, and can’t grant access to objects it doesn't own.

Here's a breakdown of how to fix it:

1. Switch to the Read/Write role (the one that owns the tables):

SET ROLE read_write_user;  -- or login as that user

1. Grant SELECT to the read_only role on all tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

1. Optionally, update future privileges (run as the table owner):

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

You can now test to confirm that access is working as expected. The operation should succeed if everything is set up correctly.

SET ROLE read_only;

SELECT * FROM my_table;

You can also refer to these documentations, which might help for future reference.

• IAM roles and permissions for AlloyDB
• Connect using an IAM account
• Manage PostgreSQL users with standard authentication

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[IDWalsh]

Hi @nmagcalengjr Yep this turned out to be the solution.

We eventually worked it out from this note in the postgres manual: https://www.postgresql.org/docs/current/sql-grant.html

When a non-owner of an object attempts to GRANT privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options. The GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)

E.g. the GRANTs will look like they're succeeding but not actually do anything.

So it turned out automating migration files also needed to be setting the owner of the tables if it was going to be an IAM user as there was no way to grant the IAM user role to the admin inside AlloyDB studio without breaking the login entirely.