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;
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014