Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

s4l3h1 commented Sep 4, 2017

Thank you

@juliolvfilho

This comment has been minimized.

Copy link

juliolvfilho commented Sep 16, 2017

Thank you very much!

@electropolis

This comment has been minimized.

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

This comment has been minimized.

Copy link

dinesh24591 commented Oct 20, 2017

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

@samdesh-git

This comment has been minimized.

Copy link

samdesh-git commented Nov 3, 2017

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

@slavafomin

This comment has been minimized.

Copy link

slavafomin commented Nov 6, 2017

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

This comment has been minimized.

Copy link

lmoncada commented Jan 26, 2018

Thank you, it's very useful

@hanspoo

This comment has been minimized.

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

This comment has been minimized.

Copy link

JohnVonNeumann commented Mar 28, 2018

@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

This comment has been minimized.

Copy link

fedek6 commented Jul 6, 2018

AWSM!

@zxdvd

This comment has been minimized.

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;
@indrajeetdfoundry

This comment has been minimized.

Copy link

indrajeetdfoundry commented Jan 2, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.