Setting Access Rights with GRANT and REVOKE

Imagine your data is a fortress, and every resident has their own rights: some can just walk around the yard, some hold the keys to the treasure room, and some chill in the throne room running the show. In our database, GRANT and REVOKE are the commands that handle these roles. They're the ones deciding who can go where and why.

The GRANT command, just like any normal grant, lets you give access to resources (like databases, tables, schemas) to specific roles. It's kinda like a "party invite" where you decide who can read, write, or even break the furniture.

The REVOKE command, on the flip side, takes away previously given rights. It's like saying, "Hey, party's over for you, leave your keys at the door."

Granting Rights with GRANT

Let's start at the top level—the database. For a user to connect to a database, you gotta give them connect rights. Here's the command for that:

GRANT CONNECT ON DATABASE database_name TO role_name;

For example, if we've got a database called university and a role student, we can let students connect with this query:

GRANT CONNECT ON DATABASE university TO student;

But just connecting doesn't mean you can do whatever you want. If you want the user to create objects in the database, you also need to give them create rights:

GRANT CREATE ON DATABASE university TO student;

You can check the rights on a database with this command:

\l+ university

Revoking Rights with REVOKE

If a student suddenly starts acting shady (like trying to create more tables than we expected), we can take away their create rights with this command:

REVOKE CREATE ON DATABASE university FROM student;

After that, the student can only connect, but no more "creative freedom."

Setting Access Rights at the Schema Level

A schema is basically a "room" inside our database where tables, views, and other objects live. For a user to work with objects inside a schema, you can set up rights for reading, writing, or creating objects.

Granting Rights on a Schema

Let's say we've got a schema called public (it's created by default in every database). We can let a user view the contents of the schema:

GRANT USAGE ON SCHEMA public TO student;

But just USAGE isn't enough to work with tables. If you want the user to create new objects, add this:

GRANT CREATE ON SCHEMA public TO student;

Now the student can not only read but also create tables in the public schema.

Revoking Rights on a Schema

If the student starts cluttering up the schema with weird tables like bad_idea_01, we can limit their rights:

REVOKE CREATE ON SCHEMA public FROM student;

Now the student can't add new tables anymore. Order restored!

Setting Access Rights at the Table Level

The table is probably the most popular database object. Let's figure out how to set access specifically for tables. There are three main categories of actions here: reading, writing, and modifying.

Read Rights

To let a user read the contents of a table, use this command:

GRANT SELECT ON TABLE table_name TO role_name;

For example, to let students read records from the courses table:

GRANT SELECT ON TABLE courses TO student;

Now the student user can run SELECT queries on the courses table.

Write Rights

If you want the user to be able to insert new rows into a table, set it up like this:

GRANT INSERT ON TABLE table_name TO role_name;

Example:

GRANT INSERT ON TABLE courses TO student;

Now students can add new courses to the table. But wait... Are we sure that's a good idea?

Modify and Delete Rights

If a user should be able to update existing rows or delete them, you need to give them UPDATE and DELETE rights respectively.

GRANT UPDATE ON TABLE courses TO student;
GRANT DELETE ON TABLE courses TO student;

Tip: try not to overdo it with these rights. If you give students delete access, they might accidentally (or on purpose) mess everything up.

Examples: Creating a Role with Limited Rights

Imagine we're creating a role for teachers who should have access to read data about students and courses, but can't delete records. Here's how to do it:

1. Create the role:

CREATE ROLE teacher;

2. Give read rights on the students and courses tables:

GRANT SELECT ON TABLE students, courses TO teacher;

3. Restrict delete access:

REVOKE DELETE ON TABLE students, courses FROM teacher;

Now our teachers have only the rights they need and nothing extra.

How to Combine GRANT and REVOKE for Flexible Setup

Let's say we've got a role called intern that we want to restrict. They should only have access to course data, but absolutely not to student data. Here's how it looks:

1. Allow access only to the courses table:

GRANT SELECT, INSERT ON TABLE courses TO intern;

2. Make sure the intern role has no rights on the students table:

REVOKE ALL ON TABLE students FROM intern;

This combo lets you fine-tune access rights.

Real-World Usage Examples

This access management mechanism is used all the time in real projects. For example:

1. In online stores, rights to user and order tables are split between roles like "admin", "operator", and "guest".
2. In university systems, admins can add and edit courses, while students can only view them.
3. In banking systems, access to client accounts is separated between employees from different departments.