11

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

2 Answers 2

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;
2
  • Impressive! A new, relevant answer after more than 6 years.
    – Adam Matan
    Feb 17, 2016 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, 2017 at 11:16
4

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .