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;
@JohnVonNeumann
Copy link

@electropolis were you granting on the correct database? were you connected when you did it? i noticed the same issue and found it was because i was granting for A database, not the correct one though

@fedek6
Copy link

fedek6 commented Jul 6, 2018

AWSM!

@zxdvd
Copy link

zxdvd commented Jul 19, 2018

If you want this readonly user to use pg_dump, you may also need to grant access to sequences.

-- Grant access to existing tables
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readaccess;

@indrajeetw
Copy link

Hi there are four databases present in this server. When i create this user, that user can create tables in different databases. I want to restrict that too.

@bouleetbille
Copy link

Thank you very much,

@HaoDuong125
Copy link

Thank you @tomek @slavafomin

@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

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