[bug] Default privileges not applied

[gcaracuel]

Problem:

Default privileges were not being applied to objects created by owner and writer roles, causing permission errors when accessing newly created tables.

I am not super sure but have been facing this issue for months. I do use a postgres super user at the operator then the OWNER user (group role) creates new tables so WRITER users are supposed to be privileged to but they are not. Owner groups are set properly so the OWNER users have no problems but WRITER and READERS are impacted.

Root cause:

The operator was executing ALTER DEFAULT PRIVILEGES commands without the FOR ROLE clause:
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO "mydb-reader";

When FOR ROLE is not specified, PostgreSQL only applies default privileges to objects created by the current user executing the command (in this case, the postgres superuser that the operator connects with).

However, when users are provisioned by the operator:

1. A user role is created (e.g., myapp-abc123) with LOGIN privilege
2. The user is granted membership in a group role (e.g., mydb-group for owner, mydb-writer for writer, mydb-reader for reader)
3. The operator executes ALTER USER "myapp-abc123" SET ROLE "mydb-group" (see postgresuser_controller.go:158)
4. When this user logs in and creates a table, the table is owned by the group role (mydb-group), not by the postgres superuser
   Result: Default privileges don't apply to these newly created tables, causing writer and reader roles to have no access.

Steps to reproduce:

1. Use a non operator managed user as the user in the operator I.E. postgres
2. Operator creates database with owner/writer/reader roles
3. User with owner privileges creates a table
4. User with writer privileges cannot access the table (permission denied)
5. Same applies to reader users