Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, 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?

share|improve this question
    
Just a hint: I kept on getting "database doesn't exist". It turned out the database in question was named in uppercase (PDOK), so in that case you need to put the name in quotes grant all on database "PDOK" to user –  JPvdV May 1 at 19:50

3 Answers 3

up vote 22 down vote accepted

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).

share|improve this answer
1  
Many thanks! I knew it had to be that easy. –  PlaidFan Feb 6 '13 at 20:30
    
@PlaidFan Thanks for the edit! –  dezso Feb 6 '13 at 22:46
3  
I wanted to update this answer with granting access to SEQUENCES. In MySQL this isn't necessary but in Postgres it is. After the GRANT above you need to also issue the following: GRANT USAGE ON ALL SEQUENCES IN SCHEMA <schema_name> TO <user>; This related SO post has more info on granting access to SEQUENCES. –  PlaidFan Feb 18 '13 at 18:51
    
Worth bearing in mind that grant ... on all tables grants access to all currently existing tables - it's more of a shorthand for looping through them all than a special privilege. –  Jack Douglas Jan 14 at 7:56

It may be that i was doing something wrong here since I am very new to postgres. 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;
share|improve this answer

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.

share|improve this answer
6  
This will add the following privileges on the database: CREATE, CONNECT, TEMPORARY. No privileges on tables. –  dezso Feb 1 '13 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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