You can create a read-only Postgres user (I called mine "bind9_dlz_readonly") by executing the following SQL:
Code:
--
-- Create the bind9_dlz_readonly user,
-- without privileges to create databases,
-- without privileges to create users,
-- and with an encrypted password.
--
CREATE USER bind9_dlz_readonly NOCREATEDB NOCREATEUSER WITH ENCRYPTED PASSWORD '<password>';
--
-- Revoke all permissions on the bind9_dlz database (that's
-- what I called mine) from the user we just created.
--
-- Note that you have to do this on every database you don't
-- want the bind9_dlz_readonly user to be able to connect to.
--
REVOKE ALL ON DATABASE bind9_dlz FROM bind9_dlz_readonly;
--
-- Grant connection privileges to the bind9_dlz_readonly user.
--
GRANT CONNECT TO DATABASE bind9_dlz TO bind9_dlz_readonly;
--
-- Grant selection (read) privileges to the bind9_dlz_readonly
-- user on the bind9_dlz and dns_xfr tables.
--
GRANT SELECT ON dns_record, dns_xfr TO bind9_dlz_readonly;
If you are using psql, you can check the privileges on all databases by using the
\list
command.
It returns a table that looks like
Code:
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-------------------------------
bind9_dlz | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | bind9_dlz_readonly=c/postgres
. . . . .
. . . . .
. . . . .
Where the access privileges is of the format
<(username>=<permissions/<user who granted permissions>
And access privileges are indicated by:
- r -- SELECT
- w -- UPDATE
- a -- INSERT
- d -- DELETE
- D -- TRUNCATE
- x -- REFERENCES
- t -- TRIGGER
- X -- EXECUTE
- U -- USAGE
- C -- CREATE
- c -- CONNECT
- T -- TEMPORARY
- arwdDxt -- all table privileges (differs for other types of objects)
So the lines in the table above would be read as:
- Any user (PUBLIC) has been granted TEMPORARY (T) and CONNECT (c) privileges by the postgres user.
- The postgres user has been granted CREATE DATABASE (C), TEMPORARY (T), and CONNECT (c) privileges by the postgres user.
- The bind9_dlz_readonly user has been granted CONNECT (c) privileges by the postgres user.
The postgres user granting privileges to itself makes sense because it is the owner of the database.
To list the table access privileges, you can use the
\dp
command.
It returns a table that looks like
Code:
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------------+----------+-------------------------------+--------------------------
public | dns_record | table | postgres=arwdDxt/postgres +|
| | | bind9_dlz_readonly=r/postgres |
public | dns_record_id_seq | sequence | |
public | dns_xfr | table | postgres=arwdDxt/postgres +|
| | | bind9_dlz_readonly=r/postgres |
So the lines in the above table would be read as:
- The postgres user has been granted all table privileges (INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCE, and TRIGGER) by the postgres user on the dns_record table.
- The bind9_dlz_readonly user has been granted SELECT privileges by the postgres user on the dns_record table.
- The postgres user has been granted all table privileges (INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCE, and TRIGGER) by the postgres user on the dns_xfr table.
- The bind9_dlz_readonly user has been granted SELECT privileges by the postgres user on the dns_xfr table.
References:
http://stackoverflow.com/questions/5...ct-permissions
Adv Reply
Bookmarks