Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases?

Or can I add a rule into some config file?

share|improve this question
    
Does user1 own the database? –  a_horse_with_no_name Jan 4 '12 at 14:48
    
@a_horse_with_no_name: at the present he does, but not necessarily, I could just change the ownership to postgres if necessary? –  mit Jan 4 '12 at 15:11
    
It would be best to have a solution for databases that user1 owns, but I'd also behappy to have at least the other option. –  mit Jan 4 '12 at 15:13
    
I'm not 100% sure, but I think the owner can always drop the database. So you should make the user postgres the owner of the database. –  a_horse_with_no_name Jan 4 '12 at 15:32

1 Answer 1

up vote 3 down vote accepted

In PostgreSQL, only the owner of a database can drop a database. (Superusers can drop databases, but that's a different issue.) So changing the owner is the most direct way to prevent user1 from dropping any databases.

Fix that with ALTER DATABASE.

ALTER DATABASE name OWNER TO new_owner

To alter the owner, you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.)

To give specific permissions back to user1, use GRANT. You're probably looking for something like

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO user1;

But read the documentation for GRANT first. You might need WITH GRANT OPTION, permissions on sequences, something less than ALL PRIVILEGES, and so on.

share|improve this answer
    
But now user1 cant do anything with the database anymore. How can I allow user1 to do everything else with it (but not dropping)? –  mit Jan 4 '12 at 15:49
1  
@mit: You probably need to grant the CREATE privilege on that database to the user. –  a_horse_with_no_name Jan 4 '12 at 16:20
    
Thank you, that really helps and saves me a lot of time, great! –  mit Jan 4 '12 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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