Skip to content

Instantly share code, notes, and snippets.

@oinopion
Created October 5, 2016 13:00
Star You must be signed in to star a gist
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;
@s4l3h1
Copy link

s4l3h1 commented Sep 4, 2017

Thank you

@juliolvfilho
Copy link

Thank you very much!

@electropolis
Copy link

electropolis commented Oct 6, 2017

You are now connected to database "example" as user "user".
example=> select * from transaction;
ERROR: permission denied for relation transaction

Doesn't work.

This worked for me: https://stackoverflow.com/questions/13497352/error-permission-denied-for-relation-tablename-on-postgres-while-trying-a-selec

@dinesh24591
Copy link

But with this role we are able to create tables individully. how to restrict user from creating tables individually?

@samdesh-git
Copy link

I too have same query - How to stop read only users from creating tables?

@slavafomin
Copy link

It is allowed by default for every user to create tables in public schema. If you want to mitigate this, do the following:

  • REVOKE ALL ON SCHEMA public FROM public
  • GRANT ALL ON SCHEMA public TO writeuser

@lmoncada
Copy link

Thank you, it's very useful

@hanspoo
Copy link

hanspoo commented Mar 22, 2018

Hi, thanks for your script.

I work a lot with schemas, so i wrote a bash script that using your sql commands, echos shell commands to give read permissions on all schemas not just in public and it doesn't touch the database. Just paste generated lines on the shell to execute them against the real database.

#!/bin/bash

if [[ $# -eq 0 ]]; then
echo Echoes shell commands to give read permissions to a user in a database in all schemas.
echo "Usage: $0 user database"
exit 0
fi

if [[ $# -ne 2 ]]; then
echo Please give user and database
exit -1
fi

usage="select 'grant usage on schema ' || nspname ||' to $1;' from pg_catalog.pg_namespace;"
tables="select 'grant select on all tables in schema ' || nspname ||' to $1;' from pg_catalog.pg_namespace;"
default="select 'alter default privileges in schema ' || nspname ||' grant select on tables to $1;' from pg_catalog.pg_namespace;"

echo "psql -t -c "$usage" $2 | psql $2"
echo "psql -t -c "$tables" $2 | psql $2"
echo "psql -t -c "$default" $2 | psql $

@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