correct way to create readonly role

[y0zg]

Hi there,

I'm about to assign proper readonly permission for user in public schema.

After reading this page https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ and checking https://github.com/cyrilgdn/terraform-provider-postgresql/issues/33#issuecomment-757512759 I expected to configure everything as planned, but after some simple testing I see that role isn't assigned properly.

Basically I'd like to apply the following config:

CREATE ROLE test_readonly;
GRANT CONNECT ON DATABASE test TO test_readonly;
GRANT USAGE ON SCHEMA public TO test_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_readonly;

Terraform Version

Terraform v0.13.6

Terraform Configuration Files

Database is created via aws module https://registry.terraform.io/modules/terraform-aws-modules/rds/aws/latest

module "test-db" {
  source = "../../../external_modules/terraform-aws-modules/terraform-aws-rds"

  identifier = "test-db"

  engine            = "postgres"
  engine_version    = "13.1"
  instance_class    = "db.t3.small"
  allocated_storage = 10
  storage_encrypted = false

  name     = "test"
  username = "test"

  password = aws_ssm_parameter.test_db_password.value
  port     = "5432"

  # disable backups to create DB faster
  backup_retention_period = 0

  // only this log types available on postgres 13.1
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]

  # DB subnet group
  multi_az   = false
  subnet_ids = module.vpc-demo-eu-west-2.public_subnets

  # DB parameter group
  family = "postgres13"

  # DB option group
  major_engine_version = "13"

  # Snapshot name upon DB deletion
  final_snapshot_identifier = "${var.environment}-${var.region}-test-db"

  # Database Deletion Protection
  deletion_protection = false

  performance_insights_enabled          = false
  performance_insights_retention_period = 0

  iam_database_authentication_enabled = true

  tags = merge(
    local.tags,
  )
}

output "test-db-endpoint" {
  value = module.test-db.this_db_instance_address
}

resource "postgresql_role" "test_readonly" {
  name     = "test_readonly"
  login    = true
  password = var.test_db_password
}

resource "postgresql_grant" "grant_ro_sequence" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

resource "postgresql_grant" "grant_ro_tables" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_tables" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_sequence" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

When I run below query, I don't see any roles assigned after terraform apply.

SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

it takes effect only if I grant permissions manually via GRANT readonly TO test_readonly;

Debug Output

Please provider a link to a GitHub Gist containing the complete debug output: https://www.terraform.io/docs/internals/debugging.html. Please do NOT paste the debug output in the issue; just paste a link to the Gist.

Expected Behavior

What should have happened?

Role should be assigned with appropriate permissions

Actual Behavior

What actually happened?

Role isn't assigned to user

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

[y0zg]

That's said, I can login with user test_readonly but it doesn't have required role

I also tried to add this part but it didn't help to revoke default public permissions

resource "postgresql_grant" "revoke_public" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "schema"
  privileges  = []

  with_grant_option = true
}

[cyrilgdn]

Hi @y0zg,

I'm not sure the example code you put in the issue is complete because regarding:

When I run below query, I don't see any roles assigned after terraform apply. it takes effect only if I grant permissions manually via GRANT readonly TO test_readonly;

I don't see any reference of readonly role in:

resource "postgresql_role" "test_readonly" {
  name     = "test_readonly"
  login    = true
  password = var.test_db_password
}

resource "postgresql_grant" "grant_ro_sequence" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

resource "postgresql_grant" "grant_ro_tables" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_tables" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_sequence" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

About this Terraform code, I can see a potential problem though. You didn't set the dependencies between resources so first apply may not properly set the grant/default privileges if they are created before the associated role.

It reproduced your example but with the right dependencies between resources here: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/604f1f7031d00a07e7f12534f0c8f3100c64e21e/examples/issues/75/test.tf#L28-L74

And after applying this code, it correctly created the default privileges:

 psql -h xxx -U postgres -d test -c "\ddp"
             Default access privileges
 Owner | Schema |   Type   |   Access privileges   
-------+--------+----------+-----------------------
 test  | public | sequence | test_readonly=rU/test
 test  | public | table    | test_readonly=r/test
(2 rows)

And if create a table in the test database:

$ psql -h xxx -U postgres -d test -c "create table test (id serial);"
CREATE TABLE

And apply again it will grant the privileges to the role:

$ terraform apply
postgresql_database.test: Refreshing state... [id=test]
postgresql_role.test: Refreshing state... [id=test]
postgresql_role.test_readonly: Refreshing state... [id=test_readonly]
postgresql_grant.grant_ro_tables: Refreshing state... [id=test_readonly_test_public_table]
postgresql_grant.grant_ro_sequence: Refreshing state... [id=test_readonly_test_public_sequence]
postgresql_default_privileges.alter_ro_sequence: Refreshing state... [id=test_readonly_test_public_test_sequence]
postgresql_default_privileges.alter_ro_tables: Refreshing state... [id=test_readonly_test_public_test_table]

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # postgresql_grant.grant_ro_sequence will be updated in-place
  ~ resource "postgresql_grant" "grant_ro_sequence" {
        id                = "test_readonly_test_public_sequence"
      ~ privileges        = [
          + "SELECT",
          + "USAGE",
        ]
        # (5 unchanged attributes hidden)
    }

  # postgresql_grant.grant_ro_tables will be updated in-place
  ~ resource "postgresql_grant" "grant_ro_tables" {
        id                = "test_readonly_test_public_table"
      ~ privileges        = [
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)
    }

Plan: 0 to add, 2 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

postgresql_grant.grant_ro_tables: Modifying... [id=test_readonly_test_public_table]
postgresql_grant.grant_ro_sequence: Modifying... [id=test_readonly_test_public_sequence]
postgresql_grant.grant_ro_tables: Modifications complete after 1s [id=test_readonly_test_public_table]
postgresql_grant.grant_ro_sequence: Modifications complete after 1s [id=test_readonly_test_public_sequence]

Apply complete! Resources: 0 added, 2 changed, 0 destroyed.

And I can connect as test_readonly to verify it has read privileges

$ psql -h xxx -U test_readonly -d test -c "select count(*) from test"
 count 
-------
     0
(1 row)

[cyrilgdn]

I also tried to add this part but it didn't help to revoke default public permissions

And about this point, you actually to revoke these privileges from public role which is a special group that includes all roles.

See in my example:

https://github.com/cyrilgdn/terraform-provider-postgresql/blob/604f1f7031d00a07e7f12534f0c8f3100c64e21e/examples/issues/75/test.tf#L76-L84

[y0zg]

hi @cyrilgdn ! Thanks for the prompt reply! I amended the config a bit and can see the role is there

test=> \ddp
               Default access privileges
 Owner | Schema |   Type   |     Access privileges
-------+--------+----------+----------------------------
 test  | public | sequence | test_readonly_role=rU/test
 test  | public | table    | test_readonly_role=r/test
(2 rows)

but still wonder why user test_readonly_user can create tables in public schema

I believe this step is missing REVOKE CREATE ON SCHEMA public FROM PUBLIC;

# ROLE
resource "postgresql_role" "role_ro" {
  name                = "test_readonly_role"
  login               = false
  superuser           = false
  inherit             = true
  create_database     = false
  create_role         = false
  replication         = false
  skip_reassign_owned = true
}

# USER
resource "postgresql_role" "user_ro" {
  name                = "test_readonly_user"
  login               = true
  superuser           = false
  create_database     = false
  create_role         = false
  inherit             = true
  replication         = false
  connection_limit    = -1
  skip_reassign_owned = true
  password            = "password"
  roles               = [postgresql_role.role_ro.name]
  depends_on          = [postgresql_role.role_ro]
}

resource "postgresql_role" "additional_user_ro" {
  name                = "test_readonly_user2"
  login               = true
  superuser           = false
  create_database     = false
  create_role         = false
  inherit             = true
  replication         = false
  connection_limit    = -1
  skip_reassign_owned = true
  password            = "password"
  roles               = [postgresql_role.role_ro.name]
  depends_on          = [postgresql_role.role_ro]
}

resource "postgresql_grant" "grant_ro_tables" {
  database    = "test"
  role        = "test_readonly_role"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
  depends_on  = [postgresql_role.role_ro]
}
resource "postgresql_grant" "grant_ro_sequence" {
  database    = "test"
  role        = "test_readonly_role"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
  depends_on  = [postgresql_role.role_ro]
}

resource "postgresql_default_privileges" "alter_ro_tables" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly_role"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
  depends_on  = [postgresql_role.role_ro]
}
resource "postgresql_default_privileges" "alter_ro_sequence" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly_role"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
  depends_on  = [postgresql_role.role_ro]
}

resource "postgresql_grant" "revoke_public" { 
   database    = "test"
   role        = postgresql_role.role_ro.name
   schema      = "public" 
   object_type = "schema" 
   privileges  = [] 

   with_grant_option = true 
 } 

[y0zg]

Also with above config user test_readonly_user2 cannot do SELECT on tables created by user test_readonly_user despite they are both belong to the same role, probably I'm missing something fundamental, but with SQL commands this works for me

[y0zg]

@cyrilgdn please let me know what are your thoughts regarding this :)

[alec-rabold]

@y0zg We ran into this same issue and the fix for us was specifying the template when creating the database:

resource "postgresql_database" "database" {
  name      = var.name
  owner     = var.db_owner
  template = "template1"
}

It appears this provider defaults to template0 which creates some public schema ownership issues according to this AWS support article.

Although I think there’s two separate issues here:

1. This provider creates databases from template0 by default, whereas the CREATE DATABASE statement uses template1 by default (see docs here). There might be some complexities there that I'm not aware of.
2. The grant resources are silently failing with this message WARNING: no privileges were granted for "public”. It seems that, in order to alter public permissions you need to use template1 and the master-user role (or transfer ownership to another user). Maybe the provider could error when it sees this WARNING message.

Hopefully that helps!

[cyrilgdn]

@y0zg Sorry to have been silenced, I was a bit off the project for a while.

@alec-rabold Thanks for your help, indeed the provider uses template0 by default and to be honest I don't know why (it was the case before I started to work on this provider).

In RDS, the owner of template0's public schema is rdsamin so we don't have the privileges to alter the schema privileges where the owner of template1's public schema is postgres (actually the admin username you set at the cluster creation).

You should always set template="template1" for RDS.

I'm thinking changing this default value but it's an important breaking change (and will force recreate resources unless if not specified unless we set it to compute) so I'm not sure yet.

@y0zg Did you manage to solve your problem with this solution?

[y0zg]

Sorry, I didn't have a time to test, but I believe your last comment should solve the issue, so closing this