1

I need to create various schema in a database and give ownership of these to specific users. I want to do this in a script, and I rather not be the postgres user or a superuser. My user pat has createdb and createrole privileges.

pat=> create role toto with login password 'pass';  
pat=> create schema toto;
pat=> alter schema toto owner to toto;
ERROR:  must be member of role "toto"

Is there a way to do this without being postgres or superuser ? or should I give pat superuser privileges while running the scripts and drop it after? What is the best procedure?

1

Per documentation,

You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, [...]

So in your case, you either need to be superuser or be a member of the role you are transferring the ownership to.

Ultimately, while I applaud your efforts, you're going to have a hard time managing this without some superuser use. So I'd focus on locking down the superuser use, maybe wrap the steps in a few security-definer functions.

  • Thank you, this is what I understood, I was hoping I had missed something in the docs. Yes I must secure the "pat" account so that even if hacked, the scripts are not run easily. already it has a postgres password , I must add something in the scripts also then. – Pat070 Sep 23 '15 at 13:49
0

should I give pat superuser privileges while running the scripts and drop it after? What is the best procedure?

The best practice, or the way I would do this is simply to create the schema and assign it to the user using AUTHORIZATION

I would probably do it like this..

sudo -u postgres psql <<EOF
  CREATE USER toto WITH LOGIN PASSWORD 'pass';
  CREATE SCHEMA toto AUTHORIZATION toto;
EOF
  • not sure this is better than being the postgres superuser. in this case, i must have the sudo privileges, and if I want to run the cmd from a script, I have the same problem with the password . – Pat070 Jun 23 '17 at 14:06
  • @Pat070 that is doing it as the PostgreSQL superuser. I just keep my postgres superuser locked, as is best practices. And, sudo doesn't have to require a password, you can set that up in sudoers see NOPASSWD. – Evan Carroll Jun 23 '17 at 14:18
  • sure but I feel less safe with sudo without password. you would say that once somebody got my password, sudoers or not ... I have to think a about that – Pat070 Jun 25 '17 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.