Grant Permissions to All Schema Objects to a User in PostgreSQL

I admit that in the past I have had some real frustrations granting permission users in PostgreSQL databases. I believe that much of this stemmed from the fact that up until Version 9, there was no way to manipulate the permissions on more than one object at a time, you simply had to grant permissions to each object. Then when another was added more permissions had to follow, it was never ending. Now with version 9, things have gotten simpler. When I have a task such as creating a user with specific access to a schema, I would follow these tasks:

1. Create the PostgreSQL User or Role

prospector=# create user mary password 'marypassword';
CREATE ROLE

2. Grant Usage on the PostgreSQL Schema in Question

uptime=# \c uptime;
You are now connected to database "uptime" as user "postgres".
uptime=# grant usage on schema public to mary;
GRANT

3. Grant permissions on the tables

In this database, we have 2 tables that are in the public schema:

uptime=# \d
List of relations
Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
public | customer | table | postgres
public | vendor   | table | postgres
(2 rows)

We will grant select on all of the tables in the schema without having to list them individually:

uptime=# grant select on all tables in schema public to mary;
GRANT

4. Verify That the User’s Permissions Have Been Granted


uptime=# \z
Access privileges
Schema |   Name   | Type  |     Access privileges     | Column access privileges
--------+----------+-------+---------------------------+--------------------------
public | customer | table | postgres=arwdDxt/postgres+|
|          |       | mary=r/postgres           |
public | vendor   | table | postgres=arwdDxt/postgres+|
|          |       | mary=r/postgres           |
(2 rows)

Notice that the Access Privileges listed above follow this rubric:

              =xxxx -- privileges granted to PUBLIC
         uname=xxxx -- privileges granted to a user
   group gname=xxxx -- privileges granted to a group

                  r -- SELECT ("read")
                  w -- UPDATE ("write")
                  a -- INSERT ("append")
                  d -- DELETE
                  x -- REFERENCES
                  t -- TRIGGER
                  X -- EXECUTE
                  U -- USAGE
                  C -- CREATE
                  c -- CONNECT
                  T -- TEMPORARY
             arwdxt -- ALL PRIVILEGES (for tables)
                  * -- grant option for preceding privilege

              /yyyy -- user who granted this privilege

5. Edit the pg_hba.conf File and Add a Line for Your User to be Able to Connect

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 host    all             all             127.0.0.1/32            ident
 host    all             all             127.0.0.1/32            md5
 host    all             nagios          all                     md5
 host    all             mary      all                    md5
 host    all             sue      all                     md5

6. Restart the Database or if You Can’t Restart the Database Now, Reload it:


# service postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

7. Connect and use permissions:

# /usr/pgsql-9.2/bin/psql --username=mary --password --host=prizm.1on1.com uptime
Password for user mary:
psql (9.2.3)
Type "help" for help.

uptime=> select * from customer;
customerid |        name
------------+--------------------
1 | uptimemadeeasy.com
(1 row)

uptime=>

Create MySQL Superuser

You may be needing to grant superuser MySQL rights to one of your users. Create the user as usual and then alter the user with SUPERUSER as shown below:

mysql> ALTER USER myuser WITH SUPERUSER;

Bio
Latest Posts

Jeff Staten

Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on Google+ or LinkedIn at: LinkedIn or Twitter at: Twitter