Should I be a super user in postgis ? And other beginner’s questions about roles and users in postgis

I have a confession to make. Some reasons that I don’t post on here because:
a] I don’t want to appear too ignorant to others. Typing this out makes me realize the above thought is pretty sophomoric. We all have to start somewhere when we learn things including postgis, programming, cartography, and other things in the geospatial world.
b] Like others, I don’t always want to go back to my notes to write concise documentation. I just write enough to make sense for myself later on. Other tech writers do the same thing when they write documentation… leading to point C [SIDEBAR: I keep a textfile of every each problem, install issue, question that I have, named log-nameofprogram].
c] Most documentation for postgis and I’d extend this to linux, is written so that the reader is assumed to know a lot. This is a pet peeve of mine, and by not doing this myself, I’m a hypocrite. Below is an example…

Here is my journey along the way of learning all of this…. With that said…

What’s the difference between a “super user” in postgres and another user ? Should I use the ‘postgres’ user in my day to day postgis work ? Should my created user be a super user ?

All of my mapping work is locally done on this computer. I’m the only user. Occasionally, I’ll make dumps of my db’s and then store them in other places.

Until now, I had been just using postgres (the default user name, after you install postgres) as my user name. I knew that you could create a user name, but wasn’t sure whether I should or not in my case. What was the difference anyways ?

As a result, whenever I converting data into postgis databases, I would have to copy these data files (usually from OSM) into /tmp because postgres could access files from my /home . I was finding it to be a bit inefficient, and I was prompted to determine whether or not I couldn’t access my python virtual environment (where imposm is installed) while logged in as postgres..

So, as I was looking to create a new user, I was wondering whether or no to be a super user’

The Postgresql documentation was a bit helpful.
Stating:
“Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a “database user”. To create a role with login privilege” use either:

CREATE ROLE name LOGIN;
CREATE USER name;

What does LOGIN priviledge mean exactly ? Oh, well. A couple paragraphs down, they mention that I can issue CREATE ROLE name CREATEDB.

postgresql up and running (the book) explained it differently, telling that you can create the users in pgadmin or by the command line and highly recommend to create a new user after ASAP after installation, by doing:

CREATE ROLE leo LOGIN PASSWORD 'lion!king'
CREATEDB VALID UNTIL 'infinity';

It raises a good point, do I want a password with my postgres user account ?
“If you wanted to create a user with super rights, meaning they can cause major
destruction to your database cluster and can create what we call untrusted language functions”
Not sure what untrusted language functions are, but I’ll trust them and create the user. ‘

Well, after running, logged in as postgres (you can log into postgres through different ways, including sudo -u postgres -i) launching psql in the terminal, I typed in:
CREATE USER skors
(skors – my user name, also the user name for my desktop as well. I forgot the ; but it’s no big deal because I was able to create a database simply by doing: createdb nameofdb)

So, I go to add postgis capabilities now to my newly created database using my user name by doing

psql -d cleve -c "CREATE EXTENSION postgis;"
ERROR: permission denied to create extension "postgis"

argh ! Why shouldn’t I be a super user if I have to be a super user to create a new database ?

Which leads me full circle back to my questions that I asked in the beginning still unanswered:

What’s the difference between a “super user” in postgres and another user ? Should I use the ‘postgres’ user in my day to day postgis work ? Should my created user be a super user ? Do I want a password with my postgres user account ?

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s