How to Create Users and Grants From the Shell Safely

An application that connects as the database owner or root can drop its own tables, read other databases and turn a small injection bug into total loss. A dedicated account with only the needed rights limits the damage. Creating it from a script makes setup repeatable, and a few habits make that safe. It builds on psql and safe password handling. The examples assume PostgreSQL 17 and MySQL 8.4.

PostgreSQL

APP_PASSWORD=$(openssl rand -base64 24 | tr -d '/+=')
psql -U postgres -v ON_ERROR_STOP=1 -v app_pw="$APP_PASSWORD" <<'SQL'
SELECT 'CREATE ROLE app LOGIN' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app')\gexec
ALTER ROLE app PASSWORD :'app_pw';
CREATE DATABASE appdb OWNER postgres;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app;
\c appdb
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
SQL

The \gexec line creates the role only if it does not exist, which makes reruns safe. -v app_pw=... with the :'app_pw' reference quotes the password correctly as a literal, so no shell string-building enters the SQL. The role gets connect rights and DML on the schema's tables, and nothing to create or drop objects. ALTER DEFAULT PRIVILEGES covers tables created later by the same owner, a step people forget until a new table gives "permission denied". The password variable is visible in the process list for the moment psql starts, so for stronger secrecy use a file with \set app_pw \`cat /run/secrets/app_pw\`.

MySQL

mysql --defaults-extra-file=/root/.my.cnf <<SQL
CREATE DATABASE IF NOT EXISTS appdb CHARACTER SET utf8mb4;
CREATE USER IF NOT EXISTS 'app'@'10.0.%' IDENTIFIED BY '${APP_PASSWORD}';
ALTER USER 'app'@'10.0.%' IDENTIFIED BY '${APP_PASSWORD}';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'10.0.%';
FLUSH PRIVILEGES;
SQL
mysql --defaults-extra-file=/root/.my.cnf -e "SHOW GRANTS FOR 'app'@'10.0.%'"

GRANT USAGE ON *.* TO `app`@`10.0.%`
GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `app`@`10.0.%`

MySQL accounts are a user and a host, so 'app'@'10.0.%' allows the application servers' subnet and refuses others. Avoid '%' unless the network is otherwise locked down. The admin credentials come from an options file with mode 600, not from -p on the command line, as in pgpass and cnf files. The here-document without quotes expands ${APP_PASSWORD} in the shell, so the password is part of the text sent over stdin and does not appear in ps; passwords containing a single quote need escaping.

Verify as the new user

PGPASSWORD="$APP_PASSWORD" psql -h localhost -U app -d appdb -c "SELECT 1"
PGPASSWORD="$APP_PASSWORD" psql -h localhost -U app -d appdb -c "CREATE TABLE t (id int)" 2>&1 | head -1
PGPASSWORD="$APP_PASSWORD" psql -h localhost -U app -d postgres -c "SELECT 1" 2>&1 | head -1

 ?column?
----------
        1
ERROR:  permission denied for schema public
FATAL:  permission denied for database "postgres"

A positive test proves the account works, and negative tests prove the limits: it cannot create tables and cannot connect to other databases. Run them in the setup script and exit non-zero on a surprise. PGPASSWORD in the environment is acceptable for a short-lived test but is inherited by children, see environment variable leaks.

Separate roles by purpose

Use one account for the application (DML only), one for migrations (DDL, used at deploy time), one read-only account for reporting (SELECT) and one for backups (read plus replication rights). This way a leaked reporting credential cannot modify data. Prefer connecting over TLS and restricting by network in pg_hba.conf or the MySQL host part. Rotate passwords on a schedule, as in rotating credentials.

Why the application should not own the schema

An application account that can create and drop tables is one SQL injection away from losing the schema. If it can read every database on the server, the same bug leaks data from systems it was never meant to touch. Separating the account that changes structure from the account that reads and writes rows is the cheapest control in a database setup, and it costs almost nothing to maintain. The rule that follows is simple to state: every account gets the smallest set of rights that lets its job succeed, and the set is written in a script, not typed by hand on the server.

A script also gives you a record. Months later someone asks why the reporting account can read the billing table, and the answer is a line in a file with a commit message, not a half-remembered session. Running the same script against a fresh database in a test pipeline proves that the documented rights are the ones that actually exist, which a manual setup never does.

Making the script safe to run twice

Setup scripts get run more than once: after a failed attempt, on a new environment, after a restore. Write every statement so a second run changes nothing. In PostgreSQL that means creating a role only when it is missing, and setting the password in a separate statement so a rerun can rotate it. In MySQL, CREATE USER IF NOT EXISTS followed by ALTER USER does the same. Grants are idempotent by nature, because granting a right that is already held does nothing.

Stop on the first error. Without ON_ERROR_STOP in psql, the client continues after a failed statement, and the script finishes with a success status having applied half of its changes. In the mysql client, a similar effect comes from running with --abort-on-error or from piping statements through a script that checks the exit code. A setup that fails loudly at statement four is much cheaper to fix than one that quietly skipped it.

Handling the password

The generated secret has to go to two places: the database and the application. Generate it once with a cryptographic source such as openssl rand, send it to the database through standard input or a variable rather than a command argument, and write it to the application's secret store in the same script. Do not echo it to the terminal, because terminal output ends up in scrollback and in the logs of whatever runs the script. If the script runs in a CI job, the secret should flow from the job to the secret manager without being printed, and masking in the CI log is a backup, not a plan.

Characters matter more than people expect. A password that contains a quote, a dollar sign or a backslash breaks a statement built by string concatenation, and in the worst case turns into an injection of its own. Restrict generated passwords to letters and digits of sufficient length, as the tr filter above does, or use a client feature that binds the value as a parameter. Both avoid the problem without relying on escaping that someone will get wrong later.

Auditing and removing rights

Granting is half the job. Add a script or query that lists what each account can do, so a review is a diff against an expected list: in PostgreSQL the \du and \dp commands, and in MySQL SHOW GRANTS FOR. Run it on a schedule and after every migration that adds tables. When an application is retired, drop its account in the same change that removes its configuration, since a forgotten login with a known password is a standing entry point. Accounts that exist only in the database and are referenced nowhere else are the ones attackers hope to find.

Where this does not apply

Managed services have their own user models and sometimes forbid superuser access, with an admin user that has slightly fewer powers. Row-level security and column grants give finer control than table-level grants. For short-lived dynamic credentials, a secrets manager such as Vault can create and expire database users on demand. See the PostgreSQL GRANT documentation and the MySQL GRANT reference.