Re: [GENERAL] What is the best thing to do with PUBLIC schema in Postgresql database
- Original Message - > From: "Patricia Hu" > Sent: Friday, November 4, 2016 9:58:10 AM > > Since it could potentially be a security loop hole. So far the action taken > to address it falls into these two categories: > > drop the PUBLIC schema altogether. ... > keep the PUBLIC schema but revoke all privileges to it from public role, > then grant as necessity comes up. > > Any feedback and lessons from those who have implemented this? > Admittedly, this may be TMI (...or maybe not enough...), but FWIW (and YMMV), I use the PUBLIC schema, along with the PUBLIC role, to expose a very limited view into the data base for the purpose of anonymous login and creation of user accounts. There is one view in the PUBLIC schema (and it has appropriate triggers and permissions to make the view writeable): fairwinds=# set search_path to public; fairwinds=# \d List of relations Schema | Name | Type | Owner +-+--+-- public | fairian | view | postgres (1 row) fairwinds=# \dp public.fairian Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +-+--+---+---+-- public | fairian | view | =ar/postgres | | (1 row) Then revoke unneeded privilege on the PUBLIC schema, and grant the read and write privileges on that one view: REVOKE CREATE ON SCHEMA public FROM public; GRANT SELECT,INSERT ON TABLE fairian TO PUBLIC; The special user role "fairwinds" is allowed trusted login in pg_hba.conf: # TYPE DATABASEUSERADDRESS METHOD hostfairwinds fairwinds all trust In summary, then, new users connect the first time with the "fairwinds" user and no password, and then create an account by inserting a row in the "fairian" view. Newly-created users subequently login with a password and then have an expanded view into the data base by GRANT USAGE on a different schema that contains more data base objects. If that write-up is not clear enough, there is a test server where you can try it at http://fairwinds.btober.net and see what I'm talking about. --B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dumping table contents in a sensible order
- Original Message - > From: "Chris Withers" > Sent: Tuesday, November 15, 2016 5:56:11 PM > > I have a database that I want to dump three tables from, for use in > development. They form a subset of the data, so I was dumping like this: > > pg_dump thedatabase --inserts -t table_one -t depends_on_table_one -t > depends_on_previous_two > dump.sql > > However, when I try to load this using the following: > > psql thedatabase_dev < dump.sql > > I get the following: > > SET > ERROR: unrecognized configuration parameter "lock_timeout" > SET > SET > SET > SET > ERROR: unrecognized configuration parameter "row_security" > SET > SET > SET > ERROR: relation "table_one" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two_id_seq" already exists > ALTER TABLE > ALTER SEQUENCE > ALTER TABLE > INSERT 0 1 > ... > INSERT 0 1 > ERROR: insert or update on table "table_one" violates foreign key > constraint "table_one_parent_id_fkey" > DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". > > So, the problem appears to be that table_one is self-referential by way > of a parent_id field. > > How can I either: > > - dump the table in an insertable order? > - have the load only apply the foreign key constraint at the end of each > table import? > The configuration parameter errors are a separate problem, but as for getting the table create statements in an order that respects dependencies what I do is: pg_dump fairwinds -U postgres -Fc > fairwinds.dump pg_restore -l fairwinds.dump > fairwinds.list # edit the list file, deleting everything except # the rows for defining the objects needed and # being sure to maintain the rows in the original order pg_restore -1 -c -L fairwinds.list fairwinds.dump> fairwinds.sql --B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invoice Table Design
- Original Message - > From: "rob" > To: pgsql-general@postgresql.org > Sent: Tuesday, November 29, 2016 3:45:21 AM > Subject: Re: [GENERAL] Invoice Table Design > > Hi Rich, > > thanks for the response -- going from Mongo to Postgres does require the > kind of approach you suggest. > > I suppose my question was a little bit more along the lines if anyone has > experience with designing payment / invoicing systems and any caveats they > may have encountered along the way. The other bit of experience I'll share is the suggestion that invoicing is a situation that lends itself to the uniformly incremented sequence pattern. Accountants and comptrollers love this. I detailed the pattern for a specific example of expense reports for which the business requirement was that expense reports be identified by uniformly incremented integers on a per employee and per year basis, but the pattern applies to many application domains ... like invoice numbers. Implementation involves a control value to record the most-recently used key value and a before-insert trigger to increment the value and effectively serialize inserts, allowing rollback that does not cause non-uniformities in the sequence. Some novice data base designers just like the idea of having no missing numbers in the sequence, and when they ask about it on this forum, they usually get some well-deserved flak, but there is a very practical aspect from an auditing perspective. Think of the situation with a traditional hard-copy check book. You count on the numbers being in sequence to assure that no checks go missing or otherwise fail to be accounted for. A similar serialized accountability could apply in many other circumstances. The whole message thread is here https://www.postgresql.org/message-id/flat/758d5e7f0608171414l548db1e9x43f2372c560c3c2%40mail.gmail.com Specifically my detailed explanation within that thread is here: https://www.postgresql.org/message-id/44e376f6.7010...@seaworthysys.com Some refer to this as the "gapless" sequence. Personally I dislike that term. Recently, I observed someone on this forum (... sorry, I can't find a link to credit it ...) used the term "keyed sequence", which I find appealing enough to consider it the proper term for this design pattern. -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select function alias
- Original Message - > From: "Howard News" > Sent: Wednesday, November 30, 2016 9:03:28 AM > Subject: Re: [GENERAL] select function alias > On 30/11/2016 13:42, Timoteo Blanco wrote: > > I've a series of timestamp columns I'd like to alias in select statements. > > psql indicates my alias doesnt exist after > > > I define it. > > > Example -> select to_char(impressions_create_date,'-mm-dd') as ymd from > > impressionsdb where ymd like '2016-11%' ; > > > psql always complains column ymd does not exist. I've inherited a series of > > tables with many timestamps and would like to > > > shorten the select in queries without renaming any columns. > > 3 options: > 1. Use the same to_char expression in the where clause > 2. Use a sub-select to use the alias in the outer where clause > 3. Use the original column in the where clause and use the timestamp > comparisson functions. 4. Define views on the tables. Make the views writeable using rules. 5. Use a CTE: with shortnames as ( select to_char(impressions_create_date,'-mm-dd') as ymd from impressionsdb ) select ymd from shortnames where ymd like '2016-11%'; -- B
Re: [GENERAL] About the MONEY type
- Original Message - > From: "Merlin Moncure" > To: "Raymond O'Donnell" > Cc: "Thomas Kellerer" , "PostgreSQL General" > > Sent: Wednesday, November 30, 2016 11:41:39 AM > Subject: Re: [GENERAL] About the MONEY type > > On Wed, Nov 30, 2016 at 7:43 AM, Raymond O'Donnell wrote: > > On 30/11/16 12:05, Thomas Kellerer wrote: > >> > >> Tobia Conforto schrieb am 30.11.2016 um 12:15: > >>> > >>> I think MONEY is a great datatype, at least in theory. > >> > >> > >> I personally find it pretty useless to be honest - especially because > >> the currency symbol depends on the client. > >> > >> ... > > > > > > I seem to remember that it was actually deprecated at some point - this is > > going back quite a few years. This was later reversed, though I don't know > > why. > > It was moved from a 32 bit implementation to a 64 bit one, and it was > given a lot of the previously missing basic infrastructure ... I recall a number of years ago reading about a money implementation that included different currency bases and exchange rate calculation. A quick Google search turned up https://github.com/samv/pg-currency which I am not sure is the same thing, but it looks like it might be something useful in the current context. -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generating sample data
- Original Message -
> From: "Rich Shepard"
> To: pgsql-general@postgresql.org
> Sent: Tuesday, December 27, 2016 7:23:46 PM
> Subject: Re: [GENERAL] Generating sample data
>
> On Tue, 27 Dec 2016, Adrian Klaver wrote:
>
> > As it happens there is a Python version of the a fore mentioned faker:
> > https://pypi.python.org/pypi/Faker/0.7.7
>
> Adrian,
>
>Impressive and complete. It will generate all the data I need.
>
This is kind of fun:
https://github.com/bmtober/groan
I had to hunt down the original author from the 1990's, which was when I
originally downloaded from his personal web site at
http://raingod.com/raingod/resources/Programming/Perl/Software/Groan/
The initial commit on that github page is the original source as provided by
Mr. McIntyre.
In a subsequent commit, I removed some of the original code that formatted for
HTML output, leaving just plain text, and also posted an example grammar for
generating fake names and strings that look like social security numbers (i.e.,
a U.S. taxpayer identification).
The script will generate duplicates, but you can do something like
for n in {1..20}
do
groan.pl ssn.gn
done | sort -u
to get unique source data.
By defining other custom grammars, you could potentially generate all kinds of
data.
-- B
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?
- Original Message - > From: "Alban Hertroys" > To: "Guyren Howe" > Cc: "pgsql-general" > Sent: Friday, December 30, 2016 6:23:27 AM > Subject: Re: [GENERAL] LYDB: Feasible to use PG roles instead of > application-level security? > > > > On 30 Dec 2016, at 4:19, Guyren Howe wrote: > > > > > > ... wonder whether it is practical to use PG’s own roles and > > security model in lieu of using an application-level one. > > > > It seems that the role system in PG is sufficient for most general > > purposes. One could presumably also have a table with role names and > > associated metainformation (email address etc) as needed. > > > > If I have a system with many thousands of users, is it practical to manage > > these users’ authentication and authorization using *just* Postgres? > > Postgres roles are global to the cluster, so you would end up with multiple > thousands of roles if you have multiple databases in your cluster with > different users on each. Which roles each user is allowed to have becomes > quite the nightmare for the administrators, I suspect. > > For a web-application facing the internet, I'd say no, don't do that. You're > dealing with far too many users to be maintainable. > > For an intranet database in a not-too-large company with a fixed set of > users, it could be a good solution, especially if those roles can be linked > to the company's LDAP server (assuming that's possible, I don't know). > Multiple intranet applications on that same database can use the same users > and roles. > > Someone needs to do the administration though; with volumes (of users) like > that and the database knowledge level of the average system administrator, a > GUI seems preferable. IMHO, pgadmin provides too many features to be > practical for someone like that, you would probably prefer something that > only does user administration. I don't know of anything that does that > though (not a GUI user myself)... > > > It occurs to me that some client frameworks might have issues with their > > connection pools if those connections keep switching users, assuming they > > even can, but let’s set that aside for now. Or perhaps every connection > > could immediately do a SET USER before executing its connection? > > > > This seems an attractive proposition from a security standpoint: if I use > > row-level security pervasively, I can have a security system that’s > > nestled nice and close to the data and presumably tricky to work around > > from a hacker given direct access only to the client application. > > With a few changes, that could work very well. > > First, create roles for the different types of users that you expect. In a > company, that could be by division, distinguishing division-heads, interns, > etc. > > Secondly, have a table with the users and their attributes like you describe. > Include an attribute for their database role there. Only administrator users > should have access to that table. > > Finally, create a stored procedure that looks up a user name in that table > and sets the accompanying role. If a user is not found, set the role to some > default 'unprivileged' user. > Make that procedure a SECURITY DEFINER with according permissions. That role > stays active the entire session, so unless you close the connection, create > a new one or change the user's role, this procedure doesn't need calling > again. > > > Is this practical? Has anyone here done it? What might the caveats be? > > It's a fairly common practice, the ML archives should contain plenty of > examples. I cannot speak to the thousands of users scenario in practice, but in principle it seems workable. As a practical matter, I have implemented something along the lines of what Hertroys describes, and taken it a step further, engaging schema privileges as well. The PUBLIC schema contains a single relation that exposes a view with three columns: username, password, and email_address. You could expose more, but for my purposes minimal is best. This view serves the express purpose of creating new user accounts and is, by means of the rule system, writeable. Technically it is readable, too, but a select statement provides no information about underlying data ... so stalkers can't get a user list. pg_hba.conf is configured to allow anonymous login which has usage permission on, and only on, the PUBLIC schema. So anyone can log in and see that one view and do an insert on that view. The rule system pushes down the INSERT onto a table in a private schema that has a trigger which creates the data base role corresponding to the new user row inserted into the view. Thus, prospective users "self administer" and create their own accounts as needed. The trigger also grants the new role membership in a particular group role. The group role has usage privileges on a second schema that exposes broader visibility into the data base as well as additional insert and update privileges on some of
Re: [GENERAL] efficiently migrating 'old' data from one table to another
- Original Message - > From: "Jonathan Vanasco" > To: "pgsql-general general" > Sent: Thursday, January 12, 2017 3:06:14 PM > Subject: [GENERAL] efficiently migrating 'old' data from one table to another > > I'm just wondering if there's a more efficient way of handling a certain > periodic data migration. > > We have a pair of tables with this structure: > > table_a__live > column_1 INT > column_2 INT > record_timestamp TIMESTAMP > > table_a__archive > column_1 INT > column_2 INT > record_timestamp TIMESTAMP > > periodically, we must migrate items that are 'stale' from `table_a__live ` to > `table_a__archive`. The entries are copied over to the archive, then > deleted. > > The staleness is calculated based on age-- so we need to use INTERVAL. the > "live" table can have anywhere from 100k to 20MM records. > > the primary key on `table_a__live` is a composite of column_1 & column_2, > > In order to minimize scanning the table, we opted to hint migrations with a > dedicated column: > > ALTER TABLE table_a__live ADD is_migrate BOOLEAN DEFAULT NULL; > CREATE INDEX idx_table_a__live_migrate ON table_a__live(is_migrate) > WHERE > is_migrate IS NOT NULL; > > so our migration is then based on that `is_migrate` column: > > BEGIN; > UPDATE table_a__live SET is_migrate = TRUE WHERE record_timestamp < > transaction_timestamp() AT TIME ZONE 'UTC' - INTERVAL '1 month'; > INSERT INTO table_a__archive (column_1, column_2, record_timestamp) > SELECT > column_1, column_2, record_timestamp FROM table_a__live WHERE > is_migrate IS > TRUE; > DELETE FROM table_a__live WHERE is_migrate IS TRUE; > COMMIT; > > The inserts & deletes are blazing fast, but the UPDATE is a bit slow from > postgres re-writing all the rows. > > can anyone suggest a better approach? > > I considered copying everything to a tmp table then inserting/deleting based > on that table -- but there's a lot of disk-io on that approach too. Review manual section 7.8.2. Data-Modifying Statements in WITH https://www.postgresql.org/docs/9.6/static/queries-with.html -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does this hot standy archive_command work
While learning a bit about basic hot standby configuration, I was reviewing an article that used these parameters wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' max_wal_senders = 1 hot_standby = on How or why that particular archive_command actually works (... and it does ... I tried it ...) is not clear to me based on reading of the Postgresql documentation on this topic. I would have expected to see an actual copy or rsync command, as described in the fine manual at section 25.3.1. "Setting Up WAL Archiving" The entire example appears at https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps Can anyone enlighten on this topic, or provide a link to an existing explanation? -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why does this hot standy archive_command work
- Original Message - > From: "Jerry Sievers" > To: "Steve Atkins" > Cc: "pgsql-general" > Sent: Monday, January 23, 2017 12:52:46 PM > Subject: Re: [GENERAL] Why does this hot standy archive_command work > > Steve Atkins writes: > > >> On Jan 20, 2017, at 7:03 PM, bto...@computer.org > >> wrote: > >> > >> While learning a bit about basic hot standby configuration, I was > >> reviewing an article that used these parameters > >> > >> wal_level = 'hot_standby' > >> archive_mode = on > >> archive_command = 'cd .' > > That's just a very silly way of making archive_command a true no-op... > > I would have set it to '/bin/true' Roger that. Also, I guess I should have maybe been more clear what I meant by "it works" ... the hot standby replication works, which is really what I was focusing on. I think I might have previously been under the mis-impression that archiving the WAL to ... somewhere ... was required for hot stand-by, so the no-op threw me since the stand-by configuration does not seem to specify where to get the primary WAL from. It seems like what is happening here is that since the primary archive, as you point out, is no-op, and the standby server knows where to just grab the WAL directly. Thanks for replying! > > > > >> max_wal_senders = 1 > >> hot_standby = on > >> > >> > >> How or why that particular archive_command actually works (... and it does > >> ... I tried it ...) is not clear to me based on reading of the Postgresql > >> documentation on this topic. I would have expected to see an actual copy > >> or rsync command, as described in the fine manual at section 25.3.1. > >> "Setting Up WAL Archiving" > >> > >> The entire example appears at > >> > >> https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps > >> > >> Can anyone enlighten on this topic, or provide a link to an existing > >> explanation? > > > > It's not archiving logs at all, instead relying on streaming them directly > > to the slave. > > > > Changing archive_mode requires a server restart, while changing > > archive_command from a command that does nothing, successfully, to a > > command that actually archives logs just requires a reload. So this lets > > you enable archiving without halting the server by changing the command. > > > > Or that's how I vaguely recall it working some years ago. Things may have > > changed now - you're following a very old tutorial. > > > > Cheers, > > Steve > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Build in function to verify email addresses
- Original Message - From: "Nick Dro" To: pgsql-general@postgresql.org Sent: Thursday, November 16, 2017 2:56:42 AM Subject: [GENERAL] Build in function to verify email addresses I beleieve that every information system has the needs to send emails. Currently PostgreSQL doesn't have a function which gets TEXT and return true if it's valid email address (x...@yyy.com / .co.ZZ) Do you believe such function should exist in PostgreSQL or it's best to let every user to implement his own function? I don't mean to sound snarky, but did you even try to google search? https://pgxn.org/dist/email/ https://github.com/asotolongo/email or https://github.com/petere/pgemailaddr -- B
Re: [GENERAL] Limiting DB access by role after initial connection?
- Original Message - > From: "Ken Tanzer" > To: "PG-General Mailing List" > Sent: Friday, June 9, 2017 1:37:34 AM > Subject: [GENERAL] Limiting DB access by role after initial connection? > > ...I'm working with an organization with a current production > database. Organizations in other locations using the same service delivery > model want to share this database, with some but not all of the data > restricted so that people at each site can see only that site's data. I've > been looking at doing this by creating a role for each location, ... > Currently the database has > one user, the owner, and access is controlled within the application by > usernames and passwords within the DB. > > My approach was to have the initial connection made by the owner, and then > after successfully authenticating the user, to switch to the role of the > site they belong to. ... > > > ...I'd also welcome any > thoughts, suggestions or feedback about 1) and 2), or better approaches > entirely. Thanks! > As to your very last point (suggestions about other approaches), is it impossible or impractical to migrate to a scheme in which each user actually has a data base role and their own password? Postgresql has really great facility for managing database authorization and access by means of login roles assignable membership in group roles. Why not let the tool do what it can already do very effectively? -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Shared Constants in PLPGSQL
- Original Message - > From: "Tim Uckun" > To: "pgsql-general" > Sent: Tuesday, August 1, 2017 5:56:02 AM > Subject: [GENERAL] Shared Constants in PLPGSQL > > What's the best way to deal with global constants in PLPGSQL. Currently I > am putting them in a function with out parameters and then calling that > function from every other function that needs them like this. > > CREATE OR REPLACE FUNCTION hashids.constants( > OUT min_alphabet_length integer, > OUT sep_div numeric, > OUT guard_div numeric, > OUT default_steps text, > OUT default_alphabet text, > OUT salt text) > > I am presuming that if I set this function as immutable the calls to this > function will be cached and will not incur much overhead. > > Is there a better way to deal with this? > One alternative is to create a configuration values table to store application configuration parameters. This table generally has exactly one row. You can add new application configuration parameters easily as application requirements evolve by adding a new column of an appropriate data type. Then, when you need the configuration values in PLPSQL or anywhere for that matter, you do a simple SELECT statement. Also you, can update parameters if necessary (supposing if they are not actually *constants*) at run time very easily, too. -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
