Grant pg_read_server_files without superuser

[siwon]

Hi,

I get an error when I try to add a user to the group role pg_read_server_files without superuser access.
When I run the command GRANT "username" TO pg_read_server_files; manually, there is no problem but when I use postgresql_role I have a permission denied error.
In both case I use the same user to connect to the database which is not superuser.

Terraform Version

Terraform v0.12.24

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  version = "~> 1.6"

  host            = *********************
  port            = *********************
  database        = *********************
  username        = *********************
  password        = *********************
  sslmode         = "require"
  expected_version = *********************
  connect_timeout = *********************
  superuser = false
}


resource "postgresql_role" "user" {
  name     = "user_name"
  login    = true
  password = "password"
  
  roles = [
    "pg_read_server_files"
  ]
}

Debug Output

Error: could not grant role pg_read_server_files to username: pq: only superusers can grant role "pg_read_server_files"

Expected Behavior

It should add the user to the group role pg_read_server_files.

Actual Behavior

I have a permission denied error.

Steps to Reproduce

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

1. terraform apply

Important Factoids

This scenario is running on Azure PostgreSQL database so I don't have access to superuser account...

Thanks for your help.

[cyrilgdn]

Hi @siwon ,

Thanks for opening this issue.

I'm a bit surprised that Azure allows to grant pg_read_server_files role if admin user is not a superuser as it bypasses the database-level permissions but I never used Azure so I need to check this behavior.

But before that, could you just clarify one thing:

You said that, manually, you run:

GRANT "username" TO pg_read_server_files;

But I guess you meant:

GRANT pg_read_server_files TO username;

Because this is a equivalent of:

resource "postgresql_role" "username" {
  name     = "username"
  [...]
 
  roles = [
    "pg_read_server_files"
  ]
}

(and the first one does not really make sense).

Thanks in advance for your response

[siwon]

Hi @cyrilgdn and thank you for your answer,

I was surprised too but the command GRANT "username" TO pg_read_server_files; was the command I ran. And it works !

I "discovered" this command by using pgAdmin after I add the user to the pg_read_server_files memberships and retrieve the "create script" of this group role.

Here is the generated "create script" from pgAdmin :

-- Role: pg_read_server_files
-- DROP ROLE pg_read_server_files;

CREATE ROLE pg_read_server_files WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

GRANT "username" TO pg_read_server_files;

On the other side, the username's membership is still empty...

Thanks again for your help !