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

2014/04/09

Protect and access your passwords anywhere

Web sites are regularly compromised and your data may be compromised.  Server applications have as-yet-undiscovered weaknesses, as do even security appliances and encryption protocols.  The Heartbleed vulnerability with the very widely used OpenSSL was recently made public.  The details on how and even whether it may have been used to compromise data are not as clear to me.  However, we know that the vulnerability can allow sensitive data to be stolen from devices that use it for encryption.

In any case, if you haven't rolled your passwords in a while, this could be a good time to do so.  If you're doing so, it should also be a good time to finally start protecting your online and financial identities in this well-connected world the same way you lock the door on your home and perhaps have an alarm on your car.

Others may/will disagree, but here's what I recommend, as a way to (1) always have strong passwords, (2) reduce risk of a compromise of one account meaning other things get compromised, and (3) ensure that you can always get to your passwords in a pinch, any time, from nearly any device.

  1. Use a local storage password keeper program, one that never uses unencrypted temporary files and has sensible semantics for automatically backing itself up and locking itself.  I like Password Safe, for example. Download the latest here: http://sourceforge.net/projects/passwordsafe/files/passwordsafe/  (supports Windows, Linux, Android, MacOS X, iOS) 
  2. Use a free backup program and service from a reputable (publicly traded, with a lot to lose if they abuse your data,) to sync your encrypted password safe and ensure it survives local hard drive failures.  I use DropBox.  Dropbox lets you have a "dropbox" folder on your local computer (Windows, Linux, Android, MacOS X, iOS) that is automatically backed up to a cloud, and makes the files accessible from other computers (e.g., between yours, mom's laptop, and home PC; even via the web, and from Android and iOS phones).  Once you have run password safe and "saved" your password safe to your "Dropbox" folder, it is backed up automatically.
  3. With two exceptions, all of your passwords will be completely random; only your password keeper program and your password safe will have phrases that you can remember, that nobody else could guess, and that are different from each other.
    1. I suggest picking a sentence that only you would know for your password, something from your childhood like: "Oh, how I loved camp chatta-wookie as a child." (If you thought to use that exact phrase, repent and sit in the corner for 5.)
    2. Better yet, let your spouse choose a phrase that you can both remember. Like a sentence about a great memory -- you get the idea.  Then you can share the same password safe.  If something happens to one of you, the other can still log in to your sites to take care of business.
    3. You could use a favorite line from a book, but you need to change it in a way that someone else would not use or guess the exact same phrase; e.g., don't use "For God so loved the world" or "Et tu, Brute?", because those are very common phrases.
  4. Create an account with your online backup/sync provider using the pass phrase advice in the last step.  You will need to be able to recall this if you ever want to access it from a computer other than your regular PC/laptop, or if your local hard drive fails.  This is the one password that you may write down somewhere and keep in your safe. Compromise of this phrase will allow someone to get at your synced files, but they won't be able to get your passwords -- those will be encrypted using a different password.
  5. Start up the password safe program and assign a different passphrase than the one you used for your online backup/sync provider.  If you forget this pass phrase, you're out of luck.  Save your new password safe data file to the location that your backup provider uses. For drop box, that would be a "Dropbox" folder under your home directory or documents folder.
  6. Now, go to each site where you have an account and change the password, recording it in the password safe program and following this method:
    1. For Title, use a name of the service so that you can find it quickly in the list. When your list grows large, you'll want something that is meaningful
    2. For Username, use the exact username as you use it to log in.
    3. For password, click "Generate".  For details on how to configure Password Safe to be naturally stronger, see below.  If you're not going to use strong, unique (if not random) passwords, then nothing else you do will matter too terribly much, and you have left your windows unlocked and inviting.
    4. Copy and paste the generated password into your web site in the change password / new password field.
    5. URL: specify the web site URL, if it's a web site. This will help you to remember how to get to the exact site, or to search your list of passwords more easily.
    6. Use "Notes" to record stuff you'd need to remember. You might record the date you opened the account, or answers to security questions (see below on security question).
    7. Click "OK" to apply.  I like to go into password safe and configure it to automatically save every time I change a password. I also like to configure it to *not* use the system tray, so it actually exits and closes when I click the close button.
  7. Thoughts on passwords
    1. Always let let your password safe program randomly generate passwords for every site where you register.   Perhaps the only exceptions would be your main email account, your password safe "combination", and your data sync/backup site.  Set those to other sentences that you will always remember. 
    2. Never, ever use the same password twice.
    3. Using random and unique passwords ensures that if a person gets a password or access to one site, they'll have a harder time getting access to another site. Consider that some IT staff and related functions may be able to actually see your passwords that you used to register for sites at companies you deal with; if you use the same passwords, an unscrupulous IT staffer could then simply guess what sites you might use in order to gain access to other accounts and steal your data or impersonate you.
    4. Ensure that the passwords are long and complex (10+ characters, including alpha, num, and punctuation where the site permits it).  In Password Safe, you configure the "password policy" using the Manage menu --> Options --> Password Policy.
    5. Never write down your passwords elsewhere for any site.  Even silly sites.  Compromising silly sites is one way for someone to socially engineer their way into assuming parts of your identity or learning more about you to compromise the rest.  An exception would be to put the password in your strong home safe (not in a locking drawer that can easily be broken into).
    6. Never access services like these from a public or shared computer. Never.
  8. Thoughts on security questions:
    1. Generally speaking, security questions are a bad idea: they provide in some circumstances a backdoor to bypass the best passwords; and it is too easy for other people to socially engineer in order to take advantage of security questions.
    2. How many people know the name of your pet, your first school, the street on which you grew up, your teachers, mother's maiden name?  Too many. Much of it is public knowledge; much can be gleaned by casual strangers from social networking sites.
    3. My recommendation for security questions?  Give bogus, random-ish answers, and store those questions and answers in the "Notes" section for that account in your password safe.  If the answer is false and unrelated to real life, it will be much harder for a ne'er do well (old boyfriend, identity thief, etc.) to provide those answers to gain access to an account.
  9. Thoughts on the complexity, and the rule of KISS:
    1. While I understand that this is not as easy as using a password sync service, I still prefer this methodology, in part because I do not necessarily trust any vendor with a single point of access.
    2. Because password safe encrypts the file locally, and that encrypted file is synced to a private dropbox account, there is not a single person that could potentially have access to my data: even if password safe's encryption is compromised (and it will be, like everything), then someone would still have to gain access to the encrypted datafile on my personal dropbox account or on my personal computer. 
Now, if you've done all of this, then you are more protected than otherwise against password guessing and other attacks, and you can get to your passwords from any computer, even an android phone, as long as you remember your password safe account (and your dropbox password, if you don't have any other computer).  

You'll never keep the NSA away from your passwords, data, and accounts, but this should keep organized crime and casual miscreants away.

Using this method, I even have secure access to my passwords from my smart phone when I need them.  And I don't have to actually remember but a couple of passwords.

I welcome your thoughts and critiques. Everybody has blinders, perhaps you've found a problem with this method?