PostgreSQL Access & Privileges

A comprehensive guide to roles, grants, schemas, and security in PostgreSQL 14–16

1. Roles vs Users

In PostgreSQL, there is no real difference between a role and a user. A USER is simply a ROLE that has the LOGIN attribute. Both are managed with the same commands internally.

-- These two are exactly equivalent
CREATE USER alice WITH PASSWORD 'secret';
-- is the same as:
CREATE ROLE alice WITH LOGIN PASSWORD 'secret';

Roles can:

• Log in to the database (if they have LOGIN)
• Own database objects (tables, schemas, functions, etc.)
• Be granted privileges on objects
• Be members of other roles (acting as groups)
• Inherit privileges from roles they belong to (if INHERIT)

2. Role Attributes

Role attributes define what the role is allowed to do at the system level — not what database objects it can access. These are configured at creation or with ALTER ROLE.

-- Modify attributes after creation
ALTER ROLE alice CREATEDB;
ALTER ROLE alice CONNECTION LIMIT 10;
ALTER ROLE alice VALID UNTIL '2025-12-31';
ALTER ROLE alice NOLOGIN;          -- demote to a group role
ALTER ROLE alice PASSWORD 'newpass';
ALTER ROLE alice RENAME TO alice_old;

3. The PUBLIC Role

PUBLIC is a special built-in pseudo-role. Every role automatically belongs to PUBLIC, and any privilege granted to PUBLIC applies to all current and future users in the cluster.

Verifying what PUBLIC has

-- Check database-level privileges (look for "=" entries = PUBLIC)
\l your_database

-- Check schema-level privileges
\dn+ public

-- In the output:
--   =  (before the slash) means PUBLIC
--   C  = CREATE
--   U  = USAGE
--   T  = TEMP (on database)
--   c  = CONNECT (on database)
-- Example: =UC means PUBLIC has USAGE and CREATE on the schema

Locking down PUBLIC (recommended for production)

-- Prevent all users from connecting without explicit grant
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

-- Prevent all users from creating tables (most important)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Prevent temp table creation (optional, stricter)
REVOKE TEMP ON DATABASE mydb FROM PUBLIC;

-- Prevent calling all functions (optional, strictest)
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;

4. INHERIT vs NOINHERIT

When a role is a member of another role, the INHERIT attribute controls whether its privileges are automatically applied.

-- Setup a group role with SELECT
CREATE ROLE readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readers;

-- alice has INHERIT (default): privileges work immediately
CREATE USER alice WITH LOGIN PASSWORD 'pass';  -- INHERIT is default
GRANT readers TO alice;
-- alice can SELECT right away ✓

-- bob has NOINHERIT: must SET ROLE first
CREATE USER bob WITH LOGIN PASSWORD 'pass' NOINHERIT;
GRANT readers TO bob;
-- bob gets "permission denied" here ✗
SELECT * FROM orders;

-- bob must explicitly switch into the role
SET ROLE readers;
SELECT * FROM orders; -- now works ✓
RESET ROLE;           -- back to bob's own privileges

-- Fix bob to use INHERIT instead
ALTER USER bob INHERIT;

5. The Privilege Check Chain

PostgreSQL checks privileges at every level. A grant at one level does not bypass checks at others. You must satisfy all levels in the chain.

6. Database-Level Privileges

These control access at the database level, granted with GRANT ... ON DATABASE.

GRANT CONNECT ON DATABASE mydb TO alice;
GRANT CREATE ON DATABASE mydb TO alice;    -- allows creating new schemas
GRANT TEMP ON DATABASE mydb TO alice;      -- allows temporary tables
GRANT ALL ON DATABASE mydb TO admin_user;  -- all of the above

REVOKE ALL ON DATABASE mydb FROM alice;    -- remove everything

7. Schema-Level Privileges

Schemas are namespaces inside a database. You need schema privileges before you can access any objects within a schema.

GRANT USAGE ON SCHEMA public TO alice;     -- can use objects in schema
GRANT CREATE ON SCHEMA public TO alice;    -- can create tables/views etc.
GRANT USAGE, CREATE ON SCHEMA public TO alice;

-- Revoke CREATE from everyone (critical for production lockdown)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Create a private schema only certain roles can access
CREATE SCHEMA private;
REVOKE ALL ON SCHEMA private FROM PUBLIC;
GRANT USAGE ON SCHEMA private TO trusted_role;

8. Table & View Privileges

-- Single table grants
GRANT SELECT ON orders TO alice;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT ALL PRIVILEGES ON orders TO admin_user;

-- Grant on all currently existing tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alice;

-- Revoke a specific privilege
REVOKE INSERT ON orders FROM alice;

-- Grant WITH GRANT OPTION: allows alice to grant this to others
GRANT SELECT ON orders TO alice WITH GRANT OPTION;

9. Column-Level Privileges

SELECT, INSERT, UPDATE, and REFERENCES can be granted at the column level for fine-grained access control.

-- Allow alice to only read name and email columns
GRANT SELECT (name, email) ON users TO alice;

-- Allow bob to update only the status column
GRANT UPDATE (status) ON orders TO bob;

-- Verify column-level grants
SELECT grantee, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE grantee = 'alice';

10. Sequence Privileges

Sequences (used by SERIAL and IDENTITY columns) have their own privilege set. If an app user needs to INSERT into a table with an auto-increment ID, they also need sequence access.

-- App user inserting rows needs USAGE on sequences
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Read-only user typically only needs SELECT on sequences
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

-- Grant on a specific sequence
GRANT USAGE ON SEQUENCE orders_id_seq TO app_user;

11. Function & Procedure Privileges

GRANT EXECUTE ON FUNCTION calculate_tax(numeric) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

-- Lock down sensitive functions (override PUBLIC default)
REVOKE EXECUTE ON FUNCTION admin_reset_all_passwords() FROM PUBLIC;

Security Definer vs Security Invoker

Functions can run under two different privilege models:

-- SECURITY DEFINER: function runs as owner, not caller
-- Useful for allowing users to do one specific elevated operation
CREATE OR REPLACE FUNCTION get_all_orders()
RETURNS SETOF orders
LANGUAGE sql
SECURITY DEFINER
SET search_path = public  -- always set this with SECURITY DEFINER!
AS $$
    SELECT * FROM orders;
$$;

12. Built-in Predefined Roles (PG 10+)

PostgreSQL ships with several predefined roles. These are especially convenient on managed databases (like Azure, RDS) where you don't have superuser access.

-- Grant full read-only access to all tables/views in all schemas
GRANT pg_read_all_data TO readonly_user;

-- Grant write access everywhere (use sparingly)
GRANT pg_write_all_data TO app_user;

-- Grant monitoring access (for tools like pgBadger, Datadog)
GRANT pg_monitor TO monitoring_user;

13. Default Privileges

GRANT ON ALL TABLES IN SCHEMA only covers already-existing objects. Tables created in the future won't inherit those grants. ALTER DEFAULT PRIVILEGES fixes this.

-- Grant SELECT on all FUTURE tables that migration_user creates in public schema
ALTER DEFAULT PRIVILEGES
    FOR ROLE migration_user
    IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_user;

-- Grant INSERT/UPDATE/DELETE on all FUTURE tables
ALTER DEFAULT PRIVILEGES
    FOR ROLE migration_user
    IN SCHEMA public
    GRANT INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Grant USAGE on all FUTURE sequences (needed for INSERT with serial columns)
ALTER DEFAULT PRIVILEGES
    FOR ROLE migration_user
    IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_user;

-- Grant EXECUTE on all FUTURE functions
ALTER DEFAULT PRIVILEGES
    FOR ROLE migration_user
    IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO app_user;

-- View current default privilege configuration
\ddp

-- Remove a default privilege setting
ALTER DEFAULT PRIVILEGES
    FOR ROLE migration_user
    IN SCHEMA public
    REVOKE SELECT ON TABLES FROM readonly_user;

14. Row-Level Security (RLS)

RLS allows you to restrict which individual rows a user can see or modify — even when they have full SELECT on the table. Essential for multi-tenant applications.

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

-- Step 2: Create a policy
-- USING = filter for SELECT/UPDATE/DELETE
-- WITH CHECK = filter for INSERT/UPDATE (new row must satisfy this)
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::int);

-- Step 3: In your app, set the context before querying
SET app.tenant_id = '42';
SELECT * FROM orders; -- silently returns only tenant 42's rows

Separate read and write policies

-- Read policy: users see only their own rows
CREATE POLICY select_own_orders ON orders
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::int);

-- Insert policy: users can only insert rows for themselves
CREATE POLICY insert_own_orders ON orders
    FOR INSERT
    WITH CHECK (user_id = current_setting('app.current_user_id')::int);

-- Admins bypass all RLS (policy that always returns true for admins)
CREATE POLICY admin_all ON orders
    USING (current_setting('app.role') = 'admin');

-- Drop a policy
DROP POLICY select_own_orders ON orders;

-- Disable RLS entirely
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

15. Inspecting Privileges

psql meta-commands

SQL queries

-- All table privileges for a specific user
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'alice';

-- All roles a user is a member of
SELECT r.rolname
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.roleid
JOIN pg_roles u ON u.oid = m.member
WHERE u.rolname = 'alice';

-- All objects owned by a specific role
SELECT relname, relkind
FROM pg_class
WHERE relowner = 'alice'::regrole;

-- Programmatically check effective privileges
SELECT has_table_privilege('alice', 'orders', 'SELECT');    -- → true/false
SELECT has_schema_privilege('alice', 'public', 'USAGE');   -- → true/false
SELECT has_database_privilege('alice', 'mydb', 'CONNECT'); -- → true/false

-- View all RLS policies
SELECT schemaname, tablename, policyname, roles, cmd, qual
FROM pg_policies;

-- Find all non-default privileges (useful for auditing)
SELECT * FROM information_schema.role_table_grants
WHERE grantee != 'PUBLIC' AND grantee != table_schema;

16. Common Recipes

✅ Minimal Read-Only User (PG 14+)

CREATE USER readonly_user WITH LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT pg_read_all_data TO readonly_user;
REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- prevent table creation

✅ App User (CRUD, no DDL)

CREATE USER app_user WITH LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Cover future tables created by the migration user
ALTER DEFAULT PRIVILEGES FOR ROLE migration_user IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE migration_user IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_user;

✅ Migration / Schema Owner User

CREATE USER migration_user WITH LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO migration_user;
GRANT CREATE ON DATABASE mydb TO migration_user;  -- create schemas
GRANT CREATE ON SCHEMA public TO migration_user;   -- create tables

✅ Group Role Pattern

-- Create a group role (no LOGIN)
CREATE ROLE readonly_group NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

-- Add users to the group
GRANT readonly_group TO alice, bob, charlie;
-- All three can now SELECT (assuming default INHERIT)

-- Remove a user from the group
REVOKE readonly_group FROM bob;

✅ Safely Remove a User

-- 1. Reassign all objects owned by the user
REASSIGN OWNED BY old_user TO admin_user;

-- 2. Drop all remaining privileges (grants, default privileges, etc.)
DROP OWNED BY old_user;

-- 3. Now it's safe to drop the user
DROP USER old_user;

17. Best Practices