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?
up vote 36 down vote accepted

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.

  • Great explanation! – sharadov Jan 29 at 23:03
  • So ... what is the syntax for granting all rights to all tables in schema public? – AlxVallejo Mar 12 at 14:30
  • @AlxVallejo this should help. Try the top link, or read the GRANT documentation page where it lists ALL TABLES IN SCHEMA – Craig Ringer Mar 16 at 13:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.