PostgreSQL — Roles

Create Role

CREATE ROLE myuser WITH LOGIN PASSWORD 'password';

Create User

CREATE USER myuser WITH PASSWORD 'password';

Grant Privileges

-- Grant database
GRANT CONNECT ON DATABASE mydb TO myuser;

-- Grant schema
GRANT USAGE ON SCHEMA public TO myuser;

-- Grant table
GRANT SELECT, INSERT, UPDATE ON users TO myuser;

Revoke Privileges

REVOKE INSERT ON users FROM myuser;

Role Attributes

Attribute	Description
LOGIN	Can connect
SUPERUSER	Full access
CREATEDB	Create databases
CREATEROLE	Create roles
REPLICATION	Replication

List Roles

\du
SELECT rolname FROM pg_roles;

Drop Role

DROP ROLE myuser;

Mini Practice

1. Create a role
2. Grant privileges
3. Revoke privileges
4. List all roles

Up Next

Continue with Permissions — permission management.