How to grant all privileges on views to arbitrary user
database, pgadmin, postgresql, sql, sql-grant
Solution
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`:
- Grant all on a specific schema in the db to a group role in PostgreSQL
More detailed answer how to manage privileges:
- How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
pgAdmin has a feature for more sophisticated bulk operations:
Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...
Problem
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?