(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');# Now, copy and paste those lines, then exit:
?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)
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:
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/
Post a Comment