Crate a new, read-only user in postgres

Question

I would like to create a new user in an existing postgresql database on an Ubuntu machine. I want to grant this user a read-only access to all the tables.

How do I do it? Do I need to create a new user on Ubuntu, too?

Thanks,

Udi

score 27 · Accepted Answer · answered Feb 17 '16 at 11:52

27

Reference taken from this Article !

Script to Create Read-Only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

answered Feb 17 '16 at 11:52

Anvesh

674
7
3

Impressive! A new, relevant answer after more than 6 years. – Adam Matan Feb 17 '16 at 11:59
Worth it. This was the top link for me when looking for "creating read-only user in Postgres". The top article having a relevant example is a win in my book. – Audrius Nov 28 '17 at 11:16

score 4 · Answer 2 · answered Aug 30 '09 at 14:02

No need to create a new user in the operating system. Just issue:

CREATE USER newusername

in psql (or pgadmin, or whatever tool you prefer) and you will have a new user. What access this user has depends on what your tables are set up for. But default, it will not have permissions on any tables, so you will have to GRANT this. If you have already GRANTed permissions to the public group, the user will have thos permissions from the start.

Crate a new, read-only user in postgres

2 Answers2

Linked