CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1postgresql-8.4postgresql-9.0
Stefan Steiger
Stefan Steiger

Reputation: 82186

How to create a read-only user in PostgreSQL?

I want to create a read-only user in PostgreSQL.

The intention is to have a publicly accessible data explorer, where users can write custom SQL queries.

I do this to create the user:

CREATE USER MyReadOnlyUser WITH ENCRYPTED PASSWORD 'MY_WEAK_PASSWORD';

GRANT ALL PRIVILEGES ON DATABASE "MY_DB_NAME" to MyReadOnlyUser;

GRANT ALL ON SCHEMA public TO MyReadOnlyUser; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO MyReadOnlyUser;

The intention is to give the user SELECT access to all tables, then revoke select access on the sensitive tables, and then the user can run custom queries with that db user, without any need for me to sanitize input.

Especially, he may not:

insert, delete, truncate, drop table, drop database, create table, create function/procedure, see/execute/create/drop stored procedure / functions. etc.

Sooo - now my question:
Why does this user have access to information_schema ?
It wasn't granted access to any views, and not to the schema information_schema either...

Upvotes: 3

Views: 7911

Answers (1)

user330315
user330315

Reputation:

Why does this user have access to information_schema

I think select privilege is granted to PUBLIC by default on information_schema.

You should be able to do:

revoke all on all tables in schema information_schema from public;

You probably also need to revoke the select privilege on views/tables in the pg_catalog schema. But I'm not sure what this will break if e.g. psql or other client tools cannot access that information.

Upvotes: 2

Related Questions