Wednesday, March 11, 2020

How to grant access to all tables in a schema in a PostgreSQL database


PostgreSQL offers a feature that Oracle currently does not: the ability to grant access rights to all objects in a specific schema.
Here is how.

Connect to the database "klmdb" and grant user JIM DML rights + query rights to all tables in the schema "klm":
postgres-# \connect klmdb
You are now connected to database "klmdb" as user "postgres".
klmdb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA klm to JIM;
or
klmdb=# GRANT ALL ON ALL TABLES IN SCHEMA KLM TO JIM;

No comments:

Post a Comment