How to build a read-only account in PostgreSQL

Turn: How to build a read-only account in PostgreSQL posted on 2014-01-21 22:00:15 by OSDBA

There is no CREATE TABLE permission name in PostgreSQL, which is a different place than other databases, and PostgreSQL is installed by default, by controlling whether a user can create a table on the schema schema. Any user has permission to create in mode public, so the first step in creating a read-only account is to first remove the Create permission in the schema public:

REVOKE  CREATE  ON SCHEMA public from public;

The following SQL creates a user named "ReadOnly":

CREATE USER readonly with password ‘query‘;

Then give the user readonly the SELECT permission for all existing tables under the public schema, and execute the following SQL command:

GRANT SELECT ON  ALL TABLES IN SCHEMA public TO readonly;

The above SQL command only gives the existing table permissions to the user readonly, but if the table created after this, the readonly user still cannot read, need to use the following SQL to create the table after the SELECT permission also to the user readonly:

Note: The above procedure simply assigns read-only permissions to the table under the schema named Public, and if you want this user to be able to access tables under other schemas, repeat:

GRANT SELECT ON  ALL TABLES IN SCHEMA other_schema TO readonly;ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;

How to build a read-only account in PostgreSQL