I am setting up dynamic credentials for database that has schemas. Each schema has a table in it and the requirement is that when connecting to the database, user that has permissions to access table from one schema, cannot access table from another schema.

The documentation and examples I’ve seen deal with enforcing policies per database, how can I do the same for tables in schemas under the same database?

I created a role and configuration, after which new credentials were created each time I read new credentials. However, I am not sure how this should be extended for schemas. I tried the following script, but it does not work:

vault write -f database/roles/$1 
      db_name="esdcertspoc" 
      creation_statements="CREATE USER '{{name}}' WITH SUPERUSER ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}};"
              "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA $1 TO '{{name}}';"
      default_ttl=1h 
      max_ttl=24h