CODEWITHSUNDEEP
postgresql
Niel de Wet
Niel de Wet

Reputation: 8408

Postgres: GRANT to user based on sub-query

This is a symptom of database and user names being different between my dev/staging/live environments, but is there a way to GRANT permissions to a user, determined by some kind of sub-query?

Something like this (not valid syntax):

GRANT UPDATE (my_column) ON my_table TO (SELECT CASE current_database()
           WHEN 'account-dev' THEN 'c-app'
           WHEN 'account-staging' THEN 'x-app'
           WHEN 'account-live' THEN 'a-app'
           END);

Upvotes: 0

Views: 250

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

Use psql and its wonderful \gexec:

SELECT format(
          'GRANT UPDATE (my_column) ON my_table TO %I;',
          CASE current_database()
             WHEN 'account-dev' THEN 'c-app'
             WHEN 'account-staging' THEN 'x-app'
             WHEN 'account-live' THEN 'a-app'
          END
       ) \gexec

Alternatively, you can write a DO statement that uses EXECUTE to execute a dynamic statement constructed as above.

Upvotes: 1

Related Questions