giving and taking permissions on postgres

Intro

I'm writing a short and handy guide to anyone in a jr position - especially if you are a junior sysadmin. I often find myself having to search for a specific command to do something basic. I'm fully aware there are tons of other tutorials/posts, but I hope you find this one clear and helpful.

Privileges

Postgres offers 12 types of privileges, they are

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• REFERENCES
• TRIGGER
• CREATE
• CONNECT
• TEMPORARY
• EXECUTE
• USAGE

Commands

This are some examples based on what I use most in my day-to-day as an infrastructure analyst.

• To give all privileges in a database for some user
  GRANT ALL PRIVILEGES IN your_database TO your_user;

• To let an user login

GRANT CONNECT ON DATABASE your_database TO your_user;

• To become a superuser

ALTER USER your_user WITH SUPERUSER;

• To let a user create databases

ALTER USER your_user CREATEDB;

• Giving permissions in a schema

GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA schema_name TO username; 

• Giving permissions in a specific table

GRANT { SELECT | INSERT | UPDATE | REFERENCES } ON TABLE table_name TO your_user

• This one is for giving permissions in all schemas of a database (there are other ways to do this)
  SELECT format('GRANT USAGE ON SCHEMA %I TO readonly_group;', schema_name) FROM information_schema.schemata \gexec

• This one gives permissions on all tables in all schemas (again, there are other ways to do this)
  SELECT format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO readonly_group;', schema_name) FROM information_schema.schemata \gexec

Taking privileges

First of all I'll show you how to remove privilege from a superuser

ALTER USER username WITH NOSUPERUSER;

The command REVOKE is used with similar syntax as GRANT, only difference is that it revokes privileges.

Examples

• Removing user from role/group
  REVOKE role FROM user; or ALTER GROUP group DROP USER user;

• Removing all privileges from a user

REVOKE ALL PRIVILEGES ON table FROM user;

Conclusion

This is an alive document and I'm open to criticism, i'll probably update this with new findings.

References

Here is a full guide with all PostgreSQL Commands (a.k.a documentation) - https://www.postgresql.org/docs/13/sql-commands.html