Search code examples
postgresqlpermissionsaltersql-grant

Revoking ALTER permissions from users in PostgreSQL

GRANT ALL ON TABLE <table_name> TO <user_name>;

The above statement gives permission to a particular user to ALTER the table structures as well to users. How can we restrict the permission of ALTER to tables to users? I want the users only to SELECT, INSERT, UPDATE and DELETE records rather than allowing them to ALTER the DB structures as well?

Will the below statement help me in achieving my requirement?

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <table_name> TO <user_name>;

I want the issue related to postgres.

Solution

  • Refer to http://www.postgresql.org/docs/9.3/static/sql-grant.html & http://www.postgresql.org/docs/9.3/static/sql-revoke.html

    As you can see GRANT and REVOKE will do the job.

    Suggestion: Keep in mind to use groups for the privileges and handle the assignment by membership to these groups.