PostgreSQL – Granting access to users

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;