Postgres Create User: Logins, Grants and Read-Only Access
Sagar Tayde · · 5 min read
Postgres create user is only half the job: a new login also needs the grants that decide what it can touch.
Most guides stop after CREATE USER, and the new login then fails with “permission denied” on its first query.
This guide covers the full path - the login, the grants, a read-only user for a reporting tool, a separate login per application, and the one detail that catches teams who turn on row-level security: the database owner is not limited by it.
Every command here is plain PostgreSQL and is described in the official CREATE ROLE reference.
A user is a role that can log in
PostgreSQL has one object for both users and groups: the role.
CREATE USER is CREATE ROLE with LOGIN switched on, so these two lines do the same thing:
CREATE USER reporting WITH PASSWORD 'a-long-random-password';
CREATE ROLE reporting WITH LOGIN PASSWORD 'a-long-random-password';
Use a generated password, not one you type.
Since PostgreSQL 14 the default storage is SCRAM, which never keeps the password itself - only a verifier.
Avoid SUPERUSER and CREATEROLE for anything an application or a tool uses: a superuser bypasses every permission check, including the ones this guide sets up.
The grants a new login needs before it can read anything
A fresh login can connect to a database only if PUBLIC still has CONNECT, and it can read nothing.
Privileges are layered - database, then schema, then table - and a missing layer gives the same “permission denied” error as a missing table grant:
GRANT CONNECT ON DATABASE appdb TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
The third line covers tables that exist today and nothing created tomorrow.
For future tables, add a default privilege, run as the role that creates the tables:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
The full list of object types and privileges is in the GRANT reference.
A read-only login for a dashboard or an analyst
The three grants above give reporting SELECT and nothing else, so an UPDATE or DROP from a dashboard fails.
On PostgreSQL 14 and older there is one gap: every role can create tables in the public schema by default.
PostgreSQL 15 removed that default.
On 14, close it once:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Two more habits keep it read-only.
Do not grant it membership in your application’s role, because membership carries every privilege of that role.
And give it a short statement_timeout, so one slow chart cannot hold locks for minutes:
ALTER ROLE reporting SET statement_timeout = '30s';
One login per application, not one shared password
When three services and a laptop share the owner login, you cannot revoke one of them, you cannot see which one ran a query, and rotating the password means redeploying everything at once.
A login per application fixes all three.
It also gives you a rotation path with no downtime: create orders_v2 with the same grants and default privileges as orders, move the service to it, confirm it works, then drop orders.
Both logins work during the switch, so nothing is locked out.
To remove a login that owns tables, hand its objects over first, or DROP ROLE refuses:
REASSIGN OWNED BY orders TO app_owner;
DROP OWNED BY orders;
DROP ROLE orders;
Row-level security does not apply to the owner
This is the step that surprises teams building multi-tenant apps.
Row-level security policies apply to ordinary roles, but the table owner skips them unless the table is set to FORCE ROW LEVEL SECURITY, and a superuser always skips them.
If your application connects as the role that created the tables, your policies silently do nothing.
The fix is a separate login for the application that owns nothing, with the tenant passed inside each transaction so it cannot leak to the next request on a pooled connection:
BEGIN;
SELECT set_config('app.tenant', 'acme', true); -- true = this transaction only
SELECT * FROM invoices; -- the policy filters by app.tenant
COMMIT;
The PostgreSQL row security guide covers policy syntax in detail.
When a pooler refuses the new login
A connection pooler such as PgBouncer authenticates clients itself before PostgreSQL sees them.
If it reads users only from its own file, a login you just created fails at the pooler with “no such user”, while the owner keeps working - and nothing appears in the PostgreSQL log, because the connection never got that far.
The pooler has to look users up in PostgreSQL instead.
If you see that error right after creating a login, check the pooler before you check your grants.
Adding a user on Voroa
On a managed PostgreSQL database on Voroa you can add a login from the dashboard and get its connection URL once, or run CREATE USER yourself.
A login added from the dashboard can read and write all data and create tables. For a read-only login, create it with SQL as shown above.
A login you create with SQL connects through the same address as the main user when it has a SCRAM password (the default since PostgreSQL 14), is not a replication role, and its name does not start with voroa_.
That connection shares server connections between transactions, so use SET LOCAL inside a transaction rather than a session-wide SET.
The database users guide explains both, including which logins the dashboard lists.
Your connection details show the address to use, and connecting from outside covers tools on your own machine.
If you are moving from another provider, our guide to backups on Supabase and our comparison of Neon and Supabase for teams in India cover what to check before you switch.
FAQ
What is the difference between CREATE USER and CREATE ROLE?
Only the default: CREATE USER adds LOGIN.
Everything else is the same command.
Why does my new user get “permission denied”?
It is usually a missing USAGE ON SCHEMA, not a missing table grant.
Check all three layers.
Can I list users? Yes.
In psql, run \du, or query pg_roles.