26

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data.

I would like to grant rights to all tables from a given database to a specified user, but I am not sure if it is the best idea to grant him access to whole schema public as I don't know if it would allow some kind of privilege escalation. Is there any other way?

Improve this question
2
  • Why can't you just GRANT SELECT ON TableName TO [Domain\User]? I usually just assign users to the db_datareader role if all they need is read access to all the tables but I'm not sure how granular you want to be.
    – Kris Gruttemeyer
    Commented Feb 14, 2015 at 12:41
  • 1
    I'd like to grant all CRUD to all tables in the database. Also, DROPping and CREATEing inside of this database could also prove useful.
    – d33tah
    Commented Feb 14, 2015 at 12:43

1 Answer 1

Reset to default
36

Postgres 14

... adds the predefined role pg_read_all_data to make this simple:

GRANT pg_read_all_data TO myuser;

The manual:

pg_read_all_data

Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.

For older versions

The privilege on DATABASE only grants general connection rights to the database and no more. A user with just that privilege can only see what the general public is allowed to see.

To grant read access to all tables, you also need privileges on all schemas and tables:

GRANT USAGE ON SCHEMA public TO myuser; -- more schemas?
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

You may also want to set default privileges for future schemas and tables. Run for every role that creates objects in your db

ALTER DEFAULT PRIVILEGES FOR ROLE mycreating_user IN SCHEMA public
GRANT SELECT ON TABLES TO myuser;

But you really need to understand the whole concept first.
And it's almost always better to bundle privileges in group roles and then grant/revoke the group role to/from user roles. Related:

Improve this answer
4
  • Is myusr a typo? Shouldn't it be myuser?
    – attomos
    Commented Feb 14, 2018 at 3:36
  • 1
    @attomos: It's supposed to be a different user in the example. I clarified.
    – Erwin Brandstetter
    Commented Feb 14, 2018 at 12:02
  • 1
    You must connect to the desired database for this to work, either issuing \c some_db first or by specifying the database in the command line. I was doing su - postgres psq and the commands were not working for me because I was on the wrong database
    – geckos
    Commented Feb 20, 2023 at 13:41
  • 1
    I wish there was a GRANT ... ON ALL TABLES IN DATABASE foo ... but it seems that it does not
    – geckos
    Commented Feb 20, 2023 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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