Transferring concepts - users/groups/permissions of db objects from mysql to postgresql

Question

This seemed like a better fit here than stackoverflow, so here goes:

I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started seriously developing web apps I've been using postgresql locally for my database, with a view to using it properly on the clients installations once I'm comfortable.

The only thing I've not yet grasped (of the basics, of course) is the concept of users/groups/permissions. In mysql, I create users and I can GRANT them permissions on the various tables I wish them to have access to.

How does this concept work in Postgres? Does it? On my local machine I've been allowing the user apache access to my tables so that my django apps can work, via:

echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2"
echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2" | psql $1
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2"
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2" | psql $1

(part of a bigger script)

Is this secure? I'm pretty certain per-app users sounds like a more sensible idea, but then I don't know.

So, I guess that makes my questions:

• How do users/groups/permissions work in postgresql?
• How do they compare to mysql?
• What is considered best practise?

Answer 1

there are no users and groups in postgres anymore (> 8.2?), there are only ROLEs, roles may or may not be able to login. Not that this matters much as CREATE USER and CREATE GROUP are just aliases for CREATE ROLE.

As @Peter Eisentraut says this code should work fine.

Some of the bigger differences I noticed in this area, outside of the fact that Postgres uses Roles, instead of users, is the way Postgres authenticates users to databases using ident which can allow system users to access databases without passwords, via a system user -> posgres user mapping, why by default maps them if they're equivalent. For more on Authentication, see the auth method documentation.

As far as table permissions, to my knowledge they are pretty close to the same. However you can make your permissions much more fine grained in postgres. Now in postgres you can even restrict users to specific columns using GRANT and in some coming version, it should be able to restrict database users by row.

I do absolutely suggest restricting different apps by different user/database parings, though you can also use SCHEMA's instead of databases, though if you own a schema, you can modify anything in it, so it's not quite a fine grained.

Any best practices will probably depend on your app, really you should go read the docs and figure out what is best for that App.

Answer 2

Granting permissions to users works exactly the same way in PostgreSQL. There are some differences in the advanced features, but what you wrote above works fine.