24

How do you grant read/select access on all functions and views to an arbitrary user?

I use psql --user=postgres -d mydb -f myview.sql to create several functions and views, and then I run:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Having been granted all privileges, I would expect myuser to now have access to the functions and views created by the postgres user. However, when I try and access them with myuser, I get a "permission denied for relation..." error. Why is this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Cerin
  • 60,957
  • 96
  • 316
  • 522

1 Answers1

33

The reason is that you need additional privileges to access a view or table. Privileges on the database do not cover access to all objects in it.

It is different with functions: EXECUTE privilege is granted to public by default. But the function is executed with the privileges of the current user. You may be interested in the SECURITY DEFINER modifier for CREATE FUNCTION. But normally it is enough to grant SELECT on involved tables.

Per documentation about default privileges:

Depending on the type of object, the initial default privileges might include granting some privileges to PUBLIC. The default is no public access for tables, columns, schemas, and tablespaces; CONNECT privilege and TEMP table creation privilege for databases; EXECUTE privilege for functions; and USAGE privilege for languages.

You may be interested in this DDL command (requires Postgres 9.0 or later):

GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

While connected to the database in question, of course (see @marcel's comment below), and as a user with sufficient privileges. You may also be interested in the setting DEFAULT PRIVILEGES:

More detailed answer how to manage privileges:

pgAdmin has a feature for more sophisticated bulk operations:

enter image description here

Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 5
    It took me an hour to realize that 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;' only makes sense when this command is executed when the user that is granting is *CONNECTED* to that database. If not, it just doesn't do anything (or at least it doesn't give the select permission to the user). In the shell of the superuser use \c databasename. – marcel Nov 23 '14 at 17:37
  • This doesn't grant the user permission to sequences. – Cerin Nov 01 '16 at 20:50
  • 1
    @Cerin: The question asks for *views*, you don't need privileges on sequences for that (at least not at the time of writing where updatable views were not yet implemented). For more privileges see: http://stackoverflow.com/questions/24918367/grant-privileges-for-a-particular-database-in-postgresql/24923877#24923877 – Erwin Brandstetter Nov 01 '16 at 21:16