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?
To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name.
Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions.
These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables and views and EXECUTE on procedures, functions, and packages.
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 andTEMP
table creation privilege for databases;EXECUTE
privilege for functions; andUSAGE
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:
Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With