Grant Privileges for a Particular Database in Postgresql

PostgreSQL: Give all permissions to a user on a PostgreSQL database

All commands must be executed while connected to the right database cluster. Make sure of it.

Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc.

A role needs access to the database, obviously. That's granted to PUBLIC by default. Else:

GRANT CONNECT ON DATABASE my_db TO my_user;

Basic privileges for Postgres 14 or later

Postgres 14 adds the predefined, non-login roles pg_read_all_data / pg_write_all_data.

They have SELECT / INSERT, UPDATE, DELETE privileges for all tables, views, and sequences. Plus USAGE on schemas. We can GRANT membership in these roles:

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

This covers all basic DML commands (but not DDL, and not some special commands like TRUNCATE or the EXECUTE privilege for functions!). The manual:

pg_read_all_data
Read all data (tables, views, sequences), as if having SELECT rights
on those objects, and USAGE rights on all schemas, even without
having it explicitly. This role does not have the role attribute
BYPASSRLS set. If RLS is being used, an administrator may wish to
set BYPASSRLS on roles which this role is GRANTed to.
pg_write_all_data
Write all data (tables, views, sequences), as if having INSERT,
UPDATE, and DELETE rights on those objects, and USAGE rights on
all schemas, even without having it explicitly. This role does not
have the role attribute BYPASSRLS set. If RLS is being used, an
administrator may wish to set BYPASSRLS on roles which this role is
GRANTed to.

All privileges without using predefined roles (any Postgres version)

Commands must be executed while connected to the right database. Make sure of it.

The role needs (at least) the USAGE privilege on the schema. Again, if that's granted to PUBLIC, you are covered. Else:

GRANT USAGE ON SCHEMA public TO my_user;

Or grant USAGE on all custom schemas:

DO
$$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE to see generated commands
   EXECUTE (
   SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
   FROM   pg_namespace
   WHERE  nspname <> 'information_schema' -- exclude information schema and ...
   AND    nspname NOT LIKE 'pg\_%'        -- ... system schemas
   );
END
$$;

Then, all permissions for all tables (requires Postgres 9.0 or later).

And don't forget sequences (if any):

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

Alternatively, you could use the "Grant Wizard" of pgAdmin 4 to work with a GUI.

This covers privileges for existing objects. To also cover future objects, set DEFAULT PRIVILEGES. See:

Grant privileges for a particular database in PostgreSQL
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

There are some other objects, the manual for GRANT has the complete list. As of Postgres 14:

privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)

But the rest is rarely needed. More details:

Grant privileges for a particular database in PostgreSQL
How to grant all privileges on views to arbitrary user

Consider upgrading to a current version.

Grant privileges for a particular database in PostgreSQL

Basic concept in Postgres

Roles are global objects that can access all databases in a db cluster - given the required privileges.

A cluster holds many databases, which hold many schemas. Schemas (even with the same name) in different DBs are unrelated. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting).

Every database starts with a schema public by default. That's a convention, and many settings start with it. Other than that, the schema public is just a schema like any other.

Coming from MySQL, you may want to start with a single schema public, effectively ignoring the schema layer completely. I am using dozens of schema per database regularly.

Schemas are a bit (but not completely) like directories in the file system.

Once you make use of multiple schemas, be sure to understand search_path setting:

How does the search_path influence identifier resolution and the "current schema"

Default privileges

Per documentation on GRANT:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default
privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE
for databases; EXECUTE privilege for functions; and USAGE privilege for languages.

All of these defaults can be changed with ALTER DEFAULT PRIVILEGES:

Grant all on a specific schema in the db to a group role in PostgreSQL

Group role

Like @Craig commented, it's best to GRANT privileges to a group role and then make a specific user member of that role (GRANT the group role to the user role). This way it is simpler to deal out and revoke bundles of privileges needed for certain tasks.

A group role is just another role without login. Add a login to transform it into a user role. More:

Why did PostgreSQL merge users and groups into roles?

Predefined roles

Update: Postgres 14 or later adds the new predefined roles (formally "default roles") pg_read_all_data and pg_write_all_data to simplify some of the below. See:

Grant access to all tables of a database

Recipe

Say, we have a new database mydb, a group mygrp, and a user myusr ...

While connected to the database in question as superuser (postgres for instance):

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

To assign "a user all privileges to all tables" like you wrote (I might be more restrictive):

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those

To set default privileges for future objects, run for every role that creates objects in this schema:

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

Now, grant the group to the user:

GRANT mygrp TO myusr;

Alternative (non-standard) setting

Coming from MySQL, and since you want to keep privileges on databases separated, you might like this non-standard setting db_user_namespace. Per documentation:

This parameter enables per-database user names. It is off by default.

Read the manual carefully. I don't use this setting. It does not void the above.

grant all privileges on db.* to user?

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.

Postgresql CREATE GRANT - Limit Access to other Databases on Server?

You'd have to forbid connections to the other database to the user.

There are two possibilities:

Via database permissions:

-- by default, everybody can connect
REVOKE CONNECT ON DATABASE other FROM PUBLIC;
-- now you have to grant access to the legitimate users specifically
GRANT CONNECT ON DATABASE other TO other_users;

Via pg_hba.conf.

Don't add an entry to the file that allows abc to connect to other.

So rather than having

host all all 0.0.0.0/0 scram-sha-256

have entries like

host other +other_users 0.0.0.0/0 scram-sha-256
host abc   abc          0.0.0.0/0 scram-sha-256

How do I grant certain privileges on a database to a user?

Tables are not created in "a database" - only in a schema.

If you want that user to create tables only in a specific schema then you need to grant usage on that schema:

grant usage, create on schema public to someuser;

You don't need to grant privileges for not yet created tables of that user. The user that creates a table is the owner of that table. The owner of a table can do anything with it. There is no need to grant the owner additional privileges.

If you want to allow the user to create new schemas, you need to grant the create privilege on the database:

grant create on database somedb to someuser;

If you want to allow the user to select any existing table in a schema, you need to explicitly grant that:

grant select,insert,update,delete on all tables in schema public to someuser;

If you want that user to also be able to do that for new tables that are not created by that user, you need to change the default privileges:

alter default privileges
   in schema public 
   grant select,insert,update,delete on tables
   to someuser;

Grant privileges on tables in other database

No, as the error message says, cross-database references are not implemented.

This is a security feature: There is no way to affect another database than the one you are connected to with an SQL statement (unless you are using something like dblink or foreign data wrappers).

How to grant all privileges in one line to a created user on PostgreSQL

The syntax is slighly off. It is CREATEDB and CREATEROLE. Besides, if you are superuser, you have everything else automatically.

So this is enough:

ALTER ROLE faouzi SUPERUSER;