Thursday, January 20, 2011

PostgreSQL user permissions

I have a PostgreSQL database on a Debian box and i've (from the shell):

su postgres
createdb dnls
createuser dnls -P
Enter password for new user: somepass [enter]
Enter it again: somepass [enter]
Shall the new user be allowed to create databases? (y/n) n [enter]
Shall the new user be allowed to create more new users? (y/n) n [enter]

After that i added the user/pass/dbname in my Django app settings.py and then i did a manage.py syncdb and django created the tables for my models.

How come the dnls user has read/write permissions to dnls database?
I did not gived him any permissions anywhere.
Does he has access just because the database has the same name as him? This doesn't look secure.

  • Your new user didn't automatically get read/write permission to your database just because his name is the same as the database. You will find that info if you consult the createdb and createuser man pages.

    What IS going on is that even though that user is not the owner, he has rights to read/write to all databases that aren't specifically restricted. This is controlled by the template that is used during the database creation. If you don't like that behavior, you can create your own template and in the future use the template during database creation.

    daniels : So this is happening only because i created the users using 'createuser'? If i would've created him using SQL (psql, and create user dnls...) then he wouldn't had access until i explicitly given him?
    Magnus Hagander : No, it's because the default permissions on the PUBLIC schema is that anybody can create objects in it (as the answer says, you can change this by changing the template - or you can change it directly in your target database). Once that is done, the user becomes the owner of those objects, and thus have permissions. It doesn't matter which way you create your user - "createuser" just calls the same SQL command you'd call manually.
    From Josh Budde

0 comments:

Post a Comment