CODEWITHSUNDEEP
amazon-redshiftpsql
moe
moe

Reputation: 1806

Grant rights to update procedures to a redshift user

I want to create a user that can only update stored procedures in redshift. The procedures are given as .sql files that start like this

CREATE OR REPLACE PROCEDURE schema_name.procedure_name
...

Hence I created the new user in psql and tried to assign the rights: GRANT CREATE OR REPLACE PROCEDURE to ci.

But when I try to update with the new user psql -h $host -U ci -d $database -p $port -f file.sql it fails with ERROR: permission denied for schema schema_name. (This works with the admin user)

Upvotes: 0

Views: 2660

Answers (2)

moe
moe

Reputation: 1806

Synthezising from the different comments and answers:

  1. The ci user must be owner of the function. This means the function must be deleted beforehand (and recreated by ci afterwards)
  2. GRANT USAGE ON SCHEMA schema_name TO ci is necessary but not sufficient.
  3. I was finally able to make it work after additionally adding GRANT ALL ON SCHEMA schema_name TO ci;

Open questions:

  • There should be a more minimal set of rights to accomplish this.
  • Do the necessary privileges depend on the sql statements in the procedure?

Upvotes: 2

Armand
Armand

Reputation: 23

I'm not sure about splitting the CREATE and REPLACE permissions, but the error you're getting seems unrelated. I think your user ci also needs USAGE permission on the schema.

GRANT USAGE ON SCHEMA schema_name TO ci

Upvotes: 1

Related Questions