Schema-level access control in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is schema-level access control in PostgreSQL?

Schema-level access control means setting permissions on a whole schema to control who can create, modify, or use objects inside it.

beginner

Which SQL command is used to grant usage rights on a schema in PostgreSQL?

The command is GRANT USAGE ON SCHEMA schema_name TO role_name;. It lets the role access objects inside the schema.

intermediate

What does the CREATE privilege on a schema allow a user to do?

It allows the user to create new tables, views, or other objects inside that schema.

intermediate

How do you revoke all privileges on a schema from a user in PostgreSQL?

Use REVOKE ALL ON SCHEMA schema_name FROM role_name; to remove all schema-level permissions from that role.

beginner

Why is schema-level access control useful in a database?

It helps organize security by controlling access to groups of objects, making management easier and safer.

Which privilege must be granted to allow a user to create tables in a schema?

A USAGE

B CREATE

C SELECT

D INSERT

The CREATE privilege on a schema allows creating new objects like tables inside it.

What does the USAGE privilege on a schema allow?

A Create new tables

B Delete objects

C Access objects inside the schema

D Modify schema structure

USAGE lets a user access objects inside the schema but does not allow creating or modifying them.

How do you remove all schema privileges from a user?

A REVOKE ALL ON SCHEMA

B GRANT NONE

C DROP USER

D ALTER USER

REVOKE ALL ON SCHEMA removes all permissions on that schema from the specified user.

If a user has USAGE but not CREATE on a schema, what can they do?

A Modify schema permissions

B Create tables

C Drop the schema

D Access existing objects

USAGE allows accessing existing objects but not creating new ones.

Which command grants a role permission to use a schema?

A GRANT USAGE ON SCHEMA

B GRANT ALL ON DATABASE

C GRANT EXECUTE ON SCHEMA

D GRANT SELECT ON SCHEMA

GRANT USAGE ON SCHEMA lets a role access objects inside the schema.

Explain how schema-level access control works in PostgreSQL and why it is important.

Think about controlling who can see or create tables inside a folder-like structure.

Describe the difference between the USAGE and CREATE privileges on a schema.

One lets you look inside, the other lets you add new things.