Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 2 down vote accepted

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;
share|improve this answer
    
Impressive! A new, relevant answer after more than 6 years. – Adam Matan Feb 17 at 11:59
    
Thanks Adam Matan ! – Anvesh Feb 17 at 12:10

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.