How to Create a Read-Only Database User

Q: How do I grant read-only access to all tables?

In PostgreSQL use GRANT SELECT ON ALL TABLES IN SCHEMA public plus ALTER DEFAULT PRIVILEGES for future tables, or GRANT pg_read_all_data on version 14+. In MySQL use GRANT SELECT ON your_database.*. In SQL Server add the user to the db_datareader role, which covers all current and future tables in the database.

Q: Does a read-only user cover tables created later?

Not automatically in PostgreSQL unless you set ALTER DEFAULT PRIVILEGES (or use the pg_read_all_data role on 14+). In SQL Server the db_datareader role covers future tables automatically. In MySQL a database-level GRANT SELECT ON db.* applies to tables added later.

Q: Can I make an existing user read-only?

Yes. Revoke write privileges with REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ... in PostgreSQL or MySQL, or remove the user from the db_datawriter role in SQL Server. Creating a dedicated read-only user is usually cleaner than trying to strip permissions from a shared account.

Q: Can the service account write to BigQuery?

No. With only BigQuery Data Viewer and BigQuery Job User granted, the service account can read tables and run queries, but it cannot create, modify, or delete anything. Per-query costs are also capped by SaturnSQL's max-GB-billed setting, which defaults to 10 GB.

Last updated July 21, 2026 · By the SaturnSQL team

A read-only database user is a login that can run SELECT queries but cannot insert, update, delete, or change your schema. It is the safest way to connect a BI or SQL tool to a production database: even a mistaken query or a compromised token can only read. This guide gives you copy-paste SQL for PostgreSQL, MySQL, and SQL Server, the equivalent IAM setup for BigQuery, plus how to verify the account is truly read-only.

Jump to your database: PostgreSQL, MySQL, SQL Server, BigQuery.

Why use a read-only database user?

Connecting an analytics tool with a full-access account is a common but risky habit. A read-only user gives you:

Least privilege: the credentials can only do what analytics needs, which is read data.
A hard guarantee: enforcement lives in the database, so no application bug, typo, or clever query can write.
A smaller blast radius: if the credentials ever leak, there is no path to data loss or tampering.

Best practice: the database account is the hard guarantee. Pairing it with a tool that also enforces read-only on its side (for example by running queries inside a read-only transaction on PostgreSQL and MySQL, as SaturnSQL does) adds a useful second layer, but whatever tool you use, the read-only user below is what matters most.

How to create a read-only user in PostgreSQL

Connect as a superuser (or a role with the right privileges) and run the following, replacing the name, password, and database. This creates the user, lets it reach the schema, and grants read access to every existing table.

-- 1. Create the login role
CREATE USER readonly_user WITH PASSWORD 'strong-password-here';

-- 2. Allow it to connect to the database
GRANT CONNECT ON DATABASE your_database TO readonly_user;

-- 3. Allow it to see the schema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- 4. Grant read access to all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

The step teams most often forget is future tables. The grant above only covers tables that exist right now. Any table created later will be invisible to the read-only user unless you set default privileges:

-- 5. Make sure future tables are readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

The shortcut on PostgreSQL 14 and newer

PostgreSQL 14 introduced a built-in role, pg_read_all_data, that grants read access to all tables, views, and sequences (including ones created later) in one line. If you are on 14+, this replaces steps 3 to 5:

CREATE USER readonly_user WITH PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE your_database TO readonly_user;
GRANT pg_read_all_data TO readonly_user;

How to create a read-only user in MySQL

In MySQL (and MariaDB), a single database-level GRANT SELECT covers current and future tables in that database. The '%' after the username allows the user to connect from any host; restrict it to a specific IP if you can.

-- 1. Create the user
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'strong-password-here';

-- 2. Grant read-only access to one database
GRANT SELECT ON your_database.* TO 'readonly_user'@'%';

-- 3. Apply the changes
FLUSH PRIVILEGES;

To grant read access across every database on the server, use GRANT SELECT ON *.* TO 'readonly_user'@'%' instead of a single database. Prefer scoping to just the database your reporting needs.

How to create a read-only user in SQL Server

SQL Server splits the concept into a server-level login and a database-level user. The built-in db_datareader role grants SELECT on all tables and views in the database, including ones added later.

-- 1. Create a server login
CREATE LOGIN readonly_user WITH PASSWORD = 'Strong-Password-Here!';

-- 2. Create a database user for that login
USE your_database;
CREATE USER readonly_user FOR LOGIN readonly_user;

-- 3. Add the user to the built-in read-only role
ALTER ROLE db_datareader ADD MEMBER readonly_user;

Why this matters most on SQL Server: unlike PostgreSQL and MySQL, SQL Server has no read-only transaction mode a tool can wrap a query in. That means a read-only account is the only reliable way to guarantee a SQL Server connection cannot write. If you connect SQL Server to any external tool, use one.

BigQuery: a read-only service account

BigQuery does not have logins and GRANT statements the way PostgreSQL, MySQL, and SQL Server do. Instead, you create a service account and hand it only the IAM roles it needs to read data and run queries.

In the Google Cloud console, go to IAM & Admin → Service Accounts and click Create service account.
Grant exactly two roles, nothing else: BigQuery Data Viewer (roles/bigquery.dataViewer) and BigQuery Job User (roles/bigquery.jobUser).
Open the service account, go to Keys → Add key → Create new key, choose JSON, and download the file.
Paste the contents of that JSON file into SaturnSQL's connection form.

BigQuery has no in-band read-only flag the way a Postgres role or SQL Server login can be granted one. These two IAM roles are the read-only enforcement: bigquery.dataViewer can read table data and metadata, and bigquery.jobUser can run query jobs, but neither can create, delete, or modify tables, datasets, or the service account itself. There is no write path available to this credential.

Cost control: every query SaturnSQL runs against BigQuery also carries a maximum bytes billed cap, defaulting to 10 GB (about $0.06 at on-demand pricing). BigQuery refuses to run any query that would scan more than the cap, and you can raise or lower it per connection.

The next two sections (verifying the account and connecting it to SaturnSQL) apply to PostgreSQL, MySQL, and SQL Server. For BigQuery, the IAM roles above are the whole story, so feel free to skip ahead to the FAQ.

How to verify the user is truly read-only

Do not assume the grants worked. Log in as the new user and try to write. The write should be refused. In PostgreSQL, for example:

-- Reads should work
SELECT count(*) FROM your_table;

-- Writes should be refused
INSERT INTO your_table (id) VALUES (1);
-- ERROR: permission denied for table your_table

If the SELECT succeeds and the INSERT is rejected, the account is set up correctly. Run the same check with an UPDATE or DELETE if you want to be thorough.

Connect your read-only user to your SQL tool

Once the user exists, use the new read-only credentials in your SQL or BI tool instead of an admin account, and turn on any read-only option the tool offers. That combination, a read-only account plus a tool that respects it, is the setup we recommend for anything touching production data.

In SaturnSQL, add it as a connection (see the connecting a database guide) and switch on the Read-only mode toggle, so the database refuses writes and the tool refuses to send them.

Frequently asked questions

How do I grant read-only access to all tables?

In PostgreSQL use GRANT SELECT ON ALL TABLES IN SCHEMA public plus ALTER DEFAULT PRIVILEGES for future tables, or GRANT pg_read_all_data on version 14+. In MySQL use GRANT SELECT ON your_database.*. In SQL Server add the user to the db_datareader role, which covers all current and future tables.

Does a read-only user cover tables created later?

Not automatically in PostgreSQL unless you set ALTER DEFAULT PRIVILEGES (or use pg_read_all_data on 14+). In SQL Server, db_datareader covers future tables automatically. In MySQL, a database-level GRANT SELECT ON db.* applies to tables added later.

Do I still need read-only mode in my tool if the user is read-only?

A read-only user is the strongest single guarantee, since the database itself refuses writes on every connection (the only option that also covers SQL Server). A tool-side read-only mode is a helpful second layer: some tools, SaturnSQL included, check the query and run it inside a read-only transaction on PostgreSQL and MySQL. Using both is the safest setup.

Can I make an existing user read-only?

Yes. Revoke write privileges with REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ... in PostgreSQL or MySQL, or remove the user from the db_datawriter role in SQL Server. Creating a dedicated read-only user is usually cleaner than stripping permissions from a shared account.

Can the service account write to BigQuery?

No. With only BigQuery Data Viewer and BigQuery Job Usergranted, the service account can read tables and run queries, but it cannot create, modify, or delete anything. Per-query costs are also capped by SaturnSQL's max-GB-billed setting, which defaults to 10 GB.