Create Postgres read-only user (pre 9.x)

Here’s how I created the user; due to limitations in postgres 8.1, this user will not have access to any new tables (such as may be created during an upgrade of the application); the process is much easier than this for postgres 9.1+.


(Credit to this helpful user http://stackoverflow.com/a/762649 ; I just want to show it in action and spread the love. )

First, connect to postgres, then list databases, then connect to the correct one, then create the DB user.

[root@mycomputer ~]# su - postgres
-bash-3.2$ rpm -qa | grep postgres
postgresql-libs-8.1.23-10.el5_10
postgresql-8.1.23-10.el5_10
postgresql-server-8.1.23-10.el5_10
-bash-3.2$ psql
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \list
         List of databases
    Name    |   Owner    | Encoding
------------+------------+----------
testdb | testdb | UTF8
postgres   | postgres   | UTF8
template0  | postgres   | UTF8
template1  | postgres   | UTF8
(4 rows)

postgres=# \c testdb
You are now connected to database "testdb".
testdb=# CREATE USER testdbread WITH password 'supersecretpassword';
testdb=# GRANT USAGE ON SCHEMA public TO testdbread;
GRANT

# This command creates a list of GRANT commands that you can copy and paste to grant access to existing tables:

testdb=# select 'GRANT SELECT ON ' || relname || ' TO testdbread;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');
                         ?column?
----------------------------------------------------------
GRANT SELECT ON usermigration TO testdbread;
GRANT SELECT ON plugindata TO testdbread;
GRANT SELECT ON os_propertyentry TO testdbread;
GRANT SELECT ON os_user TO testdbread;
GRANT SELECT ON attachments TO testdbread;
(5 rows)

# Now, copy and paste those lines, then exit:

testdb=# GRANT SELECT ON usermigration TO testdbread;
GRANT

testdb=# GRANT SELECT ON plugindata TO testdbread;
GRANT
testdb=# GRANT SELECT ON os_propertyentry TO testdbread;
GRANT
testdb=# GRANT SELECT ON os_user TO testdbread;
GRANT

testdb=# GRANT SELECT ON attachments TO testdbread;
GRANT
testdb=# exit
testdb-# \q
-bash-3.2$ logout
[root@mycomputer ~]#

PostgreSQL Tips and Tricks


# Log in to a postgreSQL database
psql -U username dbname

# Get list of databases.
SELECT datname FROM pg_database;
# or
\l
# or
psql -l

# Get size of tables, sorted in descending order.
SELECT relname, relpages,reltuples FROM pg_class ORDER BY relpages DESC;

# Show tables in a database.
select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE';
# or
\dt

# Show structure of a table.
\d tablename

psql Commands

General

\c[onnect] [DBNAME|- [USER]]	connect to new database (currently "dfdata")
\cd [DIR]	change the current working directory
\copyright	show PostgreSQL usage and distribution terms
\encoding [ENCODING]	show or set client encoding
\h [NAME]	help on syntax of SQL commands, * for all commands
\q	quit psql
\set [NAME [VALUE]]	set internal variable, or list all if no parameters
\timing	toggle timing of commands (currently off)
\unset NAME	unset (delete) internal variable
\! [COMMAND]	execute command in shell or start interactive shell

Query Buffer

\e [FILE]	edit the query buffer (or file) with external editor
\g [FILE]	send query buffer to server (and results to file or pipe)
\p	show the contents of the query buffer
\r	reset (clear) the query buffer
\s [FILE]	display history or save it to file
\w FILE	write query buffer to file

Input/Output

\echo [STRING]	write string to standard output
\i FILE	execute commands from file
\o [FILE]	send all query results to file or |pipe
\qecho [STRING]	write string to query output stream (see \o)

Informational||

\d [NAME]	describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)	list tables/indexes/sequences/views/system tables
\da [PATTERN]	list aggregate functions
\db [PATTERN]	list tablespaces (add "+" for more detail)
\dc [PATTERN]	list conversions
\dC	list casts
\dd [PATTERN]	show comment for object
\dD [PATTERN]	list domains
\df [PATTERN]	list functions (add "+" for more detail)
\dg [PATTERN]	list groups
\dn [PATTERN]	list schemas (add "+" for more detail)
\do [NAME]	list operators
\dl	list large objects, same as \lo_list
\dp [PATTERN]	list table, view, and sequence access privileges
\dT [PATTERN]	list data types (add "+" for more detail)
\du [PATTERN]	list users
\l	list all databases (add "+" for more detail)
\z [PATTERN]	list table, view, and sequence access privileges (same as \dp)

Formatting

\a	toggle between unaligned and aligned output mode
\C [STRING]	set table title, or unset if none
\f [STRING]	show or set field separator for unaligned query output
\H	toggle HTML output mode (currently off)
\pset NAME [VALUE]	set table output option (NAME := {format|border|expanded|fieldsep|footer|null| | |numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t	show only rows (currently off)
\T [STRING]	set HTML tag attributes, or unset if none \x toggle expanded output (currently off) Copy, Large Object \copy ... perform SQL COPY with data stream to the client host \lo_export LOBOID FILE \lo_import FILE [COMMENT] \lo_list \lo_unlink LOBOID large object operations