Postgres Roles And Permissions
Posted by Jason Wed, 25 Jan 2006 03:17:00 GMT
If you’re application’s database follows the Application Database design (which most web apps do employ these days), it’s not difficult to figure out how to set up your roles and permissions for the DBMS. The challenge is that you have multiple databases on your DBMS, and you want to be sure that each user can’t access any other user’s data. So here’s how you would add a new database in Postgres:
-- First we want to create a new role for the database
CREATE USER myuser PASSWORD 'mypassword';
-- Now create the database - note the owner
CREATE DATABASE mydb OWNER myuser;
-- Remove public access to the database
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
Now connect to mydb as myuser…
-- Even though no other users should be able to access the database,
-- we'll be extra paranoid and make sure we're not working in a
-- public schema
DROP SCHEMA PUBLIC CASCADE;
-- By default, Postgres will automatically find the schema with the
-- same name as your user name if you create it.
-- Plus, it's not accessible to any other users by default!
CREATE SCHEMA myuser;
There! Now you can access and use that database normally. As long as no one is using the super user account, you can be confident that other database roles can’t access this the data in mydb data. Rinse and repeat for each application/database that you need.
This is pretty easy to figure out if you RTFM, but I figure it’s a good thing to write out anyway. I’ve seen this done in very wrong ways. And it’s tempting to ignore database security when your team members are the only people who will ever access the database. But it’s easy, SO DO IT.
