-- Create a basic role
CREATE ROLE myuser;

-- Create a role that can log in (essentially a "user")
CREATE ROLE myuser WITH LOGIN;

-- Create role with password
CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

-- Create role with multiple attributes
CREATE ROLE admin_user WITH
    LOGIN
    PASSWORD 'secret123'
    CREATEDB
    CREATEROLE
    SUPERUSER;

-- Common role attributes:
LOGIN          -- Can connect to database
SUPERUSER      -- Has all privileges
CREATEDB       -- Can create databases
CREATEROLE     -- Can create other roles
REPLICATION    -- Can initiate replication
PASSWORD       -- Set login password

-- Basic table privileges
GRANT SELECT ON table_name TO role_name;
GRANT INSERT ON table_name TO role_name;
GRANT UPDATE ON table_name TO role_name;
GRANT DELETE ON table_name TO role_name;

-- Multiple privileges at once
GRANT SELECT, INSERT, UPDATE ON customers TO sales_team;

-- All table privileges
GRANT ALL ON customers TO admin_user;
GRANT ALL PRIVILEGES ON customers TO admin_user;  -- same as above

-- Multiple tables
GRANT SELECT ON customers, orders, products TO readonly_user;

-- All tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

-- Schema access (required to access objects within)
GRANT USAGE ON SCHEMA sales TO sales_team;

-- Create objects in schema
GRANT CREATE ON SCHEMA public TO developers;

-- All privileges on schema
GRANT ALL ON SCHEMA inventory TO inventory_managers;

-- Revoke privileges
REVOKE INSERT ON customers FROM sales_team;
REVOKE ALL ON products FROM temp_user;

-- Create group roles
CREATE ROLE sales_team;
CREATE ROLE managers;

-- Grant privileges to groups
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sales_team;
GRANT sales_team TO managers;  -- managers inherit sales_team privileges

-- Add users to groups
CREATE ROLE alice WITH LOGIN PASSWORD 'alice123';
CREATE ROLE bob WITH LOGIN PASSWORD 'bob123';

GRANT sales_team TO alice;
GRANT managers TO bob;  -- bob gets both managers and sales_team privileges

SELECT
    rolname as "Role",
    rolsuper as "Superuser",
    rolcreaterole as "Create role",
    rolcreatedb as "Create DB",
    rolcanlogin as "Can login",
    rolinherit as "Inherits",
    rolreplication as "Replication"
FROM pg_roles
ORDER BY rolname;

select
	member.rolname as "Member Role",
	string_agg(role.rolname, ', ') as "Member of Roles"
from
	pg_roles member
left join pg_auth_members am on
	member.oid = am.member
left join pg_roles role on
	am.roleid = role.oid
group by
	member.rolname
order by
	member.rolname;