Roles, Permissions, and Security

PostgreSQL uses a role-based access control system. Roles can represent individual users or groups, and roles can be members of other roles, inheriting their privileges. The GRANT and REVOKE commands control which operations a role can perform on database objects. Row-Level Security (RLS) policies allow tables to filter rows based on the current user, enabling multi-tenant applications where users only see their own data. Connection-level security is configured in pg_hba.conf, which specifies which hosts can connect, which users they can authenticate as, and what authentication method to use. Always follow the principle of least privilege: application users should not be superusers, and should only have permissions on the objects they actually need.

Example

-- Create roles
CREATE ROLE app_user  LOGIN PASSWORD 'securepassword';
CREATE ROLE app_admin LOGIN PASSWORD 'adminpassword';
CREATE ROLE readonly;

-- Grant privileges
GRANT CONNECT ON DATABASE myappdb TO app_user;
GRANT USAGE ON SCHEMA shop TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA shop TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA shop TO readonly;

-- Make app_admin a member of app_user (inherits privileges)
GRANT app_user TO app_admin;

-- Row-Level Security example — users only see their own orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_isolation_policy ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Set the current user context in application code
SET LOCAL app.current_user_id = '42';

-- Revoke default public schema access (security best practice)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL    ON DATABASE myappdb FROM PUBLIC;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned