How to create read only user in PostgreSQL

read-access.sql

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

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

Thank you, it's very useful

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 $

@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

AWSM!

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;

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.

Thank you very much,

Thank you @tomek @slavafomin

tks man

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;

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

Thanks it's useful

This is golden

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