208

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database. I must be missing something in Postgres because it looks like I have to grant those privileges for each table one at a time. With many databases and hundreds of tables per database that seems like a daunting task just to get off the ground. In addition, once a database is in operation, adding tables happens frequently enough that I wouldn't want to have to grant permissions each time unless absolutely necessary.

How is this best accomplished?

0

5 Answers 5

227

First, you have to be able to connect to the database in order to run queries. This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

The REVOKE is necessary because

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

As Jack Douglas pointed out, the above only gives the privileges for the already existing tables. To achieve the same for future tables, you have to define default privileges:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Here, some_role is a role that creates the tables, while user_name is the one who gets the privileges. Defining this, you have to be logged in as some_role or a member of it.

And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) - here it is the USAGE privilege that you need.

5
  • 1
    Thanks, FOR some_role was the key part I was missing to make it work for my tables created later. But I didn't have to be logged in as some_role, it worked also if I executed the query as the default admin postgres user. Jul 28, 2015 at 14:38
  • I did all those commands for my user, but still I wasn't allowed to use SELECT on the tables inside the database. It only worked after i REVOKED all privileges again and GRANTed again.
    – rubo77
    Jan 2, 2020 at 2:24
  • note updating (refreshing) materialized view requires extra permission if user is not the owner of the database. dba.stackexchange.com/questions/171932/…
    – KuN
    Feb 19, 2021 at 16:47
  • does REVOKE CONNECT FROM PUBLIC removes CONNECT priviledge from the superuser? Aug 9, 2021 at 17:13
  • 1
    @MerajalMaksud it doesn't Aug 25, 2021 at 12:01
66

assuming you want to give them all privileges - do this:

grant all privileges on database dbname to dbuser;

where dbname is the name of your database and dbuser is the name of the user.

4
  • 66
    This will add the following privileges on the database: CREATE, CONNECT, TEMPORARY. No privileges on tables. Feb 1, 2013 at 12:45
  • 21
    A similar command for all tables would be, GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser; Mar 28, 2017 at 3:04
  • 10
    I found this useful too: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;
    – Paolo
    Sep 8, 2017 at 18:18
  • mysql bg? READ THE COMMENTS!
    – WEBjuju
    Mar 17, 2021 at 18:41
44

Granting all privileges to all tables within the database is achieved with

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;
2
  • What if there are multiple schemas?
    – asds_asds
    Jun 22, 2021 at 21:07
  • 1
    @asds_asds you'd have to execute it one by one for all the schemas you need. Jun 22, 2021 at 21:15
24

It may be that I was doing something wrong here since I am very new to PostgreSQL. But this only solved the first part of the problem for me - setting the privileges on all existing tables.

In order for permissions to be correctly set for my user on new tables, that are created I has to set default permissions for the user:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON sequences TO user_name;
8
--Create User

CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB  NOCREATEROLE    INHERIT NOREPLICATION   CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';

-- Grant connect to my data base

GRANT CONNECT ON DATABASE my_db_test TO my_user_test;

-- Grant usage the schema

GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;

-- Grant all table for SELECT, INSERT, UPDATE, DELETE

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;
1
  • What if I need to allow usage of multiple schemas, which have multiple tables
    – asds_asds
    Jun 22, 2021 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.