SQL Academy · Lesson

Roles and Privileges

GRANT and REVOKE access.

Roles and Privileges is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Are Roles and Privileges?

In SQL, roles are named groups of privileges that can be assigned to users. Privileges control what actions a user or role is allowed to perform on database objects such as tables, views, and functions.

Instead of granting permissions to each user individually, you create a role with the required privileges and then assign that role to many users at once. This makes access control much easier to manage at scale.

Creating a Role

Use CREATE ROLE to define a new role in PostgreSQL. A role can represent a single user or a group of users depending on how you configure it.

Roles are created without any privileges by default — you must explicitly grant them access to objects.

CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;

Granting Table Privileges

The GRANT statement gives a role or user permission to perform specific operations on a database object. Common table-level privileges include SELECT, INSERT, UPDATE, and DELETE.

You can grant a single privilege or multiple privileges at once by separating them with commas.

-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;

Granting All Privileges

If a role needs full access to a table, you can use GRANT ALL PRIVILEGES instead of listing each permission individually. This grants every applicable privilege on the specified object.

Be cautious with ALL PRIVILEGES — only grant it to roles that genuinely require complete control over the object.

-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;

-- Or the shorthand form
GRANT ALL ON orders TO db_admin;

Revoking Privileges

The REVOKE statement removes previously granted privileges from a role or user. This is how you tighten access when requirements change or a role no longer needs certain permissions.

After a REVOKE, the affected role immediately loses the specified privilege on the named object.

-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;

-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;

Assigning a Role to a User

In PostgreSQL, users are also roles — a role with the LOGIN attribute can connect to the database. You assign a group role to a login role using GRANT role TO user.

Once assigned, the user inherits all privileges belonging to that role, making it straightforward to manage permissions for many users at once.

-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';

-- Assign the readonly role to alice
GRANT readonly_user TO alice;

-- Alice can now SELECT on tables granted to readonly_user

Revoking a Role from a User

To remove a role assignment from a user, use REVOKE role FROM user. After this, the user no longer inherits the privileges that came with that role.

This is useful when an employee changes responsibilities or leaves the organisation — you can revoke their role without touching the role's own privilege definitions.

-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;

-- Alice no longer has SELECT on tables via that role

Schema-Level Privileges

Before a role can access any objects inside a schema, it needs the USAGE privilege on that schema. Without it, even a role with SELECT on a specific table cannot read data because it cannot resolve the schema path.

Always grant USAGE on the schema alongside the object-level privileges.

-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Default Privileges for Future Objects

When new tables are created in a schema, existing roles do not automatically gain access to them. Use ALTER DEFAULT PRIVILEGES to ensure that future objects created by a specific role are automatically accessible to another role.

This prevents a common pitfall where a newly created table is invisible to application roles until someone remembers to run GRANT manually.

-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

The WITH GRANT OPTION

By default, a role that receives a privilege cannot pass that privilege on to other roles. Adding WITH GRANT OPTION allows the recipient to also grant that privilege to others.

Use this carefully — it means the role becomes a trusted delegation point for access control, which can complicate auditing if overused.

-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;

-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;

Viewing Granted Privileges

PostgreSQL stores privilege information in system catalog views. You can query information_schema.role_table_grants to see which roles have been granted which privileges on which tables.

The backslash command \dp tablename in psql also shows the access privilege list for a table in a compact format.

-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

Quick Check

Test your understanding of roles and privileges in SQL.

Recap: Roles and Privileges

In this lesson you learned how SQL manages access control through roles and privileges:

CREATE ROLE defines a new role or user account
GRANT assigns privileges (SELECT, INSERT, UPDATE, DELETE, ALL) on objects to a role
REVOKE removes those privileges
GRANT role TO user assigns a group role to a login user so they inherit its privileges
Roles need USAGE on the schema before they can access objects inside it
ALTER DEFAULT PRIVILEGES ensures future objects are automatically accessible
WITH GRANT OPTION lets the recipient delegate the privilege further

Keeping privileges minimal and role-based is a core principle of secure database design — grant only what is needed, and revoke promptly when it is no longer required.

Frequently asked questions

Is the “Roles and Privileges” lesson free?

Yes — the full text of “Roles and Privileges” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Roles and Privileges”?

GRANT and REVOKE access. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Roles and Privileges” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy