1

I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete).

I came to know there is FOR UPDATE lock, but did not get how to use it.

Let's say for example I've the below table, how can I make it immutable (or, if I understood correctly, how can I use FOR UPDATE lock permanently)?

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);
  • 1
    Why don't you just lock the database down using permission sets/roles? – Mr.Brownstone Mar 18 at 20:51
  • 1
    SELECT FOR UPDATE is not permanent (it is tied to the open transaction/session) and it is not intended to block write access for your purpose. You make a table immutable by using database and/or filesystem permissions, instead. – eckes Mar 18 at 21:08
2

Assuming that "user" does not include any roles with superuser privileges (who can do everything), run this as owner of the table or superuser:

REVOKE ALL ON TABLE account FROM public;
GRANT SELECT, INSERT ON TABLE account TO public;

Users also need the USAGE privilege for the SEQUENCE attached to the serial column:

GRANT USAGE ON SEQUENCE account_user_id_seq TO public;

About serial:

.. and the USAGE privilege on the schema and CONNECT of the database, both of which are the case for schema public by default.

If you only want a subset of users to be affected, grant privileges to a group role instead of public and grant membership in that role to those users. You still need to revoke privileges from the pseudo-role public, which can be viewed as the default group role granting membership to all (irrevocably). And grant whatever is still needed to others (by way of another group role, for instance).

CREATE ROLE my_group;
GRANT my_group TO my_user1;
GRANT my_group TO my_user2;  -- more?
REVOKE ... FROM public;
GRANT SELECT, INSERT ON TABLE account TO my_group;
GRANT USAGE ON SEQUENCE account_user_id_seq TO my_group;

CREATE ROLE others;
GRANT others TO other_user1;
GRANT others TO other_user2;
GRANT ... TO others;

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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