2014/04/11

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 ~]#

1 comment:

Unknown said...



Nice Article !

Really this will help to people of PostgreSQL Community.
I have also prepared small note on this, How to create read only user in PostgreSQL.

http://www.dbrnd.com/2015/10/postgresql-script-to-create-a-read-only-database-user/