#PostgreSQL Permissions

-- Create login role
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- Create role with database
CREATE ROLE app_user WITH LOGIN PASSWORD 'password123' CREATEDB;

-- Grant privileges on table
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

-- Grant all privileges
GRANT ALL PRIVILEGES ON users TO admin;

-- Grant on schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant on database
GRANT CONNECT ON DATABASE mydb TO app_user;

REVOKE INSERT, UPDATE ON users FROM app_user;
REVOKE ALL PRIVILEGES ON users FROM guest;

-- Superuser
CREATE ROLE super WITH LOGIN PASSWORD 'pass' SUPERUSER;

-- Create database
CREATE ROLE db_admin WITH LOGIN PASSWORD 'pass' CREATEDB;

-- Create role
CREATE ROLE role_admin WITH LOGIN PASSWORD 'pass' CREATEROLE;

-- Replication
CREATE ROLE replicator WITH LOGIN PASSWORD 'pass' REPLICATION;

-- Create group
CREATE GROUP developers;

-- Add members
GRANT developers TO alice;
GRANT developers TO bob;

-- Grant to group
GRANT SELECT ON products TO developers;

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY user_policy ON users
    FOR SELECT
    USING (user_id = current_setting('app.current_user')::INTEGER);