DEV Community

Victoria Santana
Victoria Santana

Posted on

4 1

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; 
Enter fullscreen mode Exit fullscreen mode
  • 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

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more