Roles, GRANT & Row-Level Security

13 min read

Roles, GRANT & Row-Level Security

PostgreSQL uses a unified concept of roles for both users (login roles) and groups (nologin roles). This makes it straightforward to assign permissions to a role and then grant that role to multiple users.

Creating Roles

sql

-- Create a login role (a 'user')
CREATE ROLE alice LOGIN PASSWORD 'hunter2';

-- Create a group role (no login)
CREATE ROLE readonly_group NOLOGIN;

-- Grant the group role to a user
GRANT readonly_group TO alice;

-- Create a superuser (rarely needed outside of DBA tasks)
CREATE ROLE dba_user SUPERUSER LOGIN PASSWORD 'strongpass';

Object-Level Privileges

sql

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

-- Grant DML to an application role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- Ensure future tables inherit the same grants
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Revoke the ability to create objects in public schema (security hardening)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Row-Level Security (RLS)

Row-Level Security lets you define policies that restrict which rows a given role can see or modify. It is the foundation of multi-tenant data isolation in platforms like Supabase.

sql

-- Enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own posts
CREATE POLICY posts_owner_select
  ON posts
  FOR SELECT
  USING (author_id = current_setting('app.current_user_id')::bigint);

-- Policy: users can only update their own posts
CREATE POLICY posts_owner_update
  ON posts
  FOR UPDATE
  USING (author_id = current_setting('app.current_user_id')::bigint);

-- Admins bypass all policies (they are superusers or use BYPASSRLS)
ALTER ROLE admin_role BYPASSRLS;

Table owners bypass RLS by default. If your application connects as the table owner, RLS policies will be silently ignored. Always connect as a limited application role, not the schema owner, when you rely on RLS for data isolation.

Inspecting Privileges

sql

-- See privileges on tables
\dp posts    -- in psql

-- Or via information_schema
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'posts';

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises