Assigning "Usage" DB Permissions, Postgres

[cored0mp]

More programming fun!

OK this time around I'm trying to create a table with the statement,

CREATE TABLE tablename ( recnumber SERIAL  PRIMARY KEY, recordvalues VARCHAR ( 500 ) NOT NULL, datecreated DATE )

And it basically works, and I can add records using the dba account. However when I try to add records using the application db account I get the error,

permission denied for sequence _seq

A search engine claims that I need to assign "Usage" permissions, but this is turning into a rabbit hole of then needing to assign 'Usage' rights specific to something called a 'sequence.' But I feel like that will only be a dead end.

Am I better off granting . privs to the application service account? Or perhaps just running the application using the dba account? Or maybe I should switch back to Mysql?

It seems like with postgres there are a lot of hoops to jump through but that the nuances are reducing than adding to the flexibility of what I'm able to configure as a DBA. This is not a rant post and I am quite happy to switch back to Mysql if that's what everyone else is doing. No point in using Postgres if it's a step backwards.

Thanks in advance!

[cored0mp]

I can't reasonably switch over to Mysql, can I? I mean license wise I might as well just be on Oracle at that point?

[rproffitt]

1. GRANT. Yes, you'll be using the GRANT command.
2. I can't guess your needs here. If your system is web based or app based (C++, etc.) then the GRANT would be per user. If web or server based, then you grant needs change. This isn't a simple yes/no question. That is, the system designer would know what they need. A new system designer will take time to take a few tutorials about this area and think long and hard about security. i.e. don't grant too much power to all possible users.
3. The old MySQL vs Postgres question seems to be about if your needs are read or write heavy. Or if there's a feature you absolutely need in either system.

[rproffitt]

Can you tell me more about the License concerns? We've never had to pay for decades. I think my first use was around 1998. That's 25 years of free use.

Is MySQL free or cost?
MySQL is free and open-source software under the terms of the GNU General Public License, and is also available under a variety of proprietary licenses.

[Dani]

I'm a fan of the Percona flavor of MySQL. DaniWeb used to do a lot of work with Percona back when they were a small startup and I had the cell number of the owner. Nowadays they're too big for us ;)

[rproffitt]

Our apps worked with (at the time) almost any SQL server. So we didn't have to license MySQL.

If your application works with many databases, either natively or by using one of the database source independent frameworks, then you can freely distribute the MariaDB server with your application without being affected by the GPL. The reason for this is that MariaDB would only be an optional, independent component in your software distribution and section 2 of the GPL explicitely allows this

[cored0mp]

Update: OK, sold! You have convinced me.

I have installed mysql and I'm porting the source. Here goes nothing!

[cored0mp]

Thank you for recommending mysql. It took me only seven hours of work to port my prototype over. By now I am weeks ahead of where I was projected to be. Best decision ever.