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.
Hello. I like the new theme. I’m notorious for not R-ingTFM, please don’t digest me.
Here comes the scrolly!
:D
Particularly the title.
It’s alright… But if you ever have multiple applications sharing one Postgres instance, come back and RTFM !
It seem comments appear in reverse order… Let me see if I can track this down.
eh.
So I decided to use this KDE GUI tool for emerging queues of portage-friendly software.
I think I selected 70 pieces of software, and watched as the queue ballooned. Unfortunately, for a multitude of potential reasons, compilations errors would result. Each compilation error meant that restarting the queue would cause the GUI to renact the whole emerging process on the entire queue, unless the already-compiled packages were removed from the queue. The whole process went on two days while I was at school, with breaks for when there was an error and I couldn’t attend to it.
Apparently now I have over 700 packages installed from portage (before the recent batch, I had around 500), although I don’t think I will ever use most of them. I’ve concluded that I don’t appreciate KDE programs all that much at this point in time, and have started using XFCE again with positive results. Replacing the programs I used to use from KDE , I’ve found other equivalents—most of which seem to use GTK , or so I understand. Am I right in assuming that a few programs that rely on GTK would require less overhead software than any KDE program?
I’ve gotten some eclipse plugins that work with C/C++ development. Eclipse seems to be outside of the portage system for some reason.
I’m starting to feel that Gentoo isn’t as super-magical and special as my ideal first impression seemed to imply. I need to spend more effort into understanding portage management if I’m going to make good use of it, because I’ve made a huge mess on this hard drive.
But on the bright side, I like my new XFCE setup, and can continue working on my design document for school. Then when that’s done I can experiment with eclipse and C++ing, and developing a dynamic bacon-related website.
eh.out
Neato, the order of posting is now changed. However, the text-input box has a misleading ‘a.’.
Ah, the temp scrolly page merely lies to me, while the database holds the truth of this message appearing before the others.