Create a User for Select Only on Postgresql. Restrict "ALTER TABLE"

Question

I am using fallowing scripts for Create a user.

CREATE ROLE readonly LOGIN PASSWORD 'thePwd';

-- Existing objects

GRANT CONNECT ON DATABASE the_db TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

After Create User & Role; the "readonly" user have "SELECT" permission. No Drop or Truncate rights. But "readonly" user have right "Alter Table" command.

How can I restrict for a specific user "Alter Table" rights?

Is there a simple example?

Did you CONNECT to the_db before the GRANT USAGE ON SCHEMA ... ? — wildplasser
– wildplasser, Commented Sep 2, 2015 at 12:17
Please show the command that works but shouldn't and the output of \dp tablename on the affected table — Craig Ringer
– Craig Ringer, Commented Sep 2, 2015 at 12:41
I used the above SQL Scripts, Step by step. The readonly user working very well as expected by SELECT scripts ( no Drop, no Truncate ) ; expect for 'ALTER TABLE SAMPLE ADD/DROP Column "column_sample" ' — ugurlu2001
– ugurlu2001, Commented Sep 2, 2015 at 14:24

ugurlu2001 · Accepted Answer · 2015-09-03 12:03:11Z

2

The solution is at the following script;

create user dummy_user with nosuperuser encrypted password 'dummy_password';

GRANT CONNECT ON DATABASE the_db TO dummy_user;

GRANT USAGE ON SCHEMA public TO dummy_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dummy_user;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dummy_user;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO dummy_user;

answered Sep 3, 2015 at 12:03

ugurlu2001

415 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashutosh Tiwari Over a year ago

I am still facing issue after this :( Able to run alter command still.

Collectives™ on Stack Overflow

Create a User for Select Only on Postgresql. Restrict "ALTER TABLE"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related