How do you grant read/select access on all functions and views to an arbitrary user?
psql --user=postgres -d mydb -f myview.sql
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
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.
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;
TEMPtable creation privilege for databases;
EXECUTEprivilege for functions; and
USAGEprivilege 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
More detailed answer how to manage privileges:
pgAdmin has a feature for more sophisticated bulk operations:
Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...