Search code examples
postgresqlprivilegessql-grant

grant all privileges on db.* to user?

(select version() PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit)

I created a new user:

create user bob with password 'some_password_string';

I am in a db called 'GoogleData':

grant all privileges on GoogleData to bob;

SQL Error [42P01]: ERROR: relation "googledata" does not exist

I am able to add bob to specific tables in GoogleData by specifying schema.table e.g.

grant all privileges on reporting.ecom_dashboard to bob;

That works, except there are too many tables to grant access to one by one. I am also unable to grant access to public.

Tried:

grant all privileges on public to bob;
SQL Error [42P01]: ERROR: relation "public" does not exist

Tried iterating over specific schemas like so:

grant all privileges on reporting.* to bob;
SQL Error [42601]: ERROR: syntax error at or near "to"
  Position: 46
  1. How can I grant all privileges to Bob at the database level? When I tried I got error 'ERROR: relation "googledata" does not exist' (Where googledata is the db in question)
  2. GIven I cannot seem to grant access at the db level, how can I grant bob privileges at the schema level grant all privileges on schema_name.* to bob;?
Solution

  • how can I grant bob privileges at the schema level

    You your looking for the option on all tables in schema

    grant all privileges on all tables in schema public to bob;
grant all privileges on all tables in schema reporting to bob;

    You probably also want to change the default privileges so that this is also applied for tables created in the future.