Installing and setting up a Postgres 9.3 Database on Ubuntu 14.04.
After it's the DB server is set up, create a DB with a read only and a read write user.
I want to make sure I will install 9.3 if I use
apt-get. To check that run the following
command
> apt-cache
policy postgresql
|
Yep 9.3
OK, now install it.
> sudo apt-get
update
> sudo apt-get
install postgresql
|
Switch to the Postgres user (the only user who can login at this point)
> sudo su
postgres
|
Login to the database
> psql -d
postgres -U postgres
|
Create Users
I am going to create two users. The first user is myappr and the second
myapprw. myappr can only read, myapprw
can read or write to the tables.
> CREATE USER
app_ro WITH PASSWORD 'myPassword';
> CREATE USER
app_rw WITH PASSWORD 'myPasswordrw';
|
If you need to update a password run the following command.
> ALTER USER app_rw WITH PASSWORD
'myNewPassword';
|
Create new Database
> CREATE DATABASE
myapp;
|
Immediately run this to revoke access to the database (If
you don't anyone can create new tables)
>
\c myapp
> REVOKE ALL ON
DATABASE myapp
FROM PUBLIC;
> REVOKE ALL ON
SCHEMA public FROM PUBLIC;
|
Grant Privileges Per User
Let me see who has what
privilege to begin with.
> \du
|
app_or and app_rw have
no privileges at all.
Run the following
commands to give the two users connect access to the database myappdb.
> GRANT
CONNECT ON DATABASE myapp to app_ro;
> GRANT
CONNECT ON DATABASE myapp to app_rw;
|
Then check their roles again
> \du
|
OK, that didn't change?
Maybe I am looking at roles for the Postgres server.
Poking around I found
this http://dba.stackexchange.com/questions/4286/list-the-database-privileges-using-psql [1]
This command kind of
gets the info I want.
> \l
|
Look like I need to be connected to the database to give
grant specific permissions to user on that database.
r -- SELECT ("read")
w --
UPDATE ("write")
a --
INSERT ("append")
d --
DELETE
D --
TRUNCATE
x --
REFERENCES
t --
TRIGGER
X --
EXECUTE
U -- USAGE
C -- CREATE
c --
CONNECT
T --
TEMPORARY
Switch to the myappdb database.
> \c myapp
|
Give the app_ro select access and app_rw select, update,
insert, and delete.
> GRANT SELECT
ON ALL TABLES IN SCHEMA public TO app_ro;
> GRANT
SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
|
Running \l again does
not seem to show these added privelges, just if the role can connect to the DB.
> \l
|
Nothing different
Let me create a table so I can see the privileges
> CREATE TABLE
test (id serial, name varchar(40));
|
Now run \z
> \z
|
OK, what did I do wrong?
Poking around… oh http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql
[2] .
The command I ran to grant select privileges on all tables
only effects existing tables.
If I run it again…
(Run these after
connecting to the database >\c maypp)
> GRANT SELECT
ON ALL TABLES IN SCHEMA public TO app_ro;
> GRANT SELECT
ON ALL SEQUENCES IN SCHEMA public TO app_ro;
> GRANT USAGE
ON SCHEMA public to app_ro;
> GRANT
SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
> GRANT SELECT,
UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_rw;
> GRANT USAGE
ON SCHEMA public to app_rw;
|
And check
> \z
|
Thinking …
I want to update the privilege on all existing tables in the
database and make sure that when a new table is created I get the same
permissions assigned to it.
Setting the default privileges when new tables is made
To set the app_ro user (read only) run the following
commands
> ALTER DEFAULT
PRIVILEGES IN SCHEMA public
GRANT SELECT ON
TABLES TO app_ro;
> ALTER DEFAULT
PRIVILEGES IN SCHEMA public
GRANT SELECT ON
SEQUENCES TO app_ro;
|
Then for app_rw (read and write) run the following commands.
> ALTER DEFAULT
PRIVILEGES IN SCHEMA public
GRANT SELECT,
UPDATE, INSERT, DELETE ON TABLES TO app_rw;
> ALTER DEFAULT
PRIVILEGES IN SCHEMA public
GRANT SELECT,
UPDATE ON SEQUENCES TO app_rw;
|
Create a new table, as the postgres user, and see if it
worked.
> CREATE TABLE
new_test (id serial, name varchar(40));
|
Check
> \z
|
Wahoo that worked.
I set these defaults up as the postgres user. If I create a table as another user these defaults
will not be used. You would need to
login to Postgres and set defaults for that user. (Or at least that is my understanding at this
point).
Insert a few bits of
data into the test table.
> insert into
test (name) VALUES ('TEST_ME');
> insert into
test (name) VALUES ('TEST_ME_TOO');
|
Login
Login as app_ro and see if you have the correct permissions.
> psql -h
localhost -U app_ro myapp
|
See if you can select.
> select *
from test;
|
Test if you can insert
> insert into
test (name) VALUES ('insert should not work');
|
Denied, as expected.
See if I can create a table (I should not be able to)
> CREATE TABLE
cannot_create (id serial, name varchar(40));
|
Permission denied, as expected.
Login as app_rw and see if you have the correct permissions.
> psql -h
localhost -U app_rw myapp
|
See if you can select.
> select *
from test;
|
Test if you can insert (you should be able to)
> insert into
test (name) VALUES ('insert should work');
|
Inserted, as expected.
See if I can delete a row.
> delete FROM
test where id = 8;
|
It was able to delete, as expected.
See if I can create a table (I should not be able to)
> CREATE TABLE
cannot_create (id serial, name varchar(40));
|
Permission denied, as expected.
Login from another machine
My test machine is on 192.168.0.24. I want to make sure I can log into the
database from another server.
If I log into another server and run the following command.
> psql -h
192.168.0.24 -U app_ro myapp
|
I get this error, because I have not set up the Postgres
server to accept incoming connection from other servers.
To fix that do the following.
Get the IP addresses of the machine(s) you want to allow to
connect to the database. Usually this is
an IP range. For example I have
192.168.0.x at home. If I want to allow
all these to have access I can use an IP calculator like the one at http://jodies.de/ipcalc?host=192.168.0.0&mask1=24&mask2=
to figure this out.
If I put in 192.168.0.0/24
I can see that the HostMin is 192.168.0.1 and the max is
192.168.0.254.
From the Postgres machine edit /etc/postgresql/9.3/main/pg_hba.conf
> sudo su postgres
> vi
/etc/postgresql/9.3/main/pg_hba.conf
|
Append the following line to the end of this document
host all all 192.168.0.0/24 md5
|
Edit /etc/postgresql/9.3/main/postgresql.conf
> vi /etc/postgresql/9.3/main/postgresql.conf
|
Update listen_addresses to the following
listen_addresses = '*'
|
Restart the Postgres server
> sudo service
postgresql stop
> sudo service
postgresql start
|
Try to login again from another box.
> psql -h
192.168.0.24 -U app_ro myapp
|
No passwords?
There is a way to connect without using passwords.
If you edit /etc/postgresql/9.3/main/pg_hba.conf
> sudo su
postgres
> vi
/etc/postgresql/9.3/main/pg_hba.conf
|
Append the following line to the end of this document
host all all 192.168.0.0/24 trust
|
Restart the Postgres server
> sudo service
postgresql stop
> sudo service
postgresql start
|
Try to login from another machine.
> psql -h
192.168.0.24 -U app_ro myapp
|
But you can login as any
user. You can login as Postgres
> psql -h
192.168.0.24 -U postgres myapp
|
Use "trust" with caution.
Allowing a single ip location
Let's say I only want to allow connections from IP address
192.168.0.160
Edit /etc/postgresql/9.3/main/pg_hba.conf
> sudo su
postgres
> vi
/etc/postgresql/9.3/main/pg_hba.conf
|
Append the following line to the end of this document
host all all 192.168.0.160/32
md5
|
Restart the Postgres server
> sudo service
postgresql stop
> sudo service
postgresql start
|
Try to connect now from 192.168.0.160
> psql -h
192.168.0.24 -U app_ro myapp
|
If I try to run if from another machine.
> psql -h
192.168.0.24 -U app_ro myapp
|
Summing it all up
Let's say I want to
·
install
Postgres 9.3 on a box
·
create
two users
o
app_ro (read only)
o
app_rw (read and write)
·
create
a DB called myapp
·
create
tables for myapp
·
Database
can be accessed from 192.168.0.160 (and nowhere else)
Install Postgres 9.3 (from
Ubuntu 14.04)
> sudo apt-get
update
> sudo apt-get
install postgresql
|
Switch to the Postgres user (the only user who can login at this point)
> sudo su
postgres
|
Create users, DB, and tables
Create a script file to
run.
> cd
>
|
Place the following in
it
-- Add Users
CREATE USER app_ro WITH PASSWORD 'myPassword';
CREATE USER app_rw WITH PASSWORD 'myPassword';
-- Create DB
CREATE DATABASE myapp;
-- login to the new DB
\c
myapp
--
Revoke all Privileges
REVOKE
ALL ON DATABASE myapp FROM PUBLIC;
REVOKE
ALL ON SCHEMA public FROM PUBLIC;
-- Set up privileges for app_ro
GRANT
CONNECT ON DATABASE myapp to app_ro;
GRANT
SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;
GRANT
USAGE ON SCHEMA public to app_ro;
-- Set up privileges for app_rw
GRANT CONNECT ON DATABASE myapp to app_rw;
GRANT
SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT
USAGE ON SCHEMA public to app_rw;
GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;
GRANT
USAGE ON SCHEMA public to app_ro;
-- Set up privileges for app_ro (for new tables)
ALTER
DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_ro;
ALTER
DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO app_ro;
-- Set up privileges for app_rw (for new tables)
ALTER
DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE, INSERT, DELETE ON
TABLES TO app_rw;
ALTER
DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE ON SEQUENCES TO
app_rw;
--Create
tables
CREATE
TABLE test (id serial, name varchar(40));
CREATE
TABLE test_2 (id serial, name varchar(40));
--Insert
some test data
INSERT
INTO test (name) VALUES ('test');
INSERT INTO test (name) VALUES ('test_two');
|
Run the following command to run the script.
> psql -U
postgres -d postgres -a -f script.sql
|
Allow 192.168.0.160 to connect to the database
Edit /etc/postgresql/9.3/main/pg_hba.conf
> sudo su
postgres
> vi
/etc/postgresql/9.3/main/pg_hba.conf
|
Append the following line to the end of this document
host all all 192.168.0.160/32
md5
|
Edit /etc/postgresql/9.3/main/postgresql.conf
> vi /etc/postgresql/9.3/main/postgresql.conf
|
Update listen_addresses to the following
listen_addresses = '*'
|
Restart the Postgres server
> sudo service
postgresql stop
> sudo service
postgresql start
|
Test
From 192.168.0.160 login.
> psql -h
192.168.0.24 -U app_ro myapp
|
It's all working :)
Here is the video I posted on youtube going through this how to.
References
[1] List the database
privileges using psql
Visited 4/2012
[2] How do you create
a read-only user in PostgreSQL
Visited 4/2012
[3] IP address
calculator
Visited 4/2012
No comments:
Post a Comment