Skip to content

Instantly share code, notes, and snippets.

@nepsilon

nepsilon/how-to-create-a-read-only-user-for-your-database.md

Last active March 20, 2019 10:31
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save nepsilon/6c7c79aebcd7bcab8a9f85cd7897cd4c to your computer and use it in GitHub Desktop.
Save nepsilon/6c7c79aebcd7bcab8a9f85cd7897cd4c to your computer and use it in GitHub Desktop.
Download ZIP
How to create a read-only user for your database? — First published in fullweb.io issue #97
Raw
how-to-create-a-read-only-user-for-your-database.md

How to create a read-only user for your database?

If your app is about searching (business directories, dictionaries, etc) or a catalog of browsable items, it’s always a good idea to let your API use a read-only database user. Another use case would be for doing backups, no write permissions needed either.

Here is how to do that for PostgreSQL v9.0+ (syntax quasi-similar for MySQL):

-- Say we just created the user "pouet"

-- Allow the user to CONNECT
GRANT CONNECT ON DATABASE mydb TO pouet;

-- Allow the user to SELECT
GRANT USAGE ON SCHEMA public TO pouet;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pouet;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pouet;

You can then check the user’s permission with:

mydb=# \du+ pouet
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment