PostgreSQL — Permissions

Grant Permissions

-- Database level
GRANT CONNECT ON DATABASE mydb TO myuser;

-- Schema level
GRANT USAGE ON SCHEMA public TO myuser;

-- Table level
GRANT SELECT ON users TO myuser;
GRANT ALL ON users TO myuser;

Revoke Permissions

REVOKE INSERT ON users FROM myuser;
REVOKE ALL ON users FROM myuser;

Grant to All

GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

Default Privileges

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

Check Permissions

-- Table permissions
SELECT grantee, privilege_type 
FROM information_schema.table_privileges 
WHERE table_name = 'users';

Permission Types

Permission	Description
SELECT	Read data
INSERT	Add data
UPDATE	Modify data
DELETE	Remove data
TRUNCATE	Clear table
REFERENCES	Foreign keys
TRIGGER	Create triggers

Mini Practice

1. Grant SELECT permission
2. Grant multiple permissions
3. Revoke permissions
4. Check current permissions

Up Next

Continue with Backup — backup and recovery.