In this article we will examine how to give permission to a user in PostgreSQL.
You should click on the Postgres menu item for other PostgreSQL articles.
You can give permission to a user on an object with the command below.
1 | GRANT permissiontype ON objectname TO username; |
Example1:
Give permission on database:
1 | GRANT SELECT ON DATABASE databasename TO username; |
Below you will find the kinds of permissions you can give on the database.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY or TEMP, EXECUTE, USAGE, ALL PRIVILEGES |
Example2:
Give permission on the object:
1 | GRANT SELECT ON tablename TO username; |
Example3:
Give all tables in a schema:
1 | GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO username; |
Example4:
Give all permissions on database:
1 | GRANT ALL PRIVILEGES ON DATABASE "databasename" TO username; |
Example5:
If you put the following command at the end of the above scripts, the relevant user can also give permissions to others.
1 | GRANT SELECT ON tablename TO username WITH GRANT OPTION; |
You may want to look at the link below for more detailed authorization types.
https://www.postgresql.org/docs/9.0/static/sql-grant.html