PostgreSQL - Privileges

PostgreSQL - Managing Privileges and Roles

Database security revolves around managing who can access what. In PostgreSQL, this is handled through a system of roles and privileges. When an object (like a table or a function) is created, it’s assigned an owner. By default, only the owner (or a superuser) can access or modify it. To allow other users to interact with the object, you must grant them specific permissions.

Understanding Roles

In modern PostgreSQL, there’s no technical difference between a “user” and a “group”. Both are considered roles. A role can have specific attributes, such as the ability to log in (LOGIN) or create databases (CREATEDB). A role can also be a member of another role, allowing you to create a hierarchy of permissions.

This is the modern and recommended approach: create roles for specific purposes (e.g., read_only, data_analyst), grant privileges to those roles, and then grant membership in those roles to your application or human users.

Common Privileges

Here are some of the most common privileges you will manage:

• SELECT: Allows reading data from a table or view.
• INSERT: Allows adding new rows to a table.
• UPDATE: Allows modifying existing rows in a table.
• DELETE: Allows removing rows from a table.
• TRUNCATE: Allows quickly deleting all rows from a table.
• USAGE: For schemas, allows access to objects within that schema. For sequences, allows using currval and nextval.
• EXECUTE: Allows calling a function.
• CONNECT: Allows a role to connect to a specific database.

Syntax: GRANT and REVOKE

To assign and remove privileges, you use the GRANT and REVOKE commands.

-- Granting a privilege
GRANT privilege_type [, ...] ON object_type object_name [, ...]
TO role_name [, ...];

-- Revoking a privilege
REVOKE privilege_type [, ...] ON object_type object_name [, ...]
FROM role_name [, ...];

Practical Example: Role-Based Access Control

Let’s walk through a modern, best-practice example. Instead of granting permissions directly to a user, we will create functional roles.

Step 1: Create a login role for our application.

This role can log in but initially has no other permissions. Note: Always use a secure, generated password in real applications.

CREATE ROLE web_app LOGIN PASSWORD 'a_very_secure_password';

Step 2: Create a group role for read-only access.

This role cannot log in itself; it’s just a container for privileges.

CREATE ROLE read_only;

Step 3: Grant the necessary privileges to the read_only role.

Let’s assume our tables are in the public schema of the dev_db database.

-- Allow connection to the database
GRANT CONNECT ON DATABASE dev_db TO read_only;

-- Allow usage of the 'public' schema
GRANT USAGE ON SCHEMA public TO read_only;

-- Grant SELECT on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Best Practice: Default Privileges

What about tables created in the future? ALTER DEFAULT PRIVILEGES ensures that any new tables created by a specific user (e.g., the migration user) will automatically have the correct permissions.

-- As the user who will create future tables (e.g., 'postgres' or a 'migration_role')
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

Step 4: Assign the group role to our application user.

GRANT read_only TO web_app;

Now, the web_app role inherits all the permissions of the read_only role. This is far more maintainable than assigning privileges to each user individually.

Revoking Privileges and Cleaning Up

To remove access, you simply revoke the membership.

REVOKE read_only FROM web_app;

And to delete the roles themselves:

DROP ROLE web_app;
DROP ROLE read_only;

Advanced Topic: Row-Level Security (RLS)

For more fine-grained control, PostgreSQL offers Row-Level Security. RLS allows you to define policies that control which rows a user can view or modify in a table, based on their role or other attributes. This is a powerful feature for multi-tenant applications and is a key topic to explore as you advance.