postgresql_default_privileges.html.markdown

layout	page_title	sidebar_current	description
postgresql	PostgreSQL: postgresql_default_privileges	docs-postgresql-resource-postgresql_default_privileges	Creates and manages default privileges given to a user for a database schema.

postgresql_default_privileges

The postgresql_default_privileges resource creates and manages default privileges given to a user for a database schema.

~> Note: This resource needs Postgresql version 9 or above.

Usage

resource "postgresql_default_privileges" "read_only_tables" {
  role     = "test_role"
  database = "test_db"
  schema   = "public"

  owner             = "db_owner"
  object_type       = "table"
  privileges        = ["SELECT"]
  with_grant_option = false
}

Argument Reference

• role - (Required) The role that will automatically be granted the specified privileges on new objects created by the owner.
• database - (Required) The database to grant default privileges for this role.
• owner - (Required) Specifies the role that creates objects for which the default privileges will be applied.
• schema - (Optional) The database schema to set default privileges for this role.
• object_type - (Required) The PostgreSQL object type to set the default privileges on (one of: table, sequence, function, type, schema).
• privileges - (Required) List of privileges (e.g., SELECT, INSERT, UPDATE, DELETE) to grant on new objects created by the owner. An empty list could be provided to revoke all default privileges for this role.
• with_grant_option - (Optional) Whether the role will be able to grant the specified privileges to others.

Examples

Grant default privileges for tables to "current_role" role:

resource "postgresql_default_privileges" "grant_table_privileges" {
  database = postgresql_database.example_db.name
  role     = "current_role"
  owner    = "owner_role"

  schema            = "public"
  object_type       = "table"
  privileges        = ["SELECT", "INSERT", "UPDATE"]
  with_grant_option = true
}

Whenever the owner_role creates a new table in the public schema, the current_role is automatically granted SELECT, INSERT, and UPDATE privileges on that table, and the current_role can grant these privileges to other roles.

Revoke default privileges for functions for "public" role:

resource "postgresql_default_privileges" "revoke_public" {
  database    = postgresql_database.example_db.name
  role        = "public"
  owner       = "object_owner"
  object_type = "function"
  privileges  = []
}