PostgreSQL: Read-Only Role

Create a secure, minimal-privilege PostgreSQL role for FreshGuard.

This guide walks you through creating a dedicated read-only PostgreSQL role for FreshGuard. Using a restricted role ensures FreshGuard can only read data — it cannot modify tables, insert rows, or change schema.

Create the Role and User

Connect to your PostgreSQL database as a superuser or a user with CREATEROLE privileges, then run:

-- Create a role with no login (used as a group role)
CREATE ROLE freshguard_readonly NOLOGIN;

-- Grant connect to the target database
GRANT CONNECT ON DATABASE your_database TO freshguard_readonly;

-- Grant usage on schemas you want to monitor
GRANT USAGE ON SCHEMA public TO freshguard_readonly;

-- Grant SELECT on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO freshguard_readonly;

-- Grant SELECT on tables created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO freshguard_readonly;

Now create a login user that inherits from this role:

-- Create a user with a strong password
CREATE USER freshguard_user WITH PASSWORD 'your-strong-password-here'
  LOGIN IN ROLE freshguard_readonly;

Use a strong password

Generate a random password of at least 32 characters. Never reuse passwords from other services.

Multiple Schemas

If you need to monitor tables across multiple schemas, repeat the GRANT statements for each schema:

GRANT USAGE ON SCHEMA analytics TO freshguard_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO freshguard_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO freshguard_readonly;

Verify Permissions

Connect as the new user and confirm it can read but not write:

-- This should work
SELECT COUNT(*) FROM public.your_table;

-- This should fail with "permission denied"
INSERT INTO public.your_table (id) VALUES (0);

Cloud Provider Notes

Amazon RDS / Aurora

The setup above works as-is on RDS and Aurora. Connect using the master user or any user with CREATEROLE privileges.

Google Cloud SQL

Cloud SQL supports standard PostgreSQL roles. Connect via Cloud SQL Proxy or the public IP (with SSL required) and run the same commands.

Neon

Neon uses standard PostgreSQL roles. Connect to your Neon database using the connection string from your Neon dashboard and run the commands above.

Tip

After creating the role, use freshguard_user as the username and the password you set when connecting PostgreSQL in FreshGuard.