Do I need FLUSH PRIVILEGES after GRANT?

No. GRANT, CREATE USER, and DROP USER take effect immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly.

Is the generated password random?

Yes - 24 random bytes from your browser’s cryptographic random generator, as hex, so it needs no escaping in SQL, URLs, or shells. It is never sent anywhere.

How do I remove a PostgreSQL role that owns objects?

Reassign or drop what it owns first - `REASSIGN OWNED BY` and `DROP OWNED BY`, in every database it has objects in - then `DROP ROLE`. The generator writes these for you.

Free tool · no account

Least-privilege database user generator

Most applications connect to their database as a user that can do anything. Generate a read-only, application, or migrations user for PostgreSQL or MySQL, with a strong password, TLS, and an expiry where the database supports it - and the statements that remove it again.

Database

Access

User name

Database

Schema

Table owner (migrations role)

Expires

Password

Require TLS

Create the user

-- reporting: read-only in app.public
CREATE ROLE reporting WITH LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE app TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

-- Cover tables created later, too.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;

ALTER DEFAULT PRIVILEGES without FOR ROLE covers only tables created by the role running it. Set the owner to the role your migrations run as.
PostgreSQL enforces TLS in pg_hba.conf, not in SQL: use a hostssl line for reporting and no host line that also matches it.
On PostgreSQL 14 and earlier, every role can create objects in the public schema. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC; once.

Remove it again

-- Run in each database the role has objects or privileges in:
REASSIGN OWNED BY reporting TO CURRENT_USER;
DROP OWNED BY reporting;
DROP ROLE reporting;

Runs entirely in your browser. Nothing you enter is sent to Shortspan or saved, and no account is needed.

Next step

Hand out access, not the password

Register the user in Shortspan once, and shortspan db launches the client with a brokered credential - directly, through an SSH bastion, or on the server - so people connect without ever seeing the password.

Create a free workspace See what Shortspan does

Three roles, not one

A read-only user can query and nothing else - right for reporting and support. An application user can read and write rows but not change the schema. A migrations user can also create and alter tables, and only deploys should hold it. Splitting them means a SQL injection in the application cannot drop a table.

PostgreSQL default privileges

GRANT ... ON ALL TABLES covers tables that exist now. Tables created later need ALTER DEFAULT PRIVILEGES, and that only applies to tables created by the role named in FOR ROLE - set the owner to the role your migrations run as, or new tables will be invisible to the read-only user.

Expiry and TLS

PostgreSQL’s VALID UNTIL expires the password, not open sessions. MySQL has no account expiry date; PASSWORD EXPIRE INTERVAL forces a password change instead. TLS is required with REQUIRE SSL in MySQL, and with a hostssl line in pg_hba.conf for PostgreSQL.

Common questions

Do I need FLUSH PRIVILEGES after GRANT?: No. GRANT, CREATE USER, and DROP USER take effect immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly.
Is the generated password random?: Yes - 24 random bytes from your browser’s cryptographic random generator, as hex, so it needs no escaping in SQL, URLs, or shells. It is never sent anywhere.
How do I remove a PostgreSQL role that owns objects?: Reassign or drop what it owns first - REASSIGN OWNED BY and DROP OWNED BY, in every database it has objects in - then DROP ROLE. The generator writes these for you.

More free tools