Roles and Permissions

PostgreSQL uses a role-based access control (RBAC) system to manage who can connect to databases and what they can do. Properly configuring roles and permissions is essential for security in production.

Roles vs Users

In PostgreSQL, users and roles are the same concept. The only difference is that CREATE USER adds the LOGIN attribute by default, whereas CREATE ROLE does not. You can grant roles to other roles, creating a hierarchy.

Creating Roles

-- Create a role that can log in
CREATE ROLE app_user LOGIN PASSWORD 'securepassword';

-- Create a superuser (has all privileges — use sparingly)
CREATE ROLE admin_user LOGIN SUPERUSER PASSWORD 'adminpassword';

-- Create a read-only role (no login — intended to be granted to others)
CREATE ROLE readonly;

Granting Privileges

-- Grant connection access to the database
GRANT CONNECT ON DATABASE myapp TO app_user;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant SELECT, INSERT, UPDATE, DELETE on a specific table
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE products TO app_user;

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

-- Grant a role to a user
GRANT readonly TO app_user;

Revoking Privileges

-- Revoke write access
REVOKE INSERT, UPDATE, DELETE ON TABLE products FROM app_user;

Default Privileges

When new tables are created, they do not automatically inherit existing grants. Use ALTER DEFAULT PRIVILEGES so that future tables are automatically accessible:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

Row-Level Security (RLS)

PostgreSQL supports fine-grained row-level security policies that control which rows a role can see or modify:

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy: users can only see their own orders
CREATE POLICY user_orders ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::integer);

Checking Current Permissions

-- List roles
\du

-- Show table privileges
\dp products

A well-designed permission model follows the principle of least privilege: each role receives only the permissions it needs and nothing more. This limits the blast radius of a compromised credential or a buggy application.