Postgres Lab Starting database… Playground

PostgreSQL roles, privileges and row-level security

PostgreSQL controls access with roles. A role can be a user (with LOGIN), a group, or both. Privileges are granted on objects: GRANT SELECT ON products TO analyst;. Roles can be members of other roles and inherit their privileges, so the usual pattern is: grant to group roles, then grant group roles to people.

  • Table privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER; even per column.
  • REVOKE takes privileges away. ALTER DEFAULT PRIVILEGES sets privileges for tables created later.
  • Row-level security (RLS) adds per-row rules: CREATE POLICY ... USING (owner = current_user). Ideal for multi-tenant apps.
  • Superusers bypass all checks; applications should connect with a limited role.

In this browser you are the superuser postgres. SET ROLE lets you temporarily act as another role to test permissions; RESET ROLE switches back.

Syntax

CREATE ROLE name [LOGIN PASSWORD '...'];
GRANT SELECT, INSERT ON table TO role;
REVOKE INSERT ON table FROM role;
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON t USING (condition);

Examples

Edit the code and run it. Output is printed the way psql prints it.

26.1Create a read-only role and test it

RESET ROLE;
-- CREATE ROLE has no IF NOT EXISTS, so check first in a DO block
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'readonly_analyst') THEN
    CREATE ROLE readonly_analyst NOLOGIN;
  END IF;
END
$$;
GRANT SELECT ON products, orders TO readonly_analyst;

SET ROLE readonly_analyst;
SELECT current_user, count(*) AS products_visible FROM products;
RESET ROLE;

The role may read products and orders, and nothing else. Roles belong to the whole server, not one database, and CREATE ROLE has no IF NOT EXISTS, hence the DO block that makes the example safe to re-run.

26.2Permission denied

SET ROLE readonly_analyst;
SELECT * FROM customers;

Fails with “permission denied for table customers”. (The console resets the role for you after an error; in psql you would run RESET ROLE;.) Try UPDATE products SET price = 0; as this role too.

26.3Row-level security per sales rep

RESET ROLE;
DROP POLICY IF EXISTS rep_sees_own_orders ON orders;
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'rep_kavya') THEN
    CREATE ROLE rep_kavya NOLOGIN;
  END IF;
END
$$;
GRANT SELECT ON orders TO rep_kavya;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY rep_sees_own_orders ON orders
  FOR SELECT
  USING (employee_id = 7 AND current_user = 'rep_kavya');

SET ROLE rep_kavya;
SELECT id, customer_id, employee_id, status FROM orders ORDER BY id;
RESET ROLE;

ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

With RLS enabled, a normal role sees only rows that a policy allows: Kavya (employee 7) sees just her orders, even with SELECT *. A real app would usually compare against a setting such as current_setting('app.user_id'). The last line turns RLS off again so other lessons see all orders.