Vault postgres role grants not getting applied for read-only in other schemas

[wesbragagt]

Hey, I'm facing an issue in my pg cluster where if I connect to a reader only role using pf-db-tunnel I'm not able to select tables on the other schemas even though I'm passing them to the module. Although I'm on version edge.24-08-24 I've copied the kube_pg_cluster module from main since release 24-08-27.

I tried

-- check what role_name is assigned
SELECT r.rolname AS role_name, 
       m.rolname AS member_of
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE m.rolname = 'v-oidc-bc4-reader-i-qSgcgE49SnPe9xpjAxIO-1725475212';

-- check what schemas that role has access
SELECT n.nspname AS schema_name,
       has_schema_privilege('reader', n.nspname, 'USAGE') AS can_usage,
       has_schema_privilege('reader', n.nspname, 'CREATE') AS can_create
FROM pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema'); 

And I found the output of the second query to show that the reader role does not have access to the extra schemas I've specified:

schema_name|can_usage|can_create|
-----------+---------+----------+
pg_toast   |false    |false     |
public     |true     |false     |
client     |false    |false     |
cdm        |false    |false     |

I was digging through the code and was wondering if it's appropriate for this line to have:

# allow pgbouncer to access schemas
            [
              for schema in local.all_schemas : "GRANT USAGE ON SCHEMA ${schema} TO cnpg_pooler_pgbouncer;"
            ],

instead this https://github.com/Panfactum/stack/blob/f28827d988f6f90bd7dadf33a966a64e6ece77ed/packages/infrastructure/kube_pg_cluster/main.tf#L483

Do I have the correct assumption?

[fullykubed]

@wesbragagt Can you log into the vault UI and navigate to this screen to show the creation statement for one of the postgres roles?

[fullykubed]

Also, I just want to ensure you are connecting to the app not the postgres database.

[wesbragagt]

@fullykubed Yes I'm connecting to the app database and this is the creation statement I extracted from that screen:

GRANT SELECT ON ALL TABLES IN SCHEMA cdm TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA cdm TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cdm TO reader;,GRANT SELECT ON ALL TABLES IN SCHEMA client TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA client TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA client TO reader;,GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO reader;,CREATE ROLE "{{name}}" LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';,GRANT reader TO "{{name}}";

[fullykubed]

@wesbragagt are you able to connect using the superuser role from vault?

[wesbragagt]

@fullykubed yes, I am able to connect with superuser and perform read/write operations on the schemas I've specified as extra_schemas.

[wesbragagt]

@fullykubed Interesting enough, I went and checked vault.dev to see if the creation statement matches as I was able to perform select operations with the read-only role.

GRANT SELECT ON ALL TABLES IN SCHEMA cdm TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA cdm TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cdm TO reader;,GRANT SELECT ON ALL TABLES IN SCHEMA client TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA client TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA client TO reader;,GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;,GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reader;,GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO reader;,CREATE ROLE "{{name}}" LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';,GRANT reader TO "{{name}}";

[wesbragagt]

@fullykubed I know why it's in the state. In dev I ended up applying those grants to the reader role manually. I just did the same thing in prod and now my reader-only rows can select through those tables. Could the extra_schema grants not being applied upon cluster restarts?

[fullykubed]

I am going to investigate more and attempt a fix tomorrow.

[fullykubed]

Found the issue and will be fixed in the next release.