Insert- and Read-only table in Postgres

Question

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
);

Answer 1

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:

• Safely rename tables using serial primary key columns

To make sure of the name of the attached sequence, use pg_get_serial_sequence(). See:

• PostgreSQL SELECT primary key as "serial" or "bigserial"

You also need the USAGE privilege on the schema and CONNECT of the database, both of which are granted by default for schema public.

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:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

Answer 2

An option is to use table level triggers which prevent DELETE and UPDATE operations. These kind of triggers usually prevent data updates regardless of user privileges. Also you should use some mechanism to prevent TRUNCATE, to make sure, that table cannot be purged, for example BEFORE TRUNCATE triggers or some foreign key constraint references.

This will help to create “read-only” table.

Please keep in mind that’s not system level read-only, but if properly implemented, then still brilliant and well working solution.