When connecting your database to any external tool — a BI platform, a query assistant, or an analytics service — you should never use your superuser or application user. Create a dedicated read-only user instead.
This guide covers the full setup for PostgreSQL.
Why this matters
A read-only user can only run SELECT queries. Even if credentials are leaked, an attacker cannot modify or delete data. It also limits accidental damage — no DROP TABLE from a mistyped query.
Step 1: Create the user
CREATE USER byesql_reader WITH PASSWORD 'choose-a-strong-password';
Pick a strong, randomly generated password. Use a password manager.
Step 2: Grant connection to the database
GRANT CONNECT ON DATABASE your_database_name TO byesql_reader;
Without this, the user cannot connect at all.
Step 3: Grant usage on the schema
PostgreSQL has a two-level namespace: databases contain schemas, and schemas contain tables. You need to grant access at both levels.
GRANT USAGE ON SCHEMA public TO byesql_reader;
If you use custom schemas (e.g. analytics, reporting), repeat this for each one:
GRANT USAGE ON SCHEMA analytics TO byesql_reader;
GRANT USAGE ON SCHEMA reporting TO byesql_reader;
Step 4: Grant SELECT on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO byesql_reader;
This grants SELECT on every table currently in the public schema. It does not apply to tables created in the future — that’s handled in the next step.
Step 5: Apply default privileges for future tables
This is the step most guides miss. Any table created after step 4 would not be accessible unless you re-run the grant. Default privileges solve this:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO byesql_reader;
Now any table added to public in the future will automatically be readable by byesql_reader.
If you have multiple schemas:
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO byesql_reader;
Step 6: Grant SELECT on sequences (if needed)
Some applications read sequence values (e.g., to understand auto-increment IDs). If you need this:
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO byesql_reader;
For most query workloads, this is not necessary.
Limiting access to specific tables only
If you want byesql_reader to access only certain tables — for example, to hide a payments or users table containing PII — do not run the blanket GRANT SELECT ON ALL TABLES. Instead, grant table by table:
GRANT SELECT ON TABLE orders TO byesql_reader;
GRANT SELECT ON TABLE products TO byesql_reader;
GRANT SELECT ON TABLE customers TO byesql_reader;
This way, the user can see exactly the tables you want and nothing else.
Verify the setup
Connect as the new user and confirm permissions:
-- Connect as byesql_reader, then:
SELECT * FROM orders LIMIT 1; -- should work
INSERT INTO orders VALUES (...); -- should fail with permission denied
You can also inspect grants from a superuser:
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'byesql_reader';
Revoking access
If you ever need to remove access:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM byesql_reader;
REVOKE USAGE ON SCHEMA public FROM byesql_reader;
REVOKE CONNECT ON DATABASE your_database_name FROM byesql_reader;
DROP USER byesql_reader;
A note on defense in depth
Creating a read-only database user is a good practice — but it is a database-level control. ByeSQL also enforces read-only access at the application level through SAFE mode, which blocks INSERT, UPDATE, DELETE, DROP, TRUNCATE, and ALTER operations before they reach your database.
Both layers together give you defense in depth: even if there is a bug in application-level enforcement, the database user cannot write. And even if the database user were somehow granted extra privileges, the application would still block write queries.
Start with the database-level control described here. Think of SAFE mode as the second lock.