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
  • 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

Revisions

  1. nepsilon renamed this gist May 2, 2017. 1 changed file with 0 additions and 0 deletions.
    0 ...reate a read-only user for your database? → ...ate-a-read-only-user-for-your-database.md
    View file
    File renamed without changes.
  2. nepsilon created this gist May 2, 2017.
    21 changes: 21 additions & 0 deletions How to create a read-only user for your database?
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    # 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):
    ```sql
    -- 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
    ```