In PostgreSQL, different people may need access to the same database.
For example, in a university database:
PostgreSQL provides a security system to manage these users and their permissions.
In this lesson, we will learn how to create users, create roles, assign permissions, and control database access.
A user is an account that can connect to a PostgreSQL server and perform permitted operations.
For example:
postgresteacher_useradmin_userstudent_userA user normally connects using a username and password.
A role is an account or a collection of permissions in PostgreSQL.
PostgreSQL uses roles to manage database access.
A role can:
Important: In PostgreSQL, users and groups are both implemented through roles.
The main difference is:
LOGIN attribute can connect to PostgreSQL.LOGIN is commonly used to group permissions.We can create a user using the CREATE USER command.
Syntax:
CREATE USER username WITH PASSWORD 'password';
Example:
CREATE USER teacher_user
WITH PASSWORD 'teacher123';
This creates a user named teacher_user.
The user can authenticate using the specified password, subject to PostgreSQL connection settings.
We can create a role using the CREATE ROLE command.
Syntax:
CREATE ROLE role_name;
Example:
CREATE ROLE teachers;
This creates a role named teachers.
By default, it does not have the LOGIN attribute.
We can use this role to group permissions for multiple teachers.
We can create a role with the LOGIN attribute.
Example:
CREATE ROLE teacher_user
WITH LOGIN PASSWORD 'teacher123';
This creates a login role.
It is functionally similar to:
CREATE USER teacher_user
WITH PASSWORD 'teacher123';
Remember: CREATE USER is essentially a convenient form of CREATE ROLE with LOGIN.
PostgreSQL provides commands to view existing roles.
In the psql terminal, execute:
\du
This displays the roles and their attributes.
To view additional details:
\du+
You can also use an SQL query:
SELECT rolname
FROM pg_roles;
This displays the names of available roles.
Suppose we have created:
teacher_user.teachers.We can assign the teachers role to the user.
Command:
GRANT teachers TO teacher_user;
Now teacher_user is a member of the teachers role.
The user can use the permissions associated with that role according to PostgreSQL’s role-membership rules.
We can remove role membership using REVOKE.
Syntax:
REVOKE role_name FROM username;
Example:
REVOKE teachers FROM teacher_user;
This removes the membership of teacher_user in the teachers role.
It does not delete the user account.
Creating a user does not automatically give that user access to every database.
We must grant the required permissions.
Suppose we have a database named university_db.
Command:
GRANT CONNECT ON DATABASE university_db
TO teacher_user;
This allows the role to connect to university_db, subject to other connection restrictions.
Important: CONNECT permission does not automatically allow the user to read or modify tables.
A schema is a logical container for database objects such as tables and views.
Suppose our tables are inside the public schema.
We can grant schema access:
GRANT USAGE ON SCHEMA public
TO teacher_user;
This allows the role to access objects in the schema when it has the necessary object permissions.
The SELECT permission allows a user to read data from a table.
Syntax:
GRANT SELECT ON TABLE table_name
TO username;
Example:
GRANT SELECT ON TABLE public.students
TO teacher_user;
Now teacher_user can read records from the students table.
Example query, when connected as teacher_user:
SELECT *
FROM public.students;
The INSERT permission allows a user to add new records.
Example:
GRANT INSERT ON TABLE public.students
TO teacher_user;
Now the user can insert new student records, provided any other required permissions and constraints are satisfied.
The UPDATE permission allows a user to modify existing records.
Example:
GRANT UPDATE ON TABLE public.students
TO teacher_user;
Now the user can update records in the students table.
Example:
UPDATE public.students
SET student_name = 'Ali'
WHERE student_id = 101;
The DELETE permission allows a user to remove records.
Example:
GRANT DELETE ON TABLE public.students
TO teacher_user;
Now the user can delete records from the students table.
Example:
DELETE FROM public.students
WHERE student_id = 101;
We can grant multiple permissions in one command.
Example:
GRANT SELECT, INSERT, UPDATE
ON TABLE public.students
TO teacher_user;
This allows the user to:
The user does not receive DELETE permission.
We can use ALL PRIVILEGES to grant all applicable table-level privileges.
Example:
GRANT ALL PRIVILEGES
ON TABLE public.students
TO teacher_user;
This grants the available table privileges, such as SELECT, INSERT, UPDATE, DELETE, and other table-level privileges.
Important: It does not make the user a superuser or automatically grant permissions on every other table.
Suppose the public schema contains many tables.
Instead of granting permissions individually, we can use:
GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO teacher_user;
This grants SELECT permission on all existing tables and views in the schema.
To grant multiple permissions:
GRANT SELECT, INSERT, UPDATE
ON ALL TABLES IN SCHEMA public
TO teacher_user;
Important: These commands affect existing objects. They do not automatically grant permissions on tables created in the future.
To grant permissions automatically on future tables created by a particular role, use ALTER DEFAULT PRIVILEGES.
Example:
ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT SELECT
ON TABLES TO teacher_user;
This applies to future tables created by the role executing the command.
If a different role creates tables, its default privileges must be configured separately.
We can remove previously granted permissions using the REVOKE command.
Syntax:
REVOKE permission_name
ON TABLE table_name
FROM username;
Example:
REVOKE INSERT
ON TABLE public.students
FROM teacher_user;
This removes the INSERT permission from teacher_user on the students table.
To remove multiple permissions:
REVOKE INSERT, UPDATE
ON TABLE public.students
FROM teacher_user;
We can change a user’s password using ALTER ROLE.
Syntax:
ALTER ROLE username
WITH PASSWORD 'new_password';
Example:
ALTER ROLE teacher_user
WITH PASSWORD 'newTeacher456';
This changes the password of teacher_user.
We can rename an existing role using ALTER ROLE.
Example:
ALTER ROLE teacher_user
RENAME TO faculty_user;
The role is now named faculty_user.
Its existing permissions and role memberships remain associated with the renamed role.
We can delete a role using DROP ROLE.
Syntax:
DROP ROLE role_name;
Example:
DROP ROLE teacher_user;
PostgreSQL will not allow the role to be dropped if it still owns database objects or has certain privileges or dependencies.
We may need to transfer ownership or remove dependencies first.
For example, in the appropriate database:
REASSIGN OWNED BY teacher_user TO postgres;
Then:
DROP OWNED BY teacher_user;
Finally:
DROP ROLE teacher_user;
Warning: DROP OWNED can remove privileges and drop objects owned by the role in the current database. Use it carefully.
| Permission | Purpose |
|---|---|
CONNECT |
Connect to a database |
CREATE |
Create objects in a database or schema, depending on the grant |
USAGE |
Use a schema or certain database objects |
SELECT |
Read table data |
INSERT |
Add new records |
UPDATE |
Modify existing records |
DELETE |
Delete records |
TRUNCATE |
Remove all rows from a table |
REFERENCES |
Create foreign key references |
TRIGGER |
Create triggers on a table |
Suppose we want to create a teacher account that can:
university_db.Follow these steps.
Step 1: Create a user
CREATE USER teacher_user
WITH PASSWORD 'teacher123';
Step 2: Grant database connection permission
GRANT CONNECT
ON DATABASE university_db
TO teacher_user;
Step 3: Grant schema access
GRANT USAGE
ON SCHEMA public
TO teacher_user;
Step 4: Grant table permissions
GRANT SELECT, INSERT, UPDATE
ON TABLE public.students
TO teacher_user;
Step 5: Test the account
Connect to PostgreSQL using teacher_user and try:
SELECT *
FROM public.students;
Then try inserting or updating a record in a test database.
A DELETE operation should fail if the user has no other source of DELETE permission.
postgres superuser account for ordinary application work.student_user.students.student_user.student_user to learner_user.teacher_user.university_db.USAGE on the public schema.SELECT permission on the students table.INSERT and UPDATE permissions.UPDATE permission.SELECT permission on all existing tables in the public schema.faculty.faculty role.faculty role.faculty role.GRANT?REVOKE?CONNECT and SELECT?ALTER DEFAULT PRIVILEGES?| Command | Purpose |
|---|---|
CREATE USER |
Create a login role |
CREATE ROLE |
Create a role |
\du |
Display roles in psql |
GRANT |
Assign permissions |
REVOKE |
Remove permissions |
ALTER ROLE |
Modify a role |
DROP ROLE |
Delete a role |
GRANT CONNECT |
Allow database connections |
GRANT USAGE |
Allow schema usage |
GRANT SELECT |
Allow reading data |
ALTER DEFAULT PRIVILEGES |
Set default permissions for future objects |
Next Lesson: PostgreSQL Schema Management
Students will learn how to create, rename, alter, delete, and manage schemas, and how schemas organize database objects.