Grant Permission in PostgreSQL
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA demo_admin TO demo_rw;
GRANT USAGE ON SCHEMA demo_admin TO demo_rw;
grant demo_rw to demo_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA demo_admin TO demo_rw;
How to grant access to users in PostgreSQL?
Here are some common statement to grant access to a PostgreSQL user:
=> Grant CONNECT to the database:
GRANT CONNECT ON DATABASE database_name TO username;
=> Grant USAGE on schema:
GRANT USAGE ON SCHEMA schema_name TO username;
=> Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
=> Grant all privileges on all tables in the schema:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;
=> Grant all privileges on all sequences in the schema:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;
=> Grant all privileges on the database:
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
=> Grant permission to create database:
ALTER USER username CREATEDB;
=> Make a user superuser:
ALTER USER myuser WITH SUPERUSER;
=> Remove superuser status:
ALTER USER username WITH NOSUPERUSER;
=> Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default.
For example:
ALTER DEFAULT PRIVILEGES FOR USER username IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;
