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?

1 Answer 1


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
    Commented Jan 29, 2018 at 23:03
  • So ... what is the syntax for granting all rights to all tables in schema public?
    – AlxVallejo
    Commented Mar 12, 2018 at 14:30
  • @AlxVallejo this should help. Try the top link, or read the GRANT documentation page where it lists ALL TABLES IN SCHEMA Commented Mar 16, 2018 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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