PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating an HTML table with a comparison of commands (for users/databases/command prompt, etc) between the two. After reading an answer here I've notice that role seems to be used as a group. With MySQL I have two users, basically public (DELETE, INSERT, SELECT and UPDATE permissions) and an admin user with a couple additional permissions.

So basically in the Windows 7 command prompt (local development only)...

• Is a role a user, group or a loosely used term specific to PostgreSQL?

• How do I grant only specific permissions to a specific user, for all tables in a database?

• How do I grant all permissions to a specific user, for all tables in a database?

• How, if at all, does a role compare to a user when using GRANT or REVOKE for user permissions?

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can also log in as. So "user" and "group" are essentially terms that indicate the intended usage of a role, there's no real distinction between them. Even in the PostgreSQL flavour of SQL the two are used more or less as synonyms. For example, the documentation on CREATE USER says:


CREATE USER is now an alias for CREATE ROLE.

Granting all ... see the manual for GRANT. You probably actually want to grant rights to ALL TABLES IN SCHEMA public rather than all tables in the database.

Granting some rights ... same thing, but instead of GRANT ALL use GRANT SELECT, INSERT for example. Again, see the manual.

A role is a user, and/or a group. You can only grant to roles, because roles are all that there is.

StackExchange Database Administrators Q#82271, answer score: 58