Skip to content

Instantly share code, notes, and snippets.

@oinopion
Created October 5, 2016 13:00
Show Gist options
  • Save oinopion/4a207726edba8b99fd0be31cb28124d0 to your computer and use it in GitHub Desktop.
Save oinopion/4a207726edba8b99fd0be31cb28124d0 to your computer and use it in GitHub Desktop.
How to create read only user in PostgreSQL
-- Create a group
CREATE ROLE readaccess;
-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;
-- Create a final user with password
CREATE USER tomek WITH PASSWORD 'secret';
GRANT readaccess TO tomek;
@lcostamanha
Copy link

tks man

@verma-riti
Copy link

CREATE ROLE readaccess;

GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

GRANT CONNECT ON DATABASE 'database_name' to readaccess;

CREATE USER user_name WITH PASSWORD ‘password’;
GRANT readaccess TO user_name;

@jean-tymate
Copy link

GRANT REFERENCES ON ALL TABLES IN SCHEMA public TO readaccess;
To be able to see PrimaryKey constraint

@DevOps-Emre
Copy link

Thanks it's useful

@bl4ck5un
Copy link

This is golden

@prosenjit-manna
Copy link

Getting permission denied error on viewing list of data from any tables

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment