Support for policies on schemas without creating the schema

[cmclaughlin]

Not sure if this is a usage question or a feature request...

Ultimately I'm trying to grant access to a non-superuser to read from the pg_cron job detail tables in an RDS database. I checked with AWS and their first suggestion was to grant superuser access. I don't want to do that. They followed up with this suggestion, which I'm trying to implment via this provider...

CREATE POLICY ro_user ON cron.job_run_details
FOR SELECT
TO usro
USING (TRUE);

grant usage on schema cron to usro;
grant select on cron.job_run_details usro;

So I tried to use the postgresql_schema with a policy block... but I don't see any way to specify the tables.

I also noticed "Argument is deprecated"...

Here are more details...

Terraform Version

Terraform v1.0.11 cdktf 0.9.0 cdktf-cdktf-provider-postgresql 0.3.58

Affected Resource(s)

postgresql_schema

Terraform Configuration Files

I'm using cdktf, so this is an example in Python:

       Schema(
            self,
            "cron-schema",
            name="pg_cron",
            database="mydatabase",
            policy=[
                SchemaPolicy(
                    create=False,
                    create_with_grant=False,
                    usage=True,
                    role="reader_role",
                )
            ]
        )

But the Schema resource does not seem to support parts of the policy such as... "FOR SELECT TO ... USING (TRUE)"

Any ideas?

[cyrilgdn]

Hi @cmclaughlin ,

The policy block in postgresql_schema is actually deprecated and doesn't represent a real policy in Postgres but just grants permissions. As you use policy with using(true) it seems it's what you want to achieve ?

For that you can use the postgresql_grant resource with object_type set to schema: https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs/resources/postgresql_grant Thanks to that you can grant permissions on an existing schema.

I close this issue but don't hesitate to reopen it if I haven't fully answered to your question.